# Postgres Syntax Dml Ddl

> 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

- Skill: `impertio-studio/postgres-syntax-dml-ddl` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add impertio-studio/postgres-syntax-dml-ddl`
- Raw SKILL.md: https://api.skillmd.com/api/skills/impertio-studio/postgres-syntax-dml-ddl/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: Impertio-Studio (https://skillmd.com/u/impertio-studio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/impertio-studio/postgres-syntax-dml-ddl

---


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

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

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

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

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

```sql
-- 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`).

```sql
-- 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).

```sql
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](references/methods.md) : Full CREATE TABLE / INSERT / UPDATE / DELETE / TRUNCATE / ALTER TABLE grammar tables, IDENTITY sequence options, transactional-DDL exception list
- [references/examples.md](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](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