Formatting PostgreSQL is rarely about the SELECT list. The difficulty is that Postgres has accumulated a set of operators that look like ordinary punctuation: :: for casts, -> and ->> for JSON access, @> for containment, ?| for key existence. A formatter that tokenises naively either splits these in half or mistakes the leading colon for a named parameter marker and breaks the line in the wrong place.
This page runs the formatter with the PostgreSQL grammar selected and the parameter syntax set to named (:name) plus numbered ($1), which is what the Postgres wire protocol and most drivers use. The example below is loaded into the editor above, so you can change the options and watch the same query reformat.
Before and after
This is the query loaded in the editor above. On the left is what you paste; on the right is what the formatter returns with the default options for this dialect.
select c.id, c.profile->>'name' as name, (c.profile->'prefs'->>'locale')::text as locale, o.total::numeric(12,2) as last_order, count(*) filter (where o.status = 'shipped') as shipped from customers c left join lateral (select * from orders o where o.customer_id = c.id order by o.created_at desc limit 1) o on true where c.profile @> '{"active": true}'::jsonb and c.created_at >= now() - interval '90 days' group by c.id, c.profile, o.total order by last_order desc nulls last;SELECT
c.id,
c.profile ->> 'name' AS name,
(c.profile -> 'prefs' ->> 'locale')::TEXT AS locale,
o.total::NUMERIC(12, 2) AS last_order,
COUNT(*) FILTER (
WHERE
o.status = 'shipped'
) AS shipped
FROM
customers c
LEFT JOIN LATERAL (
SELECT
*
FROM
orders o
WHERE
o.customer_id = c.id
ORDER BY
o.created_at DESC
LIMIT
1
) o ON TRUE
WHERE
c.profile @> '{"active": true}'::JSONB
AND c.created_at >= NOW() - INTERVAL '90 days'
GROUP BY
c.id,
c.profile,
o.total
ORDER BY
last_order DESC NULLS LAST;What is specific to PostgreSQL
The :: cast operator versus :named parameters
PostgreSQL uses :: for casting and : to introduce a named parameter, which puts two meanings on the same character. A formatter configured for a dialect where : is only a parameter marker will read the first colon of o.total::numeric as the start of a placeholder and break the expression.
Selecting the PostgreSQL grammar tells the parser that :: binds as a single cast operator, while :name and $1 remain placeholders. Casts stay attached to their expression, as in the (c.profile -> 'prefs' ->> 'locale')::TEXT line above.
JSONB operators, including the ? family
Postgres exposes JSON access through operators rather than functions: -> returns jsonb, ->> returns text, #> and #>> take a path array, and @> tests containment. They are handled as operators, so they keep their operands on the same line where the expression width allows.
The existence operators deserve a special mention. ? tests for a top-level key, ?| for any key in an array and ?& for all of them — and ? is also the positional placeholder used by JDBC and several other drivers. With the PostgreSQL grammar selected, these parse as operators:
SELECT
*
FROM
t
WHERE
t.data ? 'key'
AND t.data ?| ARRAY['a', 'b'];Dollar-quoted function bodies are left alone
A PL/pgSQL body written between $ or $tag$ delimiters is, to the SQL parser, one long string literal. The formatter preserves it exactly rather than reindenting it, which is the safe behaviour — reformatting the inside of a string would change the value stored in the catalog.
In practice this means CREATE FUNCTION statements come out with the surrounding SQL formatted and the body untouched on a single line. If you want the body itself formatted, format it separately as a standalone block and paste it back.
CREATE FUNCTION f () returns INT AS $ begin return 1; end; $ language plpgsql;FILTER, LATERAL and other clauses that nest
COUNT(*) FILTER (WHERE ...) puts a full WHERE clause inside an aggregate call, and LEFT JOIN LATERAL puts a full subquery inside a join. Both are expanded as nested blocks, which is what makes the shape of the query readable — you can see at a glance that the lateral subquery is a per-row top-1 lookup.
ON CONFLICT ... DO UPDATE with a RETURNING clause is handled the same way, including references to the excluded pseudo-table.
INSERT INTO
t (id, v)
VALUES
(1, 'a')
ON CONFLICT (id) DO UPDATE
SET
v = excluded.v
RETURNING
id;Identifier folding: why Preserve is the right default
PostgreSQL folds unquoted identifiers to lower case, so MyTable and mytable are the same object, while "MyTable" in double quotes is a different one. That makes identifier case meaningful in a way it is not in most dialects.
The Identifier case option therefore defaults to Preserve. Changing it to Upper or Lower will rewrite quoted identifiers too, which can point a query at a table that does not exist. Keyword case is a separate setting, so you can still uppercase SELECT and FROM without touching your schema names.
Known limitations
No formatter handles every corner of a dialect. These are the cases where this one produces output you may want to correct by hand.
- DISTINCT ON (col) is placed on the line after SELECT DISTINCT rather than kept with it. The output is valid SQL, but it reads less well than the rest.
- Inside a dollar-quoted body the formatter makes no changes at all, including the language plpgsql tail of a CREATE FUNCTION statement, which is left in the case you typed it.
Conventions worth adopting
Keywords upper, identifiers untouched
The most widely used Postgres style uppercases reserved words and leaves table and column names in the snake_case the schema was created with. That is the default configuration here: keyword, data type and function case set to Upper, identifier case set to Preserve.
Prefer CTEs, and know that they are no longer fences
Breaking a query into WITH blocks reads far better than nesting subqueries three deep, and since PostgreSQL 12 a plain CTE is inlined by the planner rather than materialised, so the readability no longer costs you a plan change. Add MATERIALIZED explicitly when you do want the old fencing behaviour.
The Lines between queries option controls the blank lines the formatter puts between statements, which is what keeps a migration script with several statements readable.
PostgreSQL formatting FAQ
Does formatting change how PostgreSQL runs my query?
No. The parser discards whitespace and folds unquoted identifiers before planning, so the plan for a formatted query is identical to the plan for the same query on one line. Formatting is for the people reading the code.
Will it break my "CamelCase" quoted identifiers?
Not with the default settings. Identifier case is set to Preserve, so quoted identifiers come out exactly as you wrote them. Only change that setting if you are certain your schema uses unquoted, case-insensitive names throughout.
Can it format the body of a PL/pgSQL function?
The body between $ delimiters is a string literal as far as the SQL parser is concerned, so it is preserved verbatim rather than reindented. To format the body itself, paste just the block between the delimiters.
Are my queries sent anywhere?
No. The formatter is a JavaScript library running in your browser. Nothing is uploaded, which is what makes it safe to paste a query that contains production table and column names.
Other SQL dialects
Not sure which one you need, or working with more than one? The general SQL formatter lets you switch dialects without leaving the page.