PostgreSQL Formatter & Beautifier

Why PostgreSQL-Specific Formatting

PostgreSQL differs from standard SQL in ways that affect formatting:

• ::type casting operators need no space around them — adding one breaks the syntax
• RETURNING clauses should stay visually connected to their DML statement
• JSONB path expressions like data->'key'->>0 should not be split across lines
• Recursive CTEs need clear visual separation between anchor and recursive members

Generic formatters often add spaces around :: operators. The PostgreSQL dialect avoids this.

PostgreSQL Formatting Examples

Recursive CTE — Before

WITH RECURSIVE subordinates AS (SELECT id,name,manager_id FROM employees WHERE id=1 UNION ALL SELECT e.id,e.name,e.manager_id FROM employees e INNER JOIN subordinates s ON s.id=e.manager_id) SELECT * FROM subordinates;

After (PostgreSQL, 4 spaces)

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

The recursive CTE structure is now visible — anchor query on top, recursive member below, final SELECT at the bottom.

PostgreSQL Clauses Worth Formatting

Beyond casting and CTEs, everyday Postgres work is full of clauses that collapse into unreadable one-liners. A PostgreSQL-aware formatter gives each clause its own line so you can scan the intent at a glance.

Upsert with ON CONFLICT ... RETURNING

INSERT INTO inventory (product_id, quantity) VALUES (42, 10) ON CONFLICT (product_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity RETURNING product_id, quantity AS new_qty;

After (PostgreSQL, 4 spaces)

INSERT INTO inventory (product_id, quantity)
VALUES (42, 10)
ON CONFLICT (product_id)
DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity
RETURNING product_id, quantity AS new_qty;

The conflict target, the update branch, and the RETURNING clause each sit on their own line — exactly how a reviewer wants to read an upsert. The formatter keeps EXCLUDED references intact and does not mangle the + expression.

Other Postgres-only constructs handled cleanly

• DISTINCT ON (column) — the ON expression list stays attached to DISTINCT instead of being split onto the next line
• FILTER (WHERE ...) — aggregate filters like COUNT(*) FILTER (WHERE status = 'paid') are preserved as one readable unit
• LATERAL joins — subqueries after LATERAL are indented as join members so the correlation between the outer query and the lateral subquery is obvious
• Array and JSONB operators — @>, ?|, and ->> keep their exact spacing so the query keeps working after formatting

These are the constructs that break under naive formatters. When the output survives a copy-paste back into psql or your migration runner without errors, you know the dialect support is doing its job.

PostgreSQL Beautifier: Readable Layout, Same Query

A PostgreSQL beautifier takes SQL that works but reads like a wall of text and hands back the same statement with line breaks and indentation a human can follow. Nothing is renamed, no clause is reordered, and no space appears around :: casts or JSONB operators — whitespace is the only thing that changes.

Paste a query from a ticket, a slow-query log, or a legacy migration, pick PostgreSQL as the dialect and 4 spaces of indentation, and the output matches what most Postgres shops already write by hand: one clause per line, join conditions attached to their join, subqueries indented inside their parentheses. The dialect setting is what separates this from a generic beautifier — generic tools happily insert a space after :: and hand you back a statement Postgres refuses to parse.

Where a PostgreSQL beautifier earns its keep

• Code review — reviewers argue about logic instead of whitespace noise
• Migration files — ALTER TABLE and generated CREATE INDEX statements line up vertically
• Bug reports — pasted SQL stays legible in issue trackers and chat
• Handover — the join graph is visible at a glance instead of buried in a 900-character line

Need the opposite — a single-line statement for a script — run the result through the SQL minifier.

Using It as a PostgreSQL Code Formatter

As a project-wide PostgreSQL code formatter, consistency matters more than taste: the same input should always produce the same output, so diffs show real changes instead of reflowed lines. Two controls decide the layout — dialect (PostgreSQL) and indentation (2 spaces, 4 spaces, or tabs). Pick one per repository and every file matches, whether it came from a human, an ORM log, or a dump.

What it is useful for beyond one query

• Reviewing a reformatted schema dump before a release
• Normalising queries pulled out of an ORM's slow-query log before filing a bug
• Comparing two revisions of a query with the SQL diff tool — format both sides first and the diff shows semantic changes only
• Cleaning big inputs: for dumps and data loads see formatting large SQL files
• Porting MySQL-flavoured SQL before running it against Postgres — SQL dialect converter
• Reusing vetted query patterns from the SQL snippet library

All of it runs client-side, so the query text never leaves your machine. That matters when the statement carries table names, column names, or customer identifiers you would rather not paste into a server-side tool.

pg_format, pgFormatter and psql: CLI Tools vs an Online Formatter

Searching for "format psql" or "pg_format" usually means you already know the command-line route. The established options:

• pg_format — a Perl command-line formatter: install it, pipe SQL in, get formatted SQL out, with configurable indentation and keyword casing.
• pgFormatter — the same idea packaged for reuse, commonly wired into editor plugins or CI jobs.
• psql itself — worth being precise here: psql has no SQL formatter. Its \x expanded-display mode changes how result rows are printed, not how your query text looks.

CLI tools are the right answer when formatting runs as part of a build. They are the wrong answer when you cannot install Perl or Docker on the machine in front of you, when you are reviewing someone else's query in a browser tab, or when you just want to see one statement laid out before pasting it back into chat.

Getting a psql-style readable layout

If what you want is psql-like readability — clauses on their own lines, joins and subqueries indented, RETURNING aligned with the statement it belongs to — select PostgreSQL and 4 spaces above. The result survives copy-paste back into psql, a migration runner, or an EXPLAIN ANALYZE session unchanged. To read the plan that comes back, use the EXPLAIN visualizer; to confirm the statement parses first, use the PostgreSQL syntax checker.

Practical Tips for Postgres Users

Validate PostgreSQL syntax before you format

Run the query through the PostgreSQL syntax checker first — a statement that does not parse produces neat but broken output. The validator understands RETURNING, :: casts, DISTINCT ON, LATERAL joins and JSONB operators such as @> and ->>, all of which get flagged if you check them under MySQL or Standard SQL. Fix the reported line, then format.

Format pg_dump output

pg_dump --schema-only output is functional but ugly. Formatting it section by section — tables, indexes, functions — makes reviewing database changes much easier.

Audit JSONB queries

When working with JSONB-heavy queries, format the SQL first, then trace the path expressions to verify they target the right nested keys. Clean formatting makes this kind of auditing practical.

Frequently Asked Questions