Why does ADD COLUMN NOT NULL fail on a table with rows?

ADD COLUMN NOT NULL without default

Critical: this fails outright or takes production down.

What happens

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.

Why it is dangerous on a populated table

On an empty table this finishes before anyone notices. On a populated one the lock is held for the whole operation, every query on the table queues behind it, and the connection pool fills from the back.

Measured

On 20M rows, 1.7 GB the statement is refused after 4 ms; the cost here is a failed deploy, not a lock. The safe form ran in 4 ms with no probe waiting more than 2 ms. The server said: column "note" of relation "orders" contains null values. The whole benchmark has the run this comes from.

Fires on

ALTER TABLE orders ADD COLUMN region text NOT NULL;

The safe pattern

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

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

not null no default

ALTER TABLE orders ADD COLUMN region text NOT NULL;

Stays silent (3)

identity column

ALTER TABLE orders ADD COLUMN seq bigint GENERATED ALWAYS AS IDENTITY NOT NULL;

not null with default

ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'eu';

nullable column

ALTER TABLE orders ADD COLUMN region text;

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BV001 BV003 →