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