postgres-syntax-dml-ddl

name	postgres-syntax-dml-ddl
description	Use when authoring CREATE TABLE / ALTER TABLE / INSERT / UPDATE / DELETE statements, picking IDENTITY vs SERIAL, or learning the RETURNING clause. Prevents shipping SERIAL columns (deprecated, breaks GENERATED ALWAYS guarantees), forgetting RETURNING on DML, and missing GENERATED columns for derived data. Covers CREATE TABLE grammar, IDENTITY columns (v10+), SERIAL deprecation, GENERATED AS STORED (v12+), RETURNING on INSERT/UPDATE/DELETE/MERGE (v17+ for MERGE), DEFAULT semantics, ALTER TABLE basics, DROP/TRUNCATE, transactional DDL. Keywords: CREATE TABLE, ALTER TABLE, INSERT, UPDATE, DELETE, RETURNING, IDENTITY, GENERATED AS IDENTITY, SERIAL, GENERATED AS STORED, DEFAULT, TRUNCATE, RESTART IDENTITY, transactional DDL, my serial sequence is broken, how to get inserted id back, why is generated column not working
license	MIT
compatibility	Designed for Claude Code. Requires PostgreSQL 15, 16, or 17.
metadata	{"author":"OpenAEC-Foundation","version":"1.0"}

postgres-syntax-dml-ddl

Quick Reference :

PostgreSQL's day-to-day SQL surface is CREATE / ALTER / DROP TABLE for DDL plus INSERT / UPDATE / DELETE / TRUNCATE for DML. Almost every statement obeys two guarantees that shape correct usage : (1) transactional DDL , BEGIN; ALTER TABLE ...; CREATE INDEX ...; ROLLBACK; undoes the entire schema change atomically (exceptions : CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, DETACH PARTITION ... CONCURRENTLY, ALTER TYPE ... ADD VALUE, ALTER SYSTEM, VACUUM, CREATE DATABASE) ; and (2) RETURNING on INSERT, UPDATE, DELETE, and (v17+) MERGE returns the affected rows in the same round trip, eliminating the "INSERT then SELECT to fetch the generated id" anti-pattern.

For auto-generated primary keys ALWAYS use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY (SQL standard, since v10) ; NEVER use the legacy serial / bigserial pseudo-types : they create an implicit sequence that is owned by the column but is a separate object you must grant USAGE on, and they cannot be made GENERATED ALWAYS. GENERATED ALWAYS AS (expr) STORED materializes a derived column at write time (the only flavor supported in v15-17 ; virtual generated columns ship in v18). For derived data that is queried more often than it changes, prefer a STORED generated column over an application-side compute.

When To Use This Skill :

ALWAYS use this skill when :

• Authoring a new CREATE TABLE and choosing the primary-key column type / DEFAULT / GENERATED columns / table-level constraints
• Migrating off serial / bigserial to GENERATED AS IDENTITY
• Writing INSERT / UPDATE / DELETE statements that need to return data to the caller (RETURNING clause)
• Adding columns, constraints, or NOT NULL with ALTER TABLE (BASIC grammar only ; lock minimization lives in postgres-impl-zero-downtime-migrations)
• Emptying a table with TRUNCATE and deciding RESTART IDENTITY vs CONTINUE IDENTITY

NEVER use this skill for :

• Zero-downtime ALTER TABLE / CREATE INDEX CONCURRENTLY playbooks : see postgres-impl-zero-downtime-migrations
• UPSERT and MERGE patterns deep-dive : see postgres-syntax-merge-upsert
• Bulk loading with COPY : see postgres-impl-bulk-loading
• Built-in types, JSONB, arrays : see postgres-core-architecture references/type-system.md

Decision Trees :

Pick the right auto-generated primary-key column :

Need a unique, server-allocated integer id?
├── New code on v15/16/17 :
│   ├── Application MUST NEVER supply the value : GENERATED ALWAYS AS IDENTITY
│   ├── Allow optional client-supplied id (data migration / replication) :
│   │       GENERATED BY DEFAULT AS IDENTITY
│   └── NEVER : serial / bigserial / smallserial (deprecated since v10)
├── UUID instead of integer (sharding, federated systems) :
│   id uuid PRIMARY KEY DEFAULT gen_random_uuid()   -- pgcrypto/v13+ built-in
└── Composite natural key (no surrogate) :
    PRIMARY KEY (tenant_id, external_ref)

When should I add a GENERATED ALWAYS AS (expr) STORED column? :

Is the value a deterministic function of other columns in the same row?
├── No : not a candidate. Use a view or trigger instead.
└── Yes :
    ├── Read-heavy, almost-never-updated : STORED generated column (best fit)
    ├── Write-heavy, rarely-read : application-side compute
    └── Needs to be indexed on the derived value :
        STORED generated column + B-tree / expression index

Pick the right delete-everything statement :

Need to empty a table?
├── A few rows only, FK-referenced data must trigger ON DELETE : DELETE FROM t
├── All rows, no FK references from other tables : TRUNCATE t [RESTART IDENTITY]
├── All rows, has FK refs : TRUNCATE t, ref_t, ...   -- list all referenced
│                            -- OR : TRUNCATE t CASCADE  -- destructive, be sure
└── Resettable test fixture :
       TRUNCATE t RESTART IDENTITY CASCADE

Patterns :

Pattern 1 : Modern CREATE TABLE skeleton

ALWAYS use GENERATED ALWAYS AS IDENTITY for surrogate primary keys. NEVER ship serial / bigserial in new code.

CREATE TABLE orders (
    order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(customer_id),
    order_no   text   NOT NULL,
    price      numeric(12,2) NOT NULL CHECK (price >= 0),
    quantity   integer       NOT NULL CHECK (quantity > 0),
    total      numeric(14,2) GENERATED ALWAYS AS (price * quantity) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata   jsonb       NOT NULL DEFAULT '{}'::jsonb,
    UNIQUE (customer_id, order_no)
);

WHY : GENERATED ALWAYS AS IDENTITY produces a sequence that is fully owned by the column ; the application cannot accidentally overwrite the id (it must use OVERRIDING SYSTEM VALUE , a deliberate, auditable keyword). The total column is a STORED generated column , write-time materialized, automatically maintained on UPDATE, indexable. DEFAULT now() and DEFAULT '{}'::jsonb are stored once in pg_attribute on v11+ , adding the column to an existing table no longer rewrites the heap. Source : postgresql.org/docs/17/sql-createtable.html.

Pattern 2 : INSERT with RETURNING to capture the generated id

ALWAYS use RETURNING when the caller needs the server-allocated id or any computed column. NEVER follow INSERT with a separate SELECT currval(...) or SELECT last_inserted_id , RETURNING gives you the value atomically in the same round trip.

-- Single-row, return generated id and timestamp :
INSERT INTO orders (customer_id, order_no, price, quantity)
VALUES (42, 'ORD-2026-0001', 19.99, 3)
RETURNING order_id, created_at, total;

-- Multi-row, return all inserted rows :
INSERT INTO orders (customer_id, order_no, price, quantity)
VALUES (42, 'ORD-2026-0002', 9.99, 1),
       (43, 'ORD-2026-0003', 4.50, 5)
RETURNING *;

-- INSERT ... SELECT, return only the new ids :
INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2025-01-01'
RETURNING order_id;

-- Move rows atomically (data-modifying CTE) :
WITH moved AS (
  DELETE FROM orders WHERE created_at < '2025-01-01' RETURNING *
)
INSERT INTO archive_orders SELECT * FROM moved;

WHY : RETURNING runs against the actually-affected rows after defaults, GENERATED columns, and BEFORE triggers have fired. The data-modifying CTE pattern is the only safe way to move rows between tables without a window where the row is in neither table. Source : postgresql.org/docs/17/sql-insert.html, postgresql.org/docs/17/queries-with.html.

Pattern 3 : UPDATE with FROM-join, multi-column SET, and RETURNING

ALWAYS prefer UPDATE ... FROM to a correlated subquery when joining the target with another table. NEVER write UPDATE t SET col = (SELECT ... WHERE t.id = ...) if the subquery can match multiple rows , it silently picks an arbitrary row.

-- FROM-join, multi-column SET, RETURNING affected rows :
UPDATE orders AS o
   SET (price, quantity) = (n.new_price, n.new_quantity),
       updated_at = now()
  FROM new_prices AS n
 WHERE o.order_id = n.order_id
   AND o.shipped_at IS NULL
RETURNING o.order_id, o.total;       -- total is re-computed on UPDATE

-- Reset to DEFAULT explicitly :
UPDATE orders SET metadata = DEFAULT WHERE order_id = 1;

-- Subquery form of multi-column SET :
UPDATE orders AS o
   SET (price, quantity) = (SELECT new_price, new_quantity
                             FROM new_prices n WHERE n.order_id = o.order_id)
 WHERE EXISTS (SELECT 1 FROM new_prices n WHERE n.order_id = o.order_id);

WHY : UPDATE ... FROM is a real join ; the planner can use indexes on both sides and reject ambiguous matches. SET (a,b) = (x,y) keeps the column list atomic and avoids drift between two SET clauses when one is later edited. total is a STORED generated column so the UPDATE automatically recomputes and rewrites it. Source : postgresql.org/docs/17/sql-update.html.

Pattern 4 : DELETE with USING-join and RETURNING

ALWAYS use the USING clause to filter a DELETE against another table. NEVER use a correlated subquery if a join works , the planner produces better plans for USING.

-- Delete rows in `orders` whose customer was flagged for cleanup :
DELETE FROM orders AS o
 USING customers AS c
 WHERE c.customer_id = o.customer_id
   AND c.status = 'purged'
RETURNING o.order_id, o.customer_id;

-- Bounded delete using a CTE (avoid one statement scanning millions of rows) :
WITH batch AS (
  SELECT order_id FROM orders
   WHERE created_at < '2024-01-01'
   ORDER BY order_id
   LIMIT 10000
   FOR UPDATE SKIP LOCKED
)
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM batch)
RETURNING order_id;

WHY : DELETE acquires RowExclusiveLock on the target and a stricter lock per row deleted ; an unbounded DELETE FROM orders WHERE created_at < ... on a billion-row table is a foot-gun. The CTE form caps each statement at 10000 rows so vacuum, replication, and replicas catch up between batches. FOR UPDATE SKIP LOCKED keeps the batches non-overlapping when multiple workers run the same statement. Source : postgresql.org/docs/17/sql-delete.html.

Pattern 5 : TRUNCATE for tests, fixtures, and full reloads

ALWAYS prefer TRUNCATE ... RESTART IDENTITY over DELETE FROM ... when you need to empty a table completely. NEVER omit RESTART IDENTITY on a test database or you will see brittle "id = 1" tests fail when the sequence carries over.

-- Fast empty + reset identity sequences :
TRUNCATE orders, order_items RESTART IDENTITY;

-- With FK cascade (destructive , prefer listing all tables instead) :
TRUNCATE customers RESTART IDENTITY CASCADE;

-- Partitioned table : truncate every partition AND the parent attaches :
TRUNCATE events;            -- includes all partitions
TRUNCATE ONLY events;       -- only the parent (typically a no-op , parent has no rows)

WHY : TRUNCATE is O(1) per table (it removes the underlying files) and reclaims disk space immediately, whereas DELETE writes a dead tuple per row and must be cleaned up by VACUUM. TRUNCATE takes ACCESS EXCLUSIVE (blocks readers) so it is unsuitable for live production ; for production purges use the bounded-DELETE pattern in Pattern 4. Source : postgresql.org/docs/17/sql-truncate.html.

Pattern 6 : ALTER TABLE adding a column, constraint, and NOT NULL

ALWAYS combine related ALTER TABLE subcommands into one statement so the strictest required lock is taken once. NEVER add a NOT NULL constraint on a large table without first proving non-null via CHECK (col IS NOT NULL) NOT VALID then VALIDATE CONSTRAINT (deep dive : postgres-impl-zero-downtime-migrations).

-- Multi-action ALTER TABLE (one ACCESS EXCLUSIVE acquisition) :
ALTER TABLE orders
    ADD COLUMN shipped_at timestamptz,
    ADD COLUMN tracking_no text,
    ADD CONSTRAINT orders_tracking_no_unique UNIQUE (tracking_no),
    ALTER COLUMN customer_id SET STATISTICS 500;

-- Rename column, table, or constraint :
ALTER TABLE orders RENAME COLUMN order_no TO order_number;
ALTER TABLE orders RENAME TO sales_orders;
ALTER TABLE sales_orders RENAME CONSTRAINT orders_pkey TO sales_orders_pkey;

-- Drop column (metadata-only on v11+ , space reclaimed by VACUUM) :
ALTER TABLE sales_orders DROP COLUMN tracking_no;

WHY : multiple subcommands in one ALTER TABLE share the lock acquisition. Renaming changes only metadata , constant time. DROP COLUMN is metadata only ; the column is hidden and autovacuum eventually frees the space. For lock-minimization rules (which subcommands rewrite the table, which only scan, which are metadata-only), see postgres-impl-zero-downtime-migrations. Source : postgresql.org/docs/17/sql-altertable.html.

Pattern 7 : Transactional DDL inside BEGIN ... COMMIT

ALWAYS wrap a multi-statement schema change in BEGIN ... COMMIT so partial failures roll back atomically. NEVER expect CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, DETACH PARTITION ... CONCURRENTLY, ALTER TYPE ... ADD VALUE, ALTER SYSTEM, VACUUM, or CREATE DATABASE to work inside a transaction , they error out with 25001 (active SQL transaction) or 0A000 (feature not supported in transaction block).

BEGIN;
  ALTER TABLE orders ADD COLUMN region text;
  ALTER TABLE orders ADD CONSTRAINT orders_region_chk
      CHECK (region IN ('eu','us','ap')) NOT VALID;
  UPDATE orders SET region = 'eu' WHERE region IS NULL;
  ALTER TABLE orders VALIDATE CONSTRAINT orders_region_chk;
COMMIT;

-- This MUST be outside any transaction :
CREATE INDEX CONCURRENTLY orders_region_idx ON orders (region);

WHY : transactional DDL is one of PostgreSQL's strongest operational guarantees. If the UPDATE inside the BEGIN fails, the ADD COLUMN, ADD CONSTRAINT, and any other DDL statements roll back , the schema is unchanged from the outside. CREATE INDEX CONCURRENTLY is the most-used exception ; it does two scans and commits each phase separately, so it cannot live in a transaction. Failed CONCURRENTLY builds leave an INVALID index , clean up with DROP INDEX. Source : postgresql.org/docs/17/sql-begin.html, postgresql.org/docs/17/sql-createindex.html.

Anti-Patterns :

(One-liners ; full diagnosis + fix in references/anti-patterns.md.)

• Using serial / bigserial / smallserial in new code : deprecated since v10, breaks GENERATED ALWAYS guarantees, separate sequence-grant problem. Use GENERATED AS IDENTITY.
• Forgetting RETURNING and running SELECT currval('seq') instead : race-prone across connection pools, and unnecessary , RETURNING is atomic.
• INSERT ... ON CONFLICT (col) DO UPDATE SET col = some_value instead of SET col = EXCLUDED.col : breaks multi-row INSERT because some_value is constant.
• Adding DEFAULT now() on a populated huge table without checking that the default is non-volatile : v11+ stores non-volatile defaults in pg_attribute ; now() is stable so v11+ also handles it cheaply, but custom volatile functions still rewrite the table.
• Treating GENERATED ALWAYS AS (expr) STORED like a virtual column : the value is written to disk on every UPDATE that changes any referenced column.
• Running DELETE FROM huge_table to empty a billion-row table : bloats the heap, blocks autovacuum. Use TRUNCATE or bounded-DELETE batches.
• TRUNCATE on a live production table without considering ACCESS EXCLUSIVE lock : blocks every reader.
• Building a multi-statement migration without BEGIN ... COMMIT : partial failure leaves the schema in a half-applied state.
• CREATE INDEX CONCURRENTLY inside BEGIN : SQLSTATE 25001 , the build aborts immediately.
• Forgetting that OVERRIDING SYSTEM VALUE is required to insert a client-supplied id into a GENERATED ALWAYS AS IDENTITY column : SQLSTATE 428C9 , column cannot be assigned.

Reference Links :

• references/methods.md : Full CREATE TABLE / INSERT / UPDATE / DELETE / TRUNCATE / ALTER TABLE grammar tables, IDENTITY sequence options, transactional-DDL exception list
• references/examples.md : Verified version-annotated examples for each pattern (IDENTITY, GENERATED STORED, RETURNING, FROM-join UPDATE, USING-join DELETE, bounded-batch DELETE, multi-action ALTER TABLE)
• references/anti-patterns.md : Anti-patterns with cause, symptom, SQLSTATE, fix pattern

See Also :

• postgres-syntax-merge-upsert : INSERT ... ON CONFLICT, MERGE (v15+) with WHEN NOT MATCHED BY SOURCE (v17+) and merge_action()
• postgres-impl-zero-downtime-migrations : ALTER TABLE rewrite vs scan vs metadata-only matrix, NOT VALID + VALIDATE, CREATE INDEX CONCURRENTLY playbook
• postgres-impl-bulk-loading : COPY semantics, INSERT ... SELECT batching
• postgres-core-architecture : MVCC implications of UPDATE (new tuple, hint bits) and DELETE (dead tuples, VACUUM)
• postgres-core-version-matrix : v15 MERGE, v17 MERGE RETURNING + WHEN NOT MATCHED BY SOURCE, v17 RETURNING extended
• Vooronderzoek section : §2 (Full SQL Surface), §16 (Zero-Downtime Migrations summary)
• Official docs : https://www.postgresql.org/docs/17/sql-createtable.html, https://www.postgresql.org/docs/17/sql-altertable.html, https://www.postgresql.org/docs/17/sql-insert.html, https://www.postgresql.org/docs/17/sql-update.html, https://www.postgresql.org/docs/17/sql-delete.html, https://www.postgresql.org/docs/17/sql-truncate.html