Query toolbox

Shape of a maintainable query

SELECT
  o.id,
  c.email,
  o.total_cents,
  o.placed_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
  AND o.placed_at >= $2
ORDER BY o.placed_at DESC, o.id DESC
LIMIT $3;

This query specifies output, join condition, parameters, deterministic ordering, and a bound. The driver binds $1, $2, and $3; never concatenate user input into SQL.

Choosing a join form

SELECT c.id, c.email
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

Aggregates and windows differ

GROUP BY collapses rows. Window functions retain detail rows while calculating across a window.

SELECT
  customer_id,
  id AS order_id,
  total_cents,
  row_number() OVER (
    PARTITION BY customer_id
    ORDER BY placed_at DESC, id DESC
  ) AS recency_rank,
  sum(total_cents) OVER (PARTITION BY customer_id) AS lifetime_cents
FROM orders;

What CTEs are for

A CTE names a stage in a complex query; it is not an automatic optimization switch.

WITH recent_paid AS (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
    AND placed_at >= now() - interval '30 days'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM recent_paid
GROUP BY customer_id;

Pagination

Prefer keyset pagination for large result sets:

SELECT id, placed_at, total_cents
FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC
LIMIT 50;

Unlike a large OFFSET, this does not repeatedly skip earlier rows and behaves more predictably under concurrent inserts. The cursor must contain every ordering key.

PostgreSQL 19 syntax conveniences

PostgreSQL 19 (Beta as of this update) plans several small syntax additions. Do not send them to an older server; verify against the final release notes.

GROUP BY ALL groups by every non-aggregate, non-window target-list item, so the grouping list is not repeated:

SELECT customer_id, status, count(*)
FROM orders
GROUP BY ALL;

Window functions accept IGNORE NULLS / RESPECT NULLS for lead(), lag(), first_value(), last_value(), and nth_value():

SELECT customer_id, placed_at,
       lag(placed_at) IGNORE NULLS OVER (
         PARTITION BY customer_id ORDER BY placed_at
       ) AS previous_order_at
FROM orders;

INSERT ... ON CONFLICT DO SELECT ... RETURNING makes get-or-create a single atomic statement: the row is either inserted or the conflicting existing row is returned. A conflict_target and a RETURNING clause are both required for DO SELECT, and an optional locking clause (FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE) locks the conflicting row against concurrent updates.

INSERT INTO customers (email)
VALUES ($1)
ON CONFLICT (email) DO SELECT
RETURNING id;

See the PostgreSQL 19 INSERT documentation.

Pre-flight checklist

• Is the output contract explicit and minimal?
• Can any join multiply rows?
• Is NULL semantics intentional?
• Does ordering have a unique final tie-breaker?
• Does the driver bind every input value?
• Do you need statement timeout and a result limit?