Formatting PostgreSQL queries and PL/pgSQL

How SQL Format lays out PostgreSQL-specific syntax, from :: casts and $1 parameters to RETURNING, ON CONFLICT and PL/pgSQL function bodies, with real formatter output for each, plus a few naming habits that make PostgreSQL code easier to read.

What the formatter recognises

SQL Format has no dialect switch: it formats the SQL that most databases share. On top of that it understands a few things that are specific to PostgreSQL:

• :: type casts, such as created_at::date;
• JSON, array and pattern operators such as ->, ->>, #>>, @>, <@, && and ~*, plus array literals and subscripts like ARRAY['beta'] and tags[1];
• positional parameters $1, $2, as used by prepared statements and most PostgreSQL drivers;
• dollar-quoted bodies ($$ … $$) of CREATE FUNCTION, CREATE PROCEDURE and DO, which get a PL/pgSQL block layout instead of being treated as one long string.

RETURNING and ON CONFLICT get a clause layout of their own, shown in the sections below. For how other databases differ, see SQL dialect differences that affect formatting.

Casts and parameters

Queries written for a driver or for PREPARE are full of $n placeholders and casts that tell PostgreSQL what type a parameter has. The formatter keeps the placeholders exactly as written and writes each cast glued to its operands, as in $2::TIMESTAMPTZ, so a cast reads as one unit. The type name after it follows the keyword case option, so DATE and TIMESTAMPTZ become uppercase here:

select o.id, o.created_at::date as order_day, o.total::text as total_label, o.status from orders o where o.customer_id = $1 and o.created_at >= $2::timestamptz and o.created_at < $2::timestamptz + interval '1 month' order by o.created_at desc limit $3

SELECT o.id,
    o.created_at::DATE AS order_day,
    o.total::TEXT AS total_label,
    o.status
FROM orders o
WHERE o.customer_id = $1
AND o.created_at >= $2::TIMESTAMPTZ
AND o.created_at < $2::TIMESTAMPTZ + INTERVAL '1 month'
ORDER BY o.created_at DESC
LIMIT $3

The same query with lowercase keywords, a style many PostgreSQL projects use. The casts follow along, so ::date and ::timestamptz stay lowercase:

select o.id,
    o.created_at::date as order_day,
    o.total::text as total_label,
    o.status
from orders o
where o.customer_id = $1
and o.created_at >= $2::timestamptz
and o.created_at < $2::timestamptz + interval '1 month'
order by o.created_at desc
limit $3

Both styles are fine. What matters is that a project uses one of them everywhere; the SQL style guide discusses how to choose.

JSON and array operators

PostgreSQL's multi-character operators are kept intact and written with a space on each side, so payload->>'email' becomes payload ->> 'email' and a chain such as -> 'address' #>> '{city}' stays readable. Array literals and subscripts stay glued to their brackets:

select e.id, e.payload->>'email' as email, e.payload->'address'#>>'{city}' as city, e.tags[1] as first_tag from events e where e.payload @> '{"type": "signup"}' and e.tags && ARRAY['beta'] and e.created_at >= $1::timestamptz

SELECT e.id,
    e.payload ->> 'email' AS email,
    e.payload -> 'address' #>> '{city}' AS city,
    e.tags[1] AS first_tag
FROM events e
WHERE e.payload @> '{"type": "signup"}'
AND e.tags && ARRAY['beta']
AND e.created_at >= $1::TIMESTAMPTZ

The JSON text inside the string literal is left exactly as typed. ARRAY is not on the keyword list, so it keeps the case you typed.

RETURNING

RETURNING gives back values from the rows an INSERT, UPDATE or DELETE touched, which saves a second round trip. The formatter treats it as a clause: it starts its own line, after WHERE, with the first returned column on the same line and the others on indented lines below, like a GROUP BY list. The SET list works the same way, one assignment per line:

update accounts set balance = balance - $1, updated_at = now() where id = $2 and balance >= $1 returning id, balance

UPDATE accounts
SET balance = balance - $1,
    updated_at = now()
WHERE id = $2
AND balance >= $1
RETURNING id,
    balance

In review, the balance >= $1 condition is the line that matters most, and having it on its own line next to the other conditions makes it hard to miss.

ON CONFLICT … DO UPDATE

An upsert has several parts that reviewers need to check separately: the column list, the values, the conflict target and the update. The formatter gives each part its own place, so each one is easy to find:

insert into inventory (sku, warehouse_id, quantity) values ($1, $2, $3) on conflict (sku, warehouse_id) do update set quantity = inventory.quantity + excluded.quantity, updated_at = now() returning sku, quantity

INSERT INTO inventory (
    sku,
    warehouse_id,
    quantity
)
VALUES (
    $1,
    $2,
    $3
)
ON CONFLICT (sku, warehouse_id) DO UPDATE
SET quantity = inventory.quantity + excluded.quantity,
    updated_at = now()
RETURNING sku,
    quantity

INSERT INTO inventory ( stays on one line, with the column list indented below it, and the VALUES list gets the same treatment. ON CONFLICT (sku, warehouse_id) DO UPDATE stays on one line, so the conflict target and the action read together; DO NOTHING is kept on that line in the same way. SET and RETURNING then start their own lines, with their continuation items indented.

Two PostgreSQL facts worth keeping in mind when reading upserts: the conflict target must match a unique index or constraint, and excluded refers to the row that was proposed for insertion, while the table name (inventory here) refers to the row already stored.

Function bodies

A PL/pgSQL function body is a string literal to the SQL parser, which is why it is wrapped in dollar quotes. Many tools therefore leave it on one line. SQL Format formats it as PL/pgSQL instead: the CREATE FUNCTION header stays on one line up to AS $$, then DECLARE, BEGIN and END start at the left margin and every statement is indented one level. IF, ELSIF, ELSE and END IF line up, with their statements indented one more level. SQL statements inside the body get the usual clause layout, and the closing $$; goes on its own line:

create or replace function close_stale_orders(p_days integer) returns integer language plpgsql as $$ declare v_count integer; begin if p_days < 1 then raise exception 'p_days must be positive, got %', p_days; elsif p_days > 365 then raise notice 'closing orders older than one year'; end if; update orders set status = 'closed' where status = 'open' and created_at < now() - p_days * interval '1 day'; get diagnostics v_count = row_count; return v_count; end; $$;

CREATE OR REPLACE FUNCTION close_stale_orders(p_days INTEGER) RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
    v_count INTEGER;
BEGIN
    IF p_days < 1 THEN
        RAISE EXCEPTION 'p_days must be positive, got %', p_days;
    ELSIF p_days > 365 THEN
        RAISE NOTICE 'closing orders older than one year';
    END IF;
    UPDATE orders
    SET status = 'closed'
    WHERE status = 'open'
    AND created_at < now() - p_days * INTERVAL '1 day';
    GET DIAGNOSTICS v_count = row_count;
    RETURN v_count;
END;
$$;

Keyword case applies inside the body too. Words such as plpgsql and row_count are not on the keyword list and stay as typed, and keyword case never touches string literals such as the RAISE messages.

DO blocks, loops and exception handlers

Anonymous DO blocks are common in migrations and maintenance scripts. They get the same layout. A FOR … IN … LOOP header stays on one line, the loop body is indented, and END LOOP lines up with FOR. A nested BEGIN … EXCEPTION … END block is indented as a unit, with each WHEN handler one level under EXCEPTION and its statements one level further. Here with lowercase keywords:

do $$ declare r record; begin for r in select id from tenants where enabled loop begin perform refresh_tenant_stats(r.id); exception when others then raise warning 'tenant % failed: %', r.id, sqlerrm; end; end loop; end $$;

do $$
declare
    r record;
begin
    for r in select id from tenants where enabled loop
        begin
            perform refresh_tenant_stats(r.id);
        exception
            when others then
                raise warning 'tenant % failed: %', r.id, sqlerrm;
        end;
    end loop;
end
$$;

That nesting is exactly what you need to see when reviewing error handling: which statements are protected by the handler, and which are not. With the default four-space indentation each level is easy to follow; if deep nesting pushes code too far right, two spaces is a common choice for PL/pgSQL.

Prefer lowercase snake_case identifiers

PostgreSQL folds unquoted identifiers to lowercase. CREATE TABLE CustomerOrders creates a table called customerorders, and SELECT * FROM CUSTOMERORDERS finds it. Only double quotes preserve case, and once a name is created with quotes and capitals, every query must quote it exactly the same way, forever:

CREATE TABLE "CustomerOrders" ("OrderId" bigint, "createdAt" timestamptz);
-- every later query must repeat the quotes exactly:
SELECT "OrderId", "createdAt" FROM "CustomerOrders";
-- this fails: unquoted names are folded to customerorders / orderid
SELECT OrderId FROM CustomerOrders;

-- snake_case needs no quotes and reads the same in any case
CREATE TABLE customer_orders (order_id bigint, created_at timestamptz);
SELECT order_id, created_at FROM customer_orders;

Lowercase snake_case avoids the problem entirely: names read the same in uppercase and lowercase SQL, need no quotes in queries, and survive a change of keyword case in the formatter or an ORM. The same applies to column names generated from camelCase code; map them in the application instead of quoting them in the database. Also avoid reserved words such as user or order as names, since they need quotes too.

text or varchar?

In PostgreSQL, text and varchar are stored and handled the same way; varchar(n) only adds a maximum-length check. So the choice is mainly about what the declaration communicates to the next reader. A useful convention:

• use text when there is no real business limit, rather than an arbitrary varchar(255) that suggests a rule nobody decided;
• when a limit or format is a real rule, express it as a named CHECK constraint, which documents the intent and shows up by name in error messages;
• avoid char(n) for variable data, because it pads values with spaces.

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL,
    -- the business rule lives in a named constraint, not in a type length
    country     text NOT NULL CONSTRAINT ck_customers_country_iso2
                    CHECK (char_length(country) = 2),
    notes       text
);

Teams that need portability to databases where varchar(n) is required may standardise on it instead; consistency within a schema is what readers benefit from most. For more on writing schema changes, see the migration formatting checklist.

Frequently asked questions