PostgreSQL Cheatsheet

Tables and DDL

Use this PostgreSQL reference while you build software engineering projects, review code, or refresh the syntax you reach for most.

CREATE TABLE

One statement with the clauses you actually reach for. IF NOT EXISTS makes it idempotent, which matters in migration scripts that may be replayed.

CREATE TABLE IF NOT EXISTS orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  status      text   NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending','paid','shipped','cancelled')),
  subtotal    numeric(12,2) NOT NULL CHECK (subtotal >= 0),
  tax         numeric(12,2) NOT NULL DEFAULT 0,
  total       numeric(12,2) GENERATED ALWAYS AS (subtotal + tax) STORED,
  metadata    jsonb  NOT NULL DEFAULT '{}'::jsonb,
  tags        text[] NOT NULL DEFAULT '{}',
  placed_at   timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz,
  UNIQUE (customer_id, placed_at)
);

Clause	Effect
`GENERATED ALWAYS AS IDENTITY`	Auto key, 10+, replaces `serial`
`GENERATED ALWAYS AS (expr) STORED`	Computed column, 12+, written on insert and update
`DEFAULT expr`	Applied only when the column is omitted, not when you send NULL
`NOT NULL`	Cheap, always add it to columns that are not optional
`REFERENCES t (c)`	Inline foreign key, see Constraints
`CHECK (...)`	Row-level predicate, may not reference other tables
`COLLATE "C"`	Byte-order comparison, faster and locale-independent
`TEMPORARY`	Session-scoped, dropped at disconnect
`UNLOGGED`	Skips WAL, fast, truncated after a crash and not replicated

A generated column must be STORED on 12 through 17. VIRTUAL generated columns arrived in 18.

Deriving tables

LIKE copies the column definitions, AS SELECT copies the data and infers the columns. AS SELECT copies no constraints, no indexes, and no defaults.

-- Structure only, with the extras you name
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
CREATE TABLE orders_shell   (LIKE orders INCLUDING DEFAULTS EXCLUDING INDEXES);

-- Data, structure inferred
CREATE TABLE orders_2023 AS
SELECT * FROM orders WHERE placed_at < '2024-01-01';

-- Empty copy of a query's shape
CREATE TABLE report_shape AS SELECT * FROM orders WITH NO DATA;

CREATE TEMP TABLE ids (id bigint PRIMARY KEY);
CREATE UNLOGGED TABLE import_staging (raw jsonb);

INCLUDING ALL covers defaults, constraints, indexes, identity, generated columns, comments, statistics, and storage settings.

ALTER TABLE

Adding a nullable column, or a column with a constant default, is a catalog-only operation since 11 and does not rewrite the table. Changing a type generally does rewrite it and takes an ACCESS EXCLUSIVE lock.

ALTER TABLE orders ADD COLUMN notes text;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS currency text NOT NULL DEFAULT 'USD';
ALTER TABLE orders DROP COLUMN IF EXISTS notes;
ALTER TABLE orders RENAME COLUMN notes TO internal_notes;
ALTER TABLE orders RENAME TO sales_orders;

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'draft';
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN updated_at SET NOT NULL;
ALTER TABLE orders ALTER COLUMN updated_at DROP NOT NULL;

-- Type change with an explicit conversion
ALTER TABLE orders
  ALTER COLUMN legacy_total TYPE numeric(12,2)
  USING nullif(btrim(legacy_total), '')::numeric;

ALTER TABLE orders ALTER COLUMN metadata SET STORAGE EXTERNAL;
ALTER TABLE orders SET (fillfactor = 90);
ALTER TABLE orders OWNER TO app_owner;
ALTER TABLE orders SET SCHEMA archive;

Operation	Rewrites the table?	Lock
`ADD COLUMN` nullable	No	`ACCESS EXCLUSIVE`, brief
`ADD COLUMN ... DEFAULT <constant>`	No, 11+	`ACCESS EXCLUSIVE`, brief
`ADD COLUMN ... DEFAULT <volatile fn>`	Yes	`ACCESS EXCLUSIVE`, long
`DROP COLUMN`	No, marked dead	`ACCESS EXCLUSIVE`, brief
`ALTER COLUMN TYPE`	Usually yes	`ACCESS EXCLUSIVE`, long
`varchar(50)` → `varchar(100)` or `text`	No	Brief
`SET NOT NULL`	No, but full scan to verify	`ACCESS EXCLUSIVE` for the scan
`RENAME`	No	Brief

On a busy table, set lock_timeout before any ALTER TABLE so a blocked statement fails fast instead of queueing every reader behind it.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

Schemas and search_path

A schema is a namespace inside one database. search_path decides which schema an unqualified name resolves to, and which schema a CREATE lands in.

CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA reporting AUTHORIZATION report_owner;

SHOW search_path;                      -- "$user", public
SET search_path TO analytics, public;  -- session only
ALTER ROLE app SET search_path TO app, public;         -- per role
ALTER DATABASE appdb SET search_path TO app, public;   -- per database

SELECT current_schema();
DROP SCHEMA analytics CASCADE;         -- drops everything inside, no undo

The first writable entry in search_path receives new objects. Always schema-qualify inside SECURITY DEFINER functions and inside migrations, and set search_path explicitly rather than trusting the default.

Dropping and emptying

DROP refuses when another object depends on the target. CASCADE drops the dependents too, which is exactly as dangerous as it sounds.

DROP TABLE IF EXISTS orders;
DROP TABLE orders CASCADE;             -- also drops dependent views and FKs
DROP VIEW IF EXISTS v_recent;
DROP SCHEMA staging CASCADE;

TRUNCATE orders;                                   -- fast, needs ACCESS EXCLUSIVE
TRUNCATE orders RESTART IDENTITY;                  -- also resets the sequence
TRUNCATE orders, order_items RESTART IDENTITY CASCADE;

Statement	Speed	Reclaims disk	Fires row triggers	Transactional
`DELETE FROM t`	Slow, row by row	No, needs `VACUUM`	Yes	Yes
`TRUNCATE t`	Instant	Yes	No (only `TRUNCATE` triggers)	Yes, rollback works
`DROP TABLE t`	Instant	Yes	No	Yes

TRUNCATE is transactional in Postgres, unlike some other engines. It still needs ACCESS EXCLUSIVE, so it blocks every reader, and CASCADE silently truncates every table with a foreign key pointing at yours.

Views and materialized views

A view is a stored query with no storage. A materialized view stores the result and goes stale until refreshed.

CREATE OR REPLACE VIEW v_paid_orders AS
SELECT o.id, o.customer_id, o.total, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT date_trunc('day', placed_at) AS day, sum(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY 1
WITH DATA;

CREATE UNIQUE INDEX ON mv_daily_revenue (day);   -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW mv_daily_revenue;                   -- locks readers out
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;      -- 9.4+, readers stay

REFRESH ... CONCURRENTLY requires at least one unique index on the view and cannot run on a view that has never been populated. CREATE OR REPLACE VIEW may add trailing columns but cannot rename, reorder, retype, or drop existing ones, so a real change means DROP VIEW plus recreate, which cascades into anything built on it.

Updatable views: a simple single-table view is updatable automatically. Anything with a join, an aggregate, or a DISTINCT needs an INSTEAD OF trigger. Add WITH CHECK OPTION to reject writes that would fall outside the view predicate.

Partitioning and comments

Declarative partitioning (10+) routes rows to child tables by a key. Range for time series, list for tenant or region, hash for even spread.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  region text NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, occurred_at)          -- key must include the partition column
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;   -- 11+

CREATE TABLE tenants (id bigint, region text NOT NULL, name text)
  PARTITION BY LIST (region);
CREATE TABLE tenants_eu PARTITION OF tenants FOR VALUES IN ('eu-west','eu-central');

-- Detach and drop is the point: instant data expiry
ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;   -- 14+
DROP TABLE events_2025_01;

Range bounds are inclusive at the lower end and exclusive at the upper, so adjacent partitions share a boundary date without overlapping. Every unique constraint and primary key must contain the partition key. DETACH ... CONCURRENTLY refuses to run while a DEFAULT partition exists, and it cannot run inside a transaction block. Attaching an existing table takes a full scan unless a matching CHECK constraint already proves the bound.

COMMENT ON TABLE orders IS 'One row per customer order, immutable after shipping.';
COMMENT ON COLUMN orders.total IS 'subtotal + tax, generated.';
COMMENT ON INDEX orders_customer_id_idx IS NULL;   -- removes the comment

Comments show up in \d+ and \dt+, which makes them the cheapest documentation in the system.