ADD COLUMN NOT NULL without default

Critical — this fails outright or takes production down.

What it catches

Adding a NOT NULL column with no default fails on any table that already has rows — existing rows would violate the constraint. The migration errors out in production even though it works on an empty dev database.

Fires on

ALTER TABLE orders ADD COLUMN region text NOT NULL;

Do this instead

Add the column with a non-volatile DEFAULT — on Postgres 11+ that is a metadata-only change, instant regardless of table size, and existing rows satisfy NOT NULL via the stored default. If no single default makes sense, add the column nullable, backfill in batches, then SET NOT NULL through a validated CHECK constraint (see BV011).

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'eu';
-- metadata-only on PG11+ because the default is non-volatile

Catch this before it ships

This rule runs locally in the free CLI — or with the full corpus through the hosted service: npx bolvrk check migration.sql

Browse all 69 rules

← BV001 BV003 →