PostgreSQL Cheatsheet

Constraints

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

The five kinds

Constraints are enforced by the server, so they hold no matter which application, ORM, or psql session writes the row. Application-level validation is a nicety, a constraint is a guarantee.

Constraint	Guarantees	Backed by an index
`PRIMARY KEY`	Unique and not null, one per table	Yes, a unique btree
`UNIQUE`	No duplicate non-null values	Yes, a unique btree
`FOREIGN KEY`	The referenced row exists	No, index the referencing side yourself
`CHECK`	A boolean expression over one row	No
`NOT NULL`	Column is never null	No
`EXCLUDE`	No pair of rows satisfies an operator	Yes, gist or btree

CREATE TABLE order_items (
  order_id   bigint NOT NULL,
  sku        text   NOT NULL,
  qty        integer NOT NULL CONSTRAINT qty_positive CHECK (qty > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, sku),
  FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

Name your constraints. An auto-generated name like order_items_qty_check is fine until you need to DROP CONSTRAINT it in a migration and the name differs between environments.

Primary keys and unique

A UNIQUE constraint treats NULLs as distinct by default, so a nullable unique column accepts unlimited NULL rows. NULLS NOT DISTINCT (15+) changes that.

ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY (id);
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);

-- Pre-15 behavior: many NULL slugs allowed
CREATE TABLE pages (id bigint, slug text UNIQUE);

-- 15+: at most one NULL slug
CREATE TABLE pages (id bigint, slug text UNIQUE NULLS NOT DISTINCT);
ALTER TABLE pages ADD CONSTRAINT pages_slug_key
  UNIQUE NULLS NOT DISTINCT (slug);

-- Attach a constraint to an index you already built concurrently
CREATE UNIQUE INDEX CONCURRENTLY pages_slug_idx ON pages (slug);
ALTER TABLE pages ADD CONSTRAINT pages_slug_key UNIQUE USING INDEX pages_slug_idx;

The USING INDEX form is the zero-downtime recipe: build the unique index concurrently, then promote it to a constraint in a fast catalog-only step.

A partial unique index does what a partial unique constraint cannot, because constraints take no WHERE:

-- Only one active subscription per customer
CREATE UNIQUE INDEX subs_one_active
  ON subscriptions (customer_id) WHERE status = 'active';

Foreign keys

ON DELETE and ON UPDATE decide what happens to the child row when the parent changes. The default is NO ACTION.

Action	On parent delete / update
`NO ACTION`	Error, but checked at end of statement so deferral can save it
`RESTRICT`	Error immediately, never deferred
`CASCADE`	Delete or update the child rows too
`SET NULL`	Set the referencing columns to NULL, needs them nullable
`SET DEFAULT`	Set them to their column default, which must itself reference a real row
`SET NULL (col)`	15+, only the listed columns of a composite key

ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders (id)
  ON DELETE CASCADE ON UPDATE RESTRICT;

-- Composite, plus a partial column reset (15+)
ALTER TABLE assignments
  ADD FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id)
  ON DELETE SET NULL (user_id);

Two things bite here:

- Postgres does not index the referencing column for you. Every parent DELETE scans the child table without one. Add CREATE INDEX ON order_items (order_id). - A foreign key takes a SHARE ROW EXCLUSIVE lock on the referenced table while it is created, and validates every existing row.

CHECK and NOT NULL

A CHECK expression sees one row and must be immutable. It cannot query another table, cannot use a subquery, and cannot call a volatile function like now(). A row is accepted when the expression is true or NULL, so CHECK (qty > 0) does not imply NOT NULL.

ALTER TABLE orders ADD CONSTRAINT total_nonneg CHECK (total >= 0);
ALTER TABLE orders ADD CONSTRAINT status_known
  CHECK (status IN ('pending','paid','shipped','cancelled'));
ALTER TABLE users ADD CONSTRAINT email_shape CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');
ALTER TABLE ranges ADD CONSTRAINT ends_after_start CHECK (ends_at > starts_at);

ALTER TABLE orders DROP CONSTRAINT total_nonneg;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

CHECK (col IS NOT NULL) and a real NOT NULL are not equivalent: only the latter is visible to the planner, and only the latter lets a column join a primary key.

Adding constraints to a live table

The default ADD CONSTRAINT validates every existing row while holding a strong lock. On a large table, add it NOT VALID first, which only enforces it for new and updated rows, then validate separately under a weaker lock.

-- Step 1, instant, blocks nothing meaningfully
ALTER TABLE orders
  ADD CONSTRAINT total_nonneg CHECK (total >= 0) NOT VALID;

-- Step 2, scans the table under SHARE UPDATE EXCLUSIVE, readers and writers continue
ALTER TABLE orders VALIDATE CONSTRAINT total_nonneg;

The same two-step works for foreign keys. It does not work for SET NOT NULL directly, so the pattern there is: add a NOT VALID check, validate it, then SET NOT NULL, which since 12 can use the validated check to skip its own scan.

ALTER TABLE orders ADD CONSTRAINT updated_at_nn
  CHECK (updated_at IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT updated_at_nn;
ALTER TABLE orders ALTER COLUMN updated_at SET NOT NULL;   -- fast on 12+
ALTER TABLE orders DROP CONSTRAINT updated_at_nn;

Find what is still unvalidated:

SELECT conrelid::regclass AS table, conname, contype
FROM pg_constraint WHERE NOT convalidated;

Deferrable and EXCLUDE

A DEFERRABLE INITIALLY DEFERRED constraint is checked at COMMIT instead of per statement, which is how you swap two unique values without a temporary placeholder. Only UNIQUE, PRIMARY KEY, EXCLUDE, and FOREIGN KEY can be deferrable. CHECK and NOT NULL cannot.

ALTER TABLE pages ADD CONSTRAINT pages_pos_key UNIQUE (position)
  DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
  SET CONSTRAINTS pages_pos_key DEFERRED;
  UPDATE pages SET position = 2 WHERE id = 1;
  UPDATE pages SET position = 1 WHERE id = 2;
COMMIT;   -- checked here, so the transient duplicate is fine

EXCLUDE generalizes UNIQUE to any operator. With && on a range it is the canonical no-double-booking constraint, and it needs btree_gist for the plain equality column.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id int NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- Ignore cancelled rows
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  WHERE (status <> 'cancelled');

Reading violation errors

Every constraint failure carries a SQLSTATE. Match on the code, never on the English message, and use constraint_name from the error fields to map it to a user-facing message.

Code	Name	Means
`23502`	`not_null_violation`	A NOT NULL column got NULL
`23503`	`foreign_key_violation`	Parent row missing, or child rows still reference it
`23505`	`unique_violation`	Duplicate key, the one you catch for upsert races
`23514`	`check_violation`	A CHECK expression was false
`23P01`	`exclusion_violation`	An EXCLUDE constraint matched, typically overlapping ranges
`23001`	`restrict_violation`	`ON DELETE RESTRICT` blocked it
`22P02`	`invalid_text_representation`	A cast failed, for example bad uuid or enum text
`40001`	`serialization_failure`	See Transactions and Locking

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.
SQLSTATE: 23505

Inspect what a table actually enforces:

\d+ orders

SELECT conname, contype, convalidated, condeferrable,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
ORDER BY contype, conname;

contype codes: p primary key, u unique, f foreign key, c check, x exclusion, n not null (18+, earlier releases record NOT NULL in pg_attribute.attnotnull).