Menu

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

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

DEV Community: postgres·René·12 days ago
#qA9XMCGC
#devtopostgresql#devto#dev#formatter#query
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.