Newer SQL recipes

Name a subquery, then aggregate over it

A non-recursive WITH block names an intermediate result you can filter, join and aggregate — all resolved server-side in one request. The CTE body reads a real table.

WITH paid AS (
  SELECT customer_id, amount_cents
    FROM shop.orders
   WHERE status = 'paid'
)
SELECT customer_id,
       count(*)          AS n,
       sum(amount_cents) AS total
  FROM paid
 GROUP BY customer_id

{ "kind": "select",
  "columns": ["customer_id", "n", "total"],
  "rows": [
    { "customer_id": 1, "n": 2, "total": 18800 },
    { "customer_id": 2, "n": 1, "total": 22000 }
  ] }

Recursive CTEs (WITH RECURSIVE) are the one form not reachable over /sql yet — you get a clear 400, never a silent re-interpretation.

Bind values with a params array

Keep SQL and values apart: write positional $1, $2 placeholders and pass a params array alongside the statement. No string interpolation, no injection surface.

{
  "sql": "SELECT id, amount_cents, status FROM shop.orders WHERE customer_id = $1 AND amount_cents > $2 ORDER BY amount_cents DESC",
  "params": [1, 1000]
}

{ "kind": "select",
  "columns": ["id", "amount_cents", "status"],
  "rows": [
    { "id": 1, "amount_cents": 14900, "status": "paid" },
    { "id": 4, "amount_cents": 3900,  "status": "paid" }
  ] }

Bind parameters are also how you write timestamps today — pass the value in params rather than as an inline TIMESTAMP '…' literal:
{ "sql": "INSERT INTO shop.orders (customer_id, amount_cents, status, created_at) VALUES ($1,$2,$3,$4)", "params": [1, 14900, "paid", "2026-01-04 10:00:00"] } → {"inserted": 1}.

Roll rows up into time buckets

The dashboard shape: bucket a timestamp column by day / month and aggregate per bucket, in a single GROUP BY.

SELECT date_trunc('month', created_at) AS m,
       count(*)          AS n,
       sum(amount_cents) AS rev
  FROM shop.orders
 GROUP BY date_trunc('month', created_at)

{ "kind": "select",
  "columns": ["m", "n", "rev"],
  "rows": [
    { "m": 1767225600000, "n": 2, "rev": 18800 },
    { "m": 1769904000000, "n": 2, "rev": 29200 }
  ] }

Buckets come back as epoch-milliseconds (1767225600000 = 2026-01-01). Sort the buckets in your app for now — ORDER BY over a date_trunc group key isn't wired through /sql yet.

Branch inline with CASE

Derive a column from a per-row condition without a second query — CASE WHEN … THEN … ELSE … END evaluates in the SELECT list.

SELECT id, amount_cents,
       CASE WHEN amount_cents >= 10000 THEN 'big'
            ELSE 'small' END AS tier
  FROM shop.orders
 ORDER BY id

{ "kind": "select",
  "columns": ["id", "amount_cents", "tier"],
  "rows": [
    { "id": 1, "amount_cents": 14900, "tier": "big"   },
    { "id": 3, "amount_cents": 7200,  "tier": "small" },
    { "id": 4, "amount_cents": 3900,  "tier": "small" },
    { "id": 6, "amount_cents": 22000, "tier": "big"   }
  ] }

Ask the engine about itself

Standard Postgres introspection works — read information_schema / pg_catalog, and inspect session state with SHOW and current_schema(). This is what lets ORMs and SQL clients introspect a schema.

SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'orders'

{ "kind": "select",
  "columns": ["column_name"],
  "rows": [
    { "column_name": "id" }, { "column_name": "customer_id" },
    { "column_name": "amount_cents" }, { "column_name": "status" },
    { "column_name": "created_at" }
  ] }

SHOW search_path → public · SELECT current_schema() → public. SET search_path and GUCs persist for the session.