Generic SQL formatters don't know PostgreSQL, and it shows Tags: postgres, sql, tooling, webdev

Paste a Postgres query into a random online SQL formatter and most of the time it looks fine. Then you hit the one with a cast, a JSON operator or a function body, and the layout goes strange. There is rarely an error. You just get id::text split across two lines, or meta ->> 'plan' laid out like three unrelated tokens.

The reason is that a formatter doesn't understand SQL. It tokenises the text and lays out the tokens. PostgreSQL bends the token rules in more places than MySQL or SQL Server, so a tokeniser built for "SQL in general" trips over everyday Postgres.

The constructs that break

These are the ones I run into most:

• :: casts, as in created_at::date
• the JSON operators ->, ->> and #>>
• $$ dollar quoting around function bodies
• ILIKE, RETURNING, DISTINCT ON, LATERAL joins and FILTER (WHERE ...) on aggregates
• named parameters like :name next to positional $1

A formatter that guesses the dialect from the text gets these right some of the time. Pinning the dialect to PostgreSQL gets them right every time, because there is nothing left to guess.

Keywords yes, identifiers never

Postgres folds unquoted identifiers to lowercase. SELECT userId FROM Users reads the column userid from the table users. Quoted names keep their case, so "UserId" is a different column altogether. A formatter may uppercase keywords, since keyword case is style. It must never touch identifiers, because in Postgres their case can carry meaning.

One edge case is worth knowing. Date-part words like day, month and year count as keywords to the tokeniser. So this query

select created_at::date as day from orders;

comes out as AS DAY once keyword uppercasing is on. It still runs, Postgres folds DAY straight back to day. It only looks wrong in a diff. Quote the alias (as "day") or rename it to signup_day and the problem is gone.

Function bodies stay one string

Everything between $$ and the matching $$ is a single string literal to Postgres. A correct formatter reads it the same way and passes the body through byte for byte. If you want the SQL inside a plpgsql function formatted as well, paste the statements between BEGIN and END on their own and put the result back. Or use pgFormatter, which parses plpgsql itself.

What about pgAdmin and psql?

If your queries live in a repository, pgFormatter is the reference. Wire it into CI or a pre-commit hook and you're done.

pgAdmin surprised me. The Query Tool has auto-indent and block indent, but no command that reformats an existing query, so a one-liner pasted from a log stays a one-liner. psql has no formatter at all. \e opens your $EDITOR and the layout is whatever the editor does.

The gap is the query that isn't in your editor yet. A line from the slow-query log, something out of a Slack thread, a statement copied from a dashboard. For those I built a PostgreSQL formatter with the dialect pinned. It runs sql-formatter inside the browser tab, so the query, with its table names and sometimes real customer values, isn't sent anywhere.

It has no schema and no planner. A misspelled column formats perfectly. For real answers, EXPLAIN against the actual database is still the only thing that counts.