Formatting makes SQL code accurate, readable, understandable, and standardized for teams and individuals. SQL formatter is a tool that beautifies code automatically. Formatting tools are various – online solutions, desktop standalone tools, add-ons, and features provided in professional IDEs for all database management systems.
This article will explore pgFormatter, probably the most popular tool for PostgreSQL code formatting. Additionally, we’ll take a look at one of its most viable, feature-rich alternatives.
Contents
- pgFormatter – PostgreSQL syntax beautifier
- pgFormatter – features overview
- Example 1. Formatting SQL code with pgFormatter
- Example 2. Formatting with additional options
- Example 3. Anonymize values in queries
- dbForge Studio – alternative software for PostgreSQL code formatting
- The advantages of dbForge Studio for PostgreSQL
- pgFormatter vs dbForge Studio for PostgreSQL – comparison table
- The advanced formatting features of dbForge Studio for PostgreSQL
- Formatting of CREATE statements
- Formatting of other statements
- Special features
- Conclusion
pgFormatter – PostgreSQL syntax beautifier
PostgreSQL is one of the most popular database management systems in the world. The SQL dialect it uses differs from other RDBMS, such as MySQL or SQL Server. Therefore, formatters for PostgreSQL must match the specifics of this system.
The best-known solution is pgFormatter – a free online PostgreSQL code beautifier created and maintained by Gilles Darold. pgFormatter, as its name suggests, aims to work with PostgreSQL – it supports its specifics and keywords. Users only need to type the code into the working area, paste it into the PostgreSQL formatter window, or load a file with code.
Many developers consider pgFormatter the most flexible code beautifier and PostgreSQL query validator. While there might be different opinions on that matter (all developers have their preferences), there is no doubt that pgFormatter is user-friendly and functional enough to match the needs of the PostgreSQL database code formatting.
pgFormatter – features overview
pgFormatter is a single-page web application with a text field and a list of options.
You can try the tool’s functionality at once: pgFormatter provides a sample of unformatted code – to get it, click the Load example button at the bottom of the left pane.
The following options are available:
- Syntax highlighting
- Remove comments
- Anonymize values in queries
- Comma at the beginning
- New line after comma (INSERT)
- Keep empty lines
- Alternate formatting
- No transaction grouping
- Statement numbering
- Redshift keywords
It is possible to apply all options or select only the ones you need.
Besides, pgFormatter allows formatting keywords, functions, and datatypes by switching values to lower case, or upper case, capitalizing all of them or keeping them unchanged. There are additional configuration options for indentation and word wrapping.
Now, let us examine some practical cases of using pgFormatter with different parameters.
Example 1. Formatting SQL code with pgFormatter
By default, the tool formats SQL code according to the standard rules and cleans raw, messy code even without additional parameters specified.
Let’s take an example:
Now we want to format it. Click the Format code button:
As you can see, the code looks much cleaner than before.
Example 2. Formatting with additional options
Now we can add more parameters. Assume we want to apply the upper case to all keywords.
The previously formatted code (with basic formatting) is adjusted according to our new settings.
Example 3. Anonymize values in queries
This option replaces actual values in your code with randomly generated values.
It helps you protect data and prevent information leaks when sharing code with third parties.
The online pgFormatter makes your code clean, structured, and readable for any developer, whether they know that code well or see it for the first time. However, this tool is not the only one.
dbForge Studio – alternative software for PostgreSQL code formatting
pgFormatter formats your code and does the job well, but it won’t provide any other code-writing assistance. In contrast, such professional tools as pgAdmin, DBeaver, or dbForge Studio for PostgreSQL are much more advanced in terms of functionality. And now we are going to examine dbForge Studio – a popular PostgreSQL GUI client by Devart.
The advantages of dbForge Studio for PostgreSQL
dbForge Studio for PostgreSQL is a multifeatured PostgreSQL GUI client designed to perform database development and management tasks. Among other features, it provides a Code Editor module that combines code autocompletion, PostgreSQL query validator, SQL formatter, and other features in one solution.
The built-in PostgreSQL formatter functionality lets you format your entire code, or part of it, or just one specific statement.
- You can use a default set of formatting options, a profile, which can be customized to match your purposes. Also, you can create your own new formatting profiles and share them with colleagues to align coding standards across your entire team.
- Different formatting profiles can provide different styles of formatting. It is helpful for developers working on projects for various companies that demand their own specific code style. You only need to switch to the corresponding profile.
- Syntax highlighting is enabled in the Studio by default, together with the syntax and spelling checkers, code debugging, and auto-completion.
- A new line after a comma (INSERT) is the option required by many developers who need to specify code formatting in the INSERT statement. You can configure it for column names and data inserted into tables with flexible settings.
- Keyword and function case formats are much more functional in dbForge Studio than in pgFormatter. The Studio offers all options that the competitor has; plus, it supports case format for Aliases, Identifiers, and Variables.
- A popular “comma at the beginning” option is provided with lots of settings for precise formatting. In particular, you can configure the SELECT list and edit indents and wrapping.
However, dbForge Studio for PostgreSQL does not support the “remove comments” option that is present in pgFormatter. The Studio considers that each comment is there for a reason, and removing it may affect code readability and effectiveness (for example, to prevent the accidental deletion of an important hint).
Now, let us review the functionality of both tools in detail.
pgFormatter vs dbForge Studio for PostgreSQL: comparison table
When comparing the code formatting functionality of these two products, we can note both similarities and differences. It is natural because all solutions in this niche need to provide certain features that are necessary for almost every user. At the same time, there can be additional features – the aces that will persuade users to choose exactly this solution.
Let’s take a look at this feature-by-feature comparison of pgFormatter and dbForge Studio for PostgreSQL.
| Option | pgFormatter | dbForge Studio |
| Syntax highlighting | ✔️ Yes | ✔️ Yes |
| Anonymize values in queries | ✔️ Yes | ❌ No |
| Comma at the beginning | ✔️ Yes | ✔️ Yes |
| New line after comma (INSERT) | ✔️ Yes | ✔️ Yes |
| Alternate formatting | ✔️ Yes | ✔️ Yes |
| No transaction grouping | ✔️ Yes | ✔️ Yes |
| Statement numbering | ✔️ Yes | ❌ No |
| Redshift keywords | ✔️ Yes | ✔️ Yes |
| Keywords & function case format | ✔️ Yes | ✔️ Yes |
| Indentation | ✔️ Yes | ✔️ Yes |
| Word wrapping | ✔️ Yes | ✔️ Yes |
| Text case | ❌ No | ✔️ Yes |
| Spacing | ❌ No | ✔️ Yes |
| CREATE statement formatting | ❌ No | ✔️ Yes |
| SELECT/UPDATE/INSERT/DELETE statement formatting | ❌ No | ✔️ Yes |
| CTE statement formatting | ❌ No | ✔️ Yes |
| Subquery formatting | ❌ No | ✔️ Yes |
| ALTER/CREATE sequence formatting | ❌ No | ✔️ Yes |
| Stacked list formatting | ❌ No | ✔️ Yes |
| VALUE formatting | ❌ No | ✔️ Yes |
From this table, we can see that dbForge Studio for PostgreSQL includes a set of options that aren’t available in pgFormatter. In particular, these include the formatting of different statements and subqueries as well as several special features.
The advanced formatting features of dbForge Studio for PostgreSQL
The most essential benefit of the Studio for code formatting tasks is the possibility to format various fragments of code differently in one document using the formatting profiles.
Formatting of CREATE statements
The SQL formatting feature of the Studio applies to the following statements:
- CREATE Index – lots of flexible settings, including indents, word wrapping, and other parameters. There are specific formatting styles for the ON clause, Include Columns, and the WHERE clause separately.
- Create Materialized View – you can apply plenty of formatting options here, including separate configuration settings for Column List and Storage Parameters.
- Create Procedure/Function – note the separate formatting options for Parameters and RETURN blocks, including indents.
- Create Table – apply various formatting settings, including options for Column List, INHERITS, PARTITION BY, and Storage Parameters.
- Create Trigger – there are separate options for formatting Events, ON, FROM, REFERENCING, WHEN, and EXECUTE.
- Create View – use flexible settings to format the full statement for creating a view in PostgreSQL with all of its components.
If you want to format various CREATE cases separately in one document, you don’t have to do it manually. Instead, specify the formatting preferences for each CREATE, and let the software beautify the code.
Formatting of other statements
dbForge Studio for PostgreSQL allows you to set specific formatting for the following statements:
- SELECT
- INSERT
- UPDATE
- DELETE
- UNION/EXCEPT/INTERSECT
- ALTER/CREATE sequences
- CTE statements
The Studio can format all these statements differently if you need that in your code.
Special features
There are further formatting-related special features in dbForge Studio for PostgreSQL:
- Formatting of separate files and bulk formatting
- Formatting of folders (namely, of all files in a selected folder)
- Automation of code formatting from the command line
dbForge Studio for PostgreSQL has built-in wizards to configure these tasks – the SQL Formatter Wizard and the Command Line Wizard. This way, you can convert your formatting settings into the command-line syntax and save them as an executable *.bat file.
Conclusion
The role of code formatting can’t be overestimated. It turns the mess of a code into clean and well-structured data that any specialist can understand and use effectively. That’s why code formatting tools are so popular and wanted.
The online pgFormatter is free of charge and easy to use. It provides basic code formatting functionality and beautifies your code easily. But it only offers a limited set of formatting options. While it might be enough for many tasks, pgFormatter would not match more complex requirements.
dbForge Studio for PostgreSQL is a paid IDE solution with plenty of features, including robust formatting functionality. It lets SQL developers tune the settings precisely up to a single statement in a document and apply them simply using formatting profiles. Besides, it allows formatting files and directories and automating recurring tasks.
To try the IDE and evaluate its capabilities, you can get the free trial of dbForge Studio for PostgreSQL – all the functionality is at your service for 30 days.
