PostgreSQL SQL Dialect & Data Types Skill

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:

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)

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. 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.

-- 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 ('a@b.com', '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.

2. `RETURNING` — get data back from writes

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

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.

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.

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.

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.

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):

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:

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

Full treatment (SEARCH/CYCLE clauses, evaluation rules): references/queries.md.

5. Window functions & frames

Compute across a set of rows related to the current row without collapsing them.

SELECT
  user_id, amount, ts,
  row_number()  OVER w                                   AS seq,
  sum(amount)   OVER (PARTITION BY user_id ORDER BY ts)  AS running_total,
  lag(amount)   OVER w                                   AS prev_amount
FROM payments
WINDOW w AS (PARTITION BY user_id ORDER BY ts);

The frame clause controls which peer rows feed a window aggregate — ROWS/RANGE are bedrock; GROUPS mode and frame EXCLUDE are pg11+:

avg(price) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  -- 7-day moving avg
sum(x)     OVER (ORDER BY g GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)  -- (pg11+)

More (frame exclusion, all window fns): references/queries.md.

6. Grouping extensions, `DISTINCT ON`, `LATERAL`, `FILTER`

-- Subtotals + grand total in ONE scan (GROUPING SETS / ROLLUP / CUBE)
SELECT region, product, sum(sales)
FROM s GROUP BY ROLLUP (region, product);

-- One row per group: latest payment per user (ORDER BY must lead with the DISTINCT ON keys)
SELECT DISTINCT ON (user_id) user_id, amount, ts
FROM payments ORDER BY user_id, ts DESC;

-- LATERAL: the subquery sees columns from the left side (top-3 orders per customer)
SELECT c.id, o.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT * FROM orders o WHERE o.cust_id = c.id ORDER BY o.total DESC LIMIT 3
) o;

-- FILTER: per-aggregate row subset, cleaner than sum(CASE WHEN ...)
SELECT count(*) AS total,
       count(*) FILTER (WHERE status = 'paid') AS paid
FROM orders;

Details + GROUPING(): references/queries.md.

7. Generated & identity columns

CREATE TABLE people (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- (pg10+) prefer over serial
  first     text,
  last      text,
  full_name text GENERATED ALWAYS AS (first || ' ' || last) STORED  -- (pg12+) STORED
);

Identity columns GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY (pg10+) are the SQL-standard auto-increment; prefer them over the old serial/bigserial (which leak a separate sequence and have ownership quirks). ALWAYS blocks manual inserts (override with OVERRIDING SYSTEM VALUE); BY DEFAULT allows them.
Generated columns are computed from other columns. STORED (pg12+) materializes on write. VIRTUAL (computed on read) arrived in pg18 and is now the default kind — so on pg18+ a bare GENERATED ALWAYS AS (...) is virtual; always write STORED explicitly if you need the stored behavior and want version-portable DDL.

More (serial migration, OVERRIDING): references/data-types.md.

8. Declarative partitioning

Split one logical table into physical partitions by range, list (pg10+), or hash (pg11+).

CREATE TABLE measurement (city_id int, logdate date, peaktemp int)
  PARTITION BY RANGE (logdate);

CREATE TABLE measurement_2026
  PARTITION OF measurement FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;  -- (pg11+) catch-all

-- Attach/detach existing tables as partitions (DETACH ... CONCURRENTLY is pg14+)
ALTER TABLE measurement ATTACH PARTITION m_old FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
ALTER TABLE measurement DETACH PARTITION m_old CONCURRENTLY;        -- (pg14+)

Routing, sub-partitioning, ATTACH/DETACH, partition-wise joins/aggregates, and pg19's MERGE/SPLIT PARTITIONS: references/partitioning.md.

9. JSON / JSONB

jsonb (binary, indexable, deduped keys — the default choice) and json (text, preserves formatting) are bedrock. Navigate and test with operators; query with jsonpath (pg12+); shred with SQL/JSON (pg16/pg17).

SELECT data->'address'->>'city'  AS city,   -- -> keeps json, ->> returns text
       data#>>'{tags,0}'         AS first_tag
FROM docs
WHERE data @> '{"active": true}'             -- containment (jsonb), GIN-indexable
  AND data ? 'email'                         -- key exists
  AND data @@ '$.age > 18';                  -- jsonpath predicate (pg12+)

-- SQL/JSON query functions (pg17+): extract scalars / objects / a whole table
SELECT JSON_VALUE(data, '$.age' RETURNING int) FROM docs;          -- (pg17+)
SELECT * FROM JSON_TABLE(                                           -- (pg17+)
  (SELECT data FROM docs WHERE id = 1), '$.items[*]'
  COLUMNS (name text PATH '$.name', qty int PATH '$.qty'));

The SQL/JSON split matters: IS JSON + the JSON_ARRAY/JSON_OBJECT constructor family are pg16+; the query functions JSON_TABLE/JSON_QUERY/JSON_VALUE/JSON_EXISTS and the JSON()/JSON_SCALAR()/JSON_SERIALIZE() constructors are pg17+. Full operator/jsonpath/SQL- JSON reference: references/json.md.

10. Arrays, ranges/multiranges, composites, enums, domains

-- Arrays: literal, contains (@>), overlaps (&&), membership, unnest
SELECT * FROM posts WHERE tags @> ARRAY['sql'] AND 'postgres' = ANY(tags);
SELECT id, unnest(tags) AS tag FROM posts;

-- Ranges + multiranges (multirange types: pg14+)
SELECT int4range(1, 10) @> 5;                         -- range containment → true
SELECT '{[1,5], [10,20]}'::int4multirange;            -- (pg14+) noncontiguous set of ranges
CREATE TABLE booking (room int, during tsrange,
  EXCLUDE USING gist (room WITH =, during WITH &&));  -- no double-booking

-- Enums and domains
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE DOMAIN positive_int AS int CHECK (VALUE > 0);

-- Composite / row types and uuid generation
SELECT ROW(1, 'a')::my_type;
SELECT gen_random_uuid();   -- (pg13+ built-in; was pgcrypto-only before)
SELECT uuidv7();            -- (pg18+) time-ordered UUID, index-friendly

Operator tables, subscripting/slicing, array_agg, range bounds & functions, ANY_VALUE() (pg16+): references/data-types.md.

Quick Reference

| Construct | Purpose | Since | |---|---|---| | INSERT ... ON CONFLICT (c) DO UPDATE SET ... = excluded.... | Atomic upsert | 9.5 (bedrock) | | INSERT ... ON CONFLICT DO NOTHING | Insert-or-ignore | bedrock | | ... RETURNING col, ... | Get rows back from a write | bedrock | | RETURNING old.col, new.col | Before/after values | pg18+ | | MERGE INTO ... USING ... WHEN MATCHED/NOT MATCHED THEN ... | Set-based reconcile | pg15+ | | MERGE ... WHEN NOT MATCHED BY SOURCE · ... RETURNING merge_action() | Source-absent action · returning | pg17+ | | WITH cte AS [NOT] MATERIALIZED (...) | Inlining control | pg12+ | | WITH RECURSIVE ... | Hierarchy/graph traversal | bedrock | | WITH x AS (DELETE ... RETURNING) INSERT ... | Data-modifying CTE | bedrock | | func() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...) | Window function + frame | bedrock | | ... GROUPS ... · EXCLUDE CURRENT ROW/GROUP/TIES | Frame mode / exclusion | pg11+ | | GROUP BY ROLLUP/CUBE/GROUPING SETS (...) | Multi-level subtotals | bedrock | | DISTINCT ON (cols) | One row per group | bedrock | | CROSS JOIN LATERAL (subquery using left cols) | Correlated join | bedrock | | agg(...) FILTER (WHERE ...) | Per-aggregate filtering | bedrock | | GENERATED { ALWAYS \| BY DEFAULT } AS IDENTITY | SQL-standard auto-increment | pg10+ | | GENERATED ALWAYS AS (expr) STORED · ... VIRTUAL | Computed column (virtual default) | STORED pg12+ · VIRTUAL pg18+ | | PARTITION BY RANGE/LIST · ... HASH · ... DEFAULT | Declarative partitioning | range/list pg10+ · hash/default pg11+ | | -> ->> #> #>> · @> ? \|\| · @@ @? | JSON navigate / test / jsonpath | jsonpath pg12+ | | IS JSON · JSON_ARRAY/JSON_OBJECT(...) · ANY_VALUE() | JSON predicate / constructors / agg | pg16+ | | JSON_TABLE/JSON_QUERY/JSON_VALUE/JSON_EXISTS · JSON()/JSON_SCALAR/JSON_SERIALIZE | SQL/JSON query + constructors | pg17+ | | int4multirange, '{[1,5],[8,9]}'::... | Multirange types | pg14+ | | gen_random_uuid() · uuidv7()/uuidv4() | UUID generation | builtin pg13+ · v7/v4 pg18+ |

Troubleshooting

syntax error at or near "MERGE" / "JSON_TABLE" / "MATERIALIZED" — the server is older than the feature. Check SHOW server_version against the (pgNN+) tag and references/version-features.md. The parser rejects the keyword, so it's a version problem, not a typo.
ON CONFLICT → there is no unique or exclusion constraint matching the ON CONFLICT specification — the conflict target must match an actual unique index / constraint (or a partial-index predicate). Add the constraint or fix the column list.
A GENERATED AS (...) column behaves as virtual on pg18 but errored on pg17 — pg18 made VIRTUAL the default; older servers only support STORED. Write STORED explicitly for portable DDL.
->> vs -> — -> returns json/jsonb; ->> returns text. Comparing the result of -> to a string fails or misbehaves; use ->> (or cast) when you want text.
@> does nothing useful on a json (not jsonb) column — containment/existence operators and GIN indexing are jsonb features. Store documents you query as jsonb.
DISTINCT ON returns unexpected rows — the leading ORDER BY expressions must start with the DISTINCT ON columns; otherwise "which row per group" is undefined.
Inserting into an IDENTITY ... GENERATED ALWAYS column fails — that's intended; use OVERRIDING SYSTEM VALUE, or define the column BY DEFAULT instead.
A row "doesn't fit any partition" — without a DEFAULT partition (pg11+) an out-of-range insert errors. Add a default partition or the right range/list partition.

References

references/dml-returning.md — INSERT ... ON CONFLICT (full conflict-target & action grammar, excluded, partial-index inference, DO SELECT pg19+), RETURNING incl. OLD/NEW, the complete MERGE grammar with per-version notes, and data-modifying CTEs.
references/queries.md — CTEs (MATERIALIZED, recursive, SEARCH/ CYCLE), window functions + the full frame-clause grammar, GROUPING SETS/CUBE/ROLLUP + GROUPING(), DISTINCT ON, LATERAL, and FILTER.
references/json.md — json vs jsonb, every operator, the jsonpath language (pg12+), and the SQL/JSON functions with the precise pg16-vs-pg17 split.
references/data-types.md — arrays, ranges & multiranges (pg14+), composite/row types, enums, domains, generated & identity columns, and notable functions (gen_random_uuid, uuidv7, ANY_VALUE).
references/partitioning.md — declarative partitioning end to end: range/list/hash, DEFAULT, ATTACH/DETACH, sub-partitioning, partition-wise plans, pg19 MERGE/SPLIT.
references/version-features.md — the sourced feature → minimum-version table, how to read the (pgNN+) tags, and citations.

Resources

SQL command reference: https://www.postgresql.org/docs/current/sql-commands.html
Queries (CTEs, window, grouping): https://www.postgresql.org/docs/current/queries.html
Data types: https://www.postgresql.org/docs/current/datatype.html
JSON functions & SQL/JSON: https://www.postgresql.org/docs/current/functions-json.html
Release notes (per-version "what's new"): https://www.postgresql.org/docs/release/

Agent Skills: PostgreSQL SQL Dialect & Data Types

Install this agent skill to your local

Skill Files