Skip to the tool
DevToolBench

PostgreSQL Formatter

Beautify Postgres SQL, dollar quoting and casts included.

171 chars
UPDATE
  "Accounts"
SET
  plan = $1,
  updated_at = now()::timestamptz
WHERE
  email ILIKE $2
  AND id IN (
    SELECT
      account_id
    FROM
      invoices
    WHERE
      total > 0
  )
RETURNING
  id,
  "planName";
Statements
1
Comments kept
0
Lines
18
Characters
219

PostgreSQL syntax, before and after

  • A dollar-quoted body is one string, quotes and semicolons included

    create function greet() returns text as $body$ select 'it''s ' || now()::date; $body$ language sql
    CREATE FUNCTION greet() RETURNS text AS $body$ select 'it''s ' || now()::date; $body$ LANGUAGE sql

  • :: casts stay glued to the value

    select created_at::date, '42'::int + 1 from events
    SELECT
  created_at::date,
  '42'::INT + 1
FROM
  events

  • RETURNING is a clause of its own

    update users set active = false where id = $1 returning id, email
    UPDATE
  users
SET
  active = FALSE
WHERE
  id = $1
RETURNING
  id,
  email

  • ILIKE is kept as a keyword, not split or rewritten

    select name from users where email ilike '%@example.com' or name ilike 'ann%'
    SELECT
  name
FROM
  users
WHERE
  email ILIKE '%@example.com'
  OR name ILIKE 'ann%'

  • Double quotes keep a mixed-case name exactly as written

    select "userId", "Order" from "Users"
    SELECT
  "userId",
  "Order"
FROM
  "Users"

Everything runs in your browser. Nothing you type is sent to a server.

Found a bug in this tool? Report it.

Share this tool

A PostgreSQL formatter that knows Postgres syntax

This PostgreSQL formatter puts each clause of a query on its own line, splits select lists and SET assignments one per line, and indents every subquery, while reading the text the way PostgreSQL does. The panel above opens with an UPDATE ... RETURNING query loaded; paste your own over it to format a PostgreSQL query copied from a log, a migration or an ORM's debug output. Nothing leaves your browser.

The reason for a dialect-aware Postgres SQL beautifier is that a handful of characters mean something in PostgreSQL that they do not mean anywhere else. $$ opens a string. :: is a cast. #> is a JSON operator, not a comment. A generic formatter that misreads any of them will break a line inside a function body or detach a type from its value.

PostgreSQL dollar quoting

Dollar quoting is PostgreSQL's answer to escaping. Everything between $$ and the next $$ is one string, with no special characters at all: single quotes, backslashes and semicolons are just text. A tag between the dollars, as in $body$ ... $body$, lets one dollar-quoted string hold another, and lets the body contain a bare $$.

It is how function and procedure bodies are written, and it is the part of a migration a formatter is most likely to damage. This one reads a dollar-quoted string as one token from opening tag to matching closing tag and copies it through unchanged. It also tells the difference between a tag and a positional parameter: $1 is a parameter, $1$ would not be valid, and $fn$ opens a string.

Casts with ::

created_at::date is PostgreSQL's shorthand for CAST(created_at AS date). The formatter keeps the double colon tight against both sides, so the type reads as part of the value, and applies your keyword case to built-in type names such as INT. Custom types and names like timestamptz are left as typed, because they are identifiers rather than reserved words.

RETURNING and ILIKE

RETURNING is what makes an INSERT, UPDATE or DELETE hand back the rows it changed, and here it gets its own line like any other clause, with the returned columns beneath it. ILIKE is the case-insensitive LIKE that PostgreSQL adds; it is treated as a keyword and follows your keyword case, and a WHERE built from several ILIKE conditions breaks on each AND and OR. ON CONFLICT ... DO UPDATE is laid out the same way, with the SET list below it.

Quoted identifiers

Double quotes in PostgreSQL always mean an identifier, never a string. They preserve case and allow reserved words, which is how an ORM ends up creating a table called "Users" with a column called "userId". Those names are copied byte for byte, and doubled quotes inside them are kept.

Block comments can also nest in PostgreSQL, so /* outer /* inner */ still a comment */ is a single comment, and the formatter treats it as one.

For the rules shared by every dialect, see the SQL formatter. Queries headed for MySQL or MariaDB belong in the MySQL formatter, which reads backticks and # comments instead.

Frequently asked questions

What is PostgreSQL dollar quoting for?

It is a way to write a string without escaping anything inside it. Text between $$ and $$, or between a tagged pair such as $body$ and $body$, is taken literally, so a function body full of single quotes and semicolons needs no doubling. The tag lets you nest one dollar-quoted string inside another, as long as the inner one uses a different tag.

Why does the formatter leave my function body on one line?

Because a dollar-quoted body is a string, and the formatter never edits the inside of a string. To PostgreSQL, the SQL inside a CREATE FUNCTION is just text until the function runs. Format the body as its own query, then paste it back between the dollar quotes.

Is x::int the same as CAST(x AS int)?

Yes, the double colon is PostgreSQL's shorthand for CAST, and the two produce the same result. The shorthand binds tighter than most operators, which is why the formatter keeps it glued to the value on both sides. CAST is the portable spelling if the query may run on another database.

Why do I have to quote some column names in Postgres?

PostgreSQL folds unquoted identifiers to lowercase, so userId and userid are the same name. A column created with double quotes, as many ORMs do for camelCase fields, keeps its capitals and can then only be reached with the same double quotes. The formatter copies quoted names exactly, capitals included.

Are $1 and $2 formatted like values?

Yes. Positional parameters from prepared statements and from drivers such as node-postgres are read as parameters, not as the start of a dollar-quoted string, so a query copied from application code formats cleanly.

Related tools

Updated