postgres-sql

What this skill does

# PostgreSQL SQL Dialect & Data Types

## Overview

PostgreSQL is far more than "SQL with a server." It ships a rich, standards-leading **SQL
dialect** and a deep **type system** that together replace patterns you'd otherwise hand-roll in
application code: atomic upserts, set-based row transformations, recursive graph walks, JSON
documents queried in-place, array and range columns, and tables that physically split themselves
by key. This skill documents the **Postgres-specific SQL surface** — the constructs and types
that distinguish Postgres from generic ANSI SQL.

**The mental-model shift:** in Postgres, things you'd normally do in a loop in app code become a
single declarative statement. "Insert or update" is one `INSERT ... ON CONFLICT`. "For each
parent, fetch its children" is a `LATERAL` join. "Walk this tree" is a `WITH RECURSIVE`. "Pick
the latest row per group" is `DISTINCT ON`. A JSON blob is a first-class `jsonb` column you index
and query, not a `TEXT` you parse. Reaching for these instead of procedural code is the whole
point.

> **Disambiguation — what this skill is NOT:**
> - **Not the `psql` client** — `\d`, `\copy`, `\watch`, meta-commands, prompts → **psql** skill.
> - **Not query tuning** — `EXPLAIN`, index choice, `VACUUM`, planner stats → **postgres-performance** skill.
> - **Not administration** — `postgresql.conf`, roles/auth, backup, replication → **postgres-admin** skill.
> - **Not contrib/extensions** — PostGIS, `pg_trgm`, `pg_stat_statements`, etc. → **postgres-extensions** skill.
> - **Not a generic SQL tutorial** — assumes you know `SELECT`/`JOIN`/`GROUP BY`; covers only what's *Postgres-specific*.

## When to Use This Skill

| Reach for it when you need to… | Postgres construct |
|---|---|
| Insert a row, or update it if it already exists (atomic upsert) | `INSERT ... ON CONFLICT DO UPDATE` |
| Return the rows a write touched (ids, computed values, before/after) | `RETURNING` (+ `OLD`/`NEW`, pg18+) |
| Apply insert/update/delete in one set-based pass from a source | `MERGE` (pg15+) |
| Reuse a subquery, walk a hierarchy, or write-then-return in one statement | CTEs / `WITH RECURSIVE` / data-modifying `WITH` |
| Rank, run totals, lag/lead, per-partition windows | window functions + frame clause |
| Subtotals across multiple grouping dimensions in one scan | `GROUPING SETS` / `CUBE` / `ROLLUP` |
| One row per group (e.g. latest per user) | `DISTINCT ON` |
| Join each left row to a subquery that depends on it | `LATERAL` |
| Aggregate only a subset of rows without a `CASE` hack | `FILTER (WHERE ...)` |
| A column auto-computed from others, or an auto-increment key | generated columns / identity columns |
| A huge table split physically by range/list/hash | declarative partitioning |
| Store, index, and query JSON documents | `jsonb` + operators + jsonpath + SQL/JSON |
| Columns holding arrays, ranges, composites, enums, or constrained domains | array / range / composite / enum / domain types |

## Prerequisites

Confirm the server version — it governs which constructs below will parse. SQL is executed by the
**server**, so the server version (not the client) is what matters:

```sql
SELECT version();          -- full build string
SHOW server_version;       -- e.g. 18.0
SELECT current_setting('server_version_num')::int;  -- e.g. 180000 (easy to compare)
```

```bash
psql -tAc 'show server_version_num'   # from the shell; 170000 = v17.0
```

**Version note:** PostgreSQL adds SQL features on **major** versions (one per year: 10, 11, …,
18; **19 is currently in beta**). Features stable since the **9.x era** are **bedrock** and shown
here **unannotated** ("unlisted = long-standing"). Anything newer carries an inline `(pgNN+)`
tag — meaning "needs PostgreSQL NN or later" — and **only when sourced** (release notes /
command docs). The full feature → minimum-version map with citations is in
[references/version-features.md](references/version-features.md). When a statement errors with a
**syntax error** on an older server, check the tag.

## Core Workflows

Each section is runnable. Tags like `(pg15+)` mark the minimum server version; untagged = bedrock.

### 1. Upsert: `INSERT ... ON CONFLICT`

Insert, but on a unique/PK conflict either update the existing row or skip. The special
`excluded` row holds the values that *would* have been inserted.

```sql
-- Update on conflict ("upsert"): bump quantity, keep an audit timestamp
INSERT INTO inventory (sku, qty) VALUES ('A-1', 10)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + excluded.qty, updated_at = now()
RETURNING sku, qty;

-- Insert-or-ignore: do nothing if it already exists
INSERT INTO tags (name) VALUES ('postgres') ON CONFLICT DO NOTHING;

-- Conflict target can be a column list, a constraint name, or a partial-index predicate
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
ON CONFLICT (email) WHERE active DO UPDATE SET name = excluded.name;
```

`ON CONFLICT (cols)` infers the arbiter index by column(s)/expression; `ON CONFLICT ON CONSTRAINT
name` names it explicitly. A `DO UPDATE` may carry its own `WHERE`. (pg19+ adds `ON CONFLICT DO
SELECT ... RETURNING` to read/lock the conflicting row.) Full grammar:
[references/dml-returning.md](references/dml-returning.md).

### 2. `RETURNING` — get data back from writes

Every `INSERT`/`UPDATE`/`DELETE`/`MERGE` can return columns from affected rows — no follow-up
`SELECT`, no race.

```sql
INSERT INTO orders (cust_id, total) VALUES (42, 99.50) RETURNING id, created_at;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 RETURNING balance;
DELETE FROM sessions WHERE expires < now() RETURNING id;

-- pg18+: reference both the OLD and NEW row explicitly
UPDATE accounts SET balance = balance + 50 WHERE id = 1
RETURNING old.balance AS before, new.balance AS after;
```

### 3. `MERGE` — set-based insert/update/delete (pg15+)

One statement reconciles a target table against a source, choosing an action per row.

```sql
MERGE INTO inventory AS t
USING shipments AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);
```

pg17+ adds three big pieces: a `RETURNING` clause (with `merge_action()` to see which branch
fired), `WHEN NOT MATCHED BY SOURCE` (act on target rows the source lacks — e.g. delete/flag),
and `MERGE` on updatable views.

```sql
MERGE INTO inventory AS t USING shipments AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED BY SOURCE THEN DELETE            -- pg17+
RETURNING merge_action(), t.sku, t.qty;           -- pg17+
```

**`MERGE` vs `ON CONFLICT`:** use `ON CONFLICT` for "insert these rows, resolve clashes" (needs a
unique constraint); use `MERGE` for "reconcile a whole source against a target" with arbitrary
match logic and deletes. More: [references/dml-returning.md](references/dml-returning.md).

### 4. CTEs: `WITH`, recursive, and data-modifying

A `WITH` clause names subqueries. Since pg12 a plain CTE is **inlined** when it's referenced once
and side-effect-free; use `MATERIALIZED` to force a one-time computed fence (an optimization
barrier) or `NOT MATERIALIZED` to force inlining.

```sql
WITH recent AS MATERIALIZED (        -- (pg12+ keyword) compute once, reuse
  SELECT * FROM events WHERE ts > now() - interval '1 day'
)
SELECT user_id, count(*) FROM recent GROUP BY user_id;
```

Recursive CTEs walk hierarchies/graphs (org charts, threads, BOMs):

```sql
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name FROM emp WHERE id = 1     -- anchor
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM emp e JOIN subordinates s ON e.manager_id = s.id  -- recursive term
)
SELECT * FROM subordinates;
```

Data-modifying CTEs run writes inside `WITH` and pipe their `RETURNING` output downstream — e.g.
archive-then-delete atomically:

```sql
WITH moved AS (
  DELETE FROM events WHERE ts < now() - interval '1 year' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;
``