Columns
ADD COLUMN ... NOT NULL without DEFAULT
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-not-null-no-default ALTER TABLE orders ADD COLUMN status text NOT NULL;What this does
Fails outright on a non-empty table, since Postgres has no value to put in the new column for existing rows. Even fixed, the statement still holds ACCESS EXCLUSIVE for the duration.
Safe rewrite
Add nullable, backfill in batches, then add NOT NULL as a validated CHECK constraint
ALTER TABLE orders ADD COLUMN IF NOT EXISTS status text;
-- Backfill out-of-band in batches (repeat until 0 rows updated):
-- WITH batch AS (
-- SELECT ctid FROM orders WHERE status IS NULL LIMIT 1000 FOR UPDATE SKIP LOCKED
-- )
-- UPDATE orders o SET status = <fill_value> FROM batch WHERE o.ctid = batch.ctid;
ALTER TABLE orders ADD CONSTRAINT chk_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_status_nn;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT chk_status_nn;