PostgreSQL Formatter

Postgres quoting rules the formatter follows

PostgreSQL is stricter than MySQL about quotes, and the PostgreSQL setting mirrors that:

• "double quotes" always mean an identifier. WHERE city = "Berlin" formats without complaint, but Postgres will look for a column called Berlin.
• Plain strings escape a quote by doubling it: 'it''s'. A backslash is an ordinary character, so 'O\'Brien' leaves the string open.
• E'line one\nline two' strings do honour backslash escapes, and U&'d\0061t\+000061' Unicode strings are read as one token.
• Backticks are not quote characters in Postgres, so a query copied from MySQL is rejected at the first one.
• Block comments nest: /* outer /* inner */ still a comment */ is treated as a single comment, as the server does.

Casts, JSONB and parameters

The :: cast stays glued to its operand (created_at::date, total::numeric(12,2)), while JSON and JSONB operators such as ->>, #>> and @> get a space on each side. The ?, ?| and ?& key-existence operators are a common trap: in this dialect a ? is never mistaken for a bind placeholder, so payload ? 'coupon' survives formatting.

Server-side parameters use $1, $2 and so on, and they are kept intact in WHERE, LIMIT and VALUES positions. If your driver rewrites ? into $n for you, format the query as the driver sends it.

Function bodies and DO blocks stay verbatim

Anything between $$ markers, or a tagged pair like $body$ … $body$, is one string literal to PostgreSQL, and the formatter treats it the same way. A CREATE FUNCTION header is formatted, but the PL/pgSQL inside the dollar quotes is copied through character for character, including its original line breaks. DO $$ … $$ blocks behave the same.

To tidy the queries inside a function, paste them on their own, format, and put them back between the markers. A dollar-quoted body with mismatched tags ($body$ opened, $$ closed) is reported as never closed.

Clauses with Postgres-specific layout

ON CONFLICT (sku, warehouse_id) DO UPDATE sits on one line, followed by an indented SET list that can refer to excluded.qty. RETURNING is a clause of its own after INSERT, UPDATE and DELETE, which makes data-modifying CTEs (WITH moved AS (DELETE … RETURNING *) INSERT …) easy to read. FILTER (WHERE …) on an aggregate is expanded across lines, and ILIKE, ANY (tags) and INTERVAL '30 days' are recognised.

Two layouts may surprise you. DISTINCT ON (customer_id) is split after DISTINCT, which reads oddly but is still valid. Column aliases that happen to be keywords, like day or month, are upper-cased along with the real keywords. Quote them (AS "day") or set Keyword case to “As written”.

Suggested settings

For application queries with many CTEs, Commas → Leading and an indent of 2 keep long column lists diff-friendly. Set Blank lines between statements to 2 when formatting a migration file so each statement stands apart. Minify is handy for embedding a query in a Go or Python string, and it never touches string contents or quoted identifiers. Formatting happens locally in the browser tab, which matters when the WHERE clause contains real customer emails. For naming and layout conventions, the SQL style guide goes further.

Examples

insert into inventory (sku, warehouse_id, qty) values ('SKU-1001', 3, 40) on conflict (sku, warehouse_id) do update set qty = inventory.qty + excluded.qty, updated_at = now() returning sku, qty;

INSERT INTO
  inventory (sku, warehouse_id, qty)
VALUES
  ('SKU-1001', 3, 40)
ON CONFLICT (sku, warehouse_id) DO UPDATE
SET
  qty = inventory.qty + excluded.qty,
  updated_at = now()
RETURNING
  sku,
  qty;

select date_trunc('week', o.created_at)::date as week_start, o.payload->>'channel' as channel, count(*) filter (where o.payload ? 'coupon') as with_coupon, sum(o.total)::numeric(12,2) as revenue from orders o where o.payload @> '{"gift": false}'::jsonb and o.created_at >= now() - interval '90 days' group by 1, 2 order by 1, 2;

SELECT
  date_trunc('week', o.created_at)::date AS week_start,
  o.payload ->> 'channel' AS channel,
  count(*) FILTER (
    WHERE
      o.payload ? 'coupon'
  ) AS with_coupon,
  sum(o.total)::NUMERIC(12, 2) AS revenue
FROM
  orders o
WHERE
  o.payload @> '{"gift": false}'::JSONB
  AND o.created_at >= now() - INTERVAL '90 days'
GROUP BY
  1,
  2
ORDER BY
  1,
  2;

with stale as (delete from cart_items where updated_at < now() - interval '30 days' and customer_id = $1 returning cart_id, sku, qty) insert into abandoned_cart_items (cart_id, sku, qty, archived_at) select cart_id, sku, qty, now() from stale returning cart_id;

WITH
  stale AS (
    DELETE FROM cart_items
    WHERE
      updated_at < now() - INTERVAL '30 days'
      AND customer_id = $1
    RETURNING
      cart_id
    , sku
    , qty
  )
INSERT INTO
  abandoned_cart_items (cart_id, sku, qty, archived_at)
SELECT
  cart_id
, sku
, qty
, now()
FROM
  stale
RETURNING
  cart_id;

create or replace function customer_lifetime_value(p_customer_id bigint) returns numeric language sql stable as $$ select coalesce(sum(total), 0) from orders where customer_id = p_customer_id and status <> 'refunded' $$;

CREATE OR REPLACE FUNCTION customer_lifetime_value (p_customer_id BIGINT) returns NUMERIC language sql stable AS $$ select coalesce(sum(total), 0) from orders where customer_id = p_customer_id and status <> 'refunded' $$;

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/mysql
• sql formatter/redshift
• sql validator
• sql minifier
• JSON to SQL
• sql style guide
• sql unbalanced parentheses