Adding NOT NULL Constraints to Existing PostgreSQL Columns Safely

The Problem with SET NOT NULL

When you run ALTER TABLE ... ALTER COLUMN ... SET NOT NULL, PostgreSQL must verify that no existing rows have NULL values in that column. To do this, it scans the entire table while holding an ACCESS EXCLUSIVE lock.

-- DANGEROUS: Full table scan under ACCESS EXCLUSIVE
-- On a 50M row table, this blocks ALL queries for minutes
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- What happens internally:
-- 1. Acquires ACCESS EXCLUSIVE lock (blocks everything)
-- 2. Scans every row to verify email IS NOT NULL
-- 3. If any NULL found: fails with error
-- 4. If all rows pass: sets the constraint
-- 5. Releases lock

-- During step 2, no query can read or write the table

ACCESS EXCLUSIVE means nothing else can happen on this table. No SELECT, no INSERT, no UPDATE, no DELETE. And it queues — any query that tries to access the table while the scan is running gets queued, filling up your connection pool.

The Safe Pattern: CHECK + VALIDATE

The safe approach uses a CHECK constraint with NOT VALID to split the operation into two steps with different lock levels:

-- Step 1: Add the constraint as NOT VALID
-- Acquires ACCESS EXCLUSIVE but is INSTANT (no table scan)
-- Only new inserts/updates are checked going forward
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint
-- Acquires SHARE UPDATE EXCLUSIVE (does NOT block reads or writes)
-- Scans the entire table to verify existing rows
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

The key differences from the direct SET NOT NULL approach:

PostgreSQL 12+: Converting CHECK to NOT NULL

Starting with PostgreSQL 12, if a validated CHECK constraint of the form CHECK (column IS NOT NULL) exists, PostgreSQL recognizes that the column already has a not-null guarantee. In this case, SET NOT NULL skips the table scan and completes instantly:

-- Full safe pattern for PG 12+:

-- Step 1: Add NOT VALID CHECK constraint (instant)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate (scans table, but under safe lock)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- Step 3: Convert to real NOT NULL (instant — no scan needed!)
-- PG 12+ skips the scan because it sees the validated CHECK
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the now-redundant CHECK constraint
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;

The result is identical to running SET NOT NULL directly, but without the dangerous full-table scan under ACCESS EXCLUSIVE. The total lock time is a few milliseconds in Steps 1, 3, and 4. Step 2 scans the table but does not block any queries.

PostgreSQL 11 and Earlier

On PostgreSQL 11 and earlier, the SET NOT NULL optimization does not exist. After validating the CHECK constraint, you have two options:

Option A: Keep the CHECK constraint (recommended)

-- Just use the CHECK constraint as your not-null guarantee
-- This provides the same data integrity as SET NOT NULL
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- Done. The CHECK constraint enforces not-null for all
-- existing and future rows. No need for SET NOT NULL.

The CHECK constraint provides the same guarantee as SET NOT NULL. The only difference is cosmetic: \d users will not show "not null" next to the column, but the constraint is visible in the constraints section. Some ORMs may not recognize the CHECK constraint as a not-null guarantee, but the data integrity is identical.

Option B: Accept the brief lock (small tables only)

-- If the table is small enough (< 100K rows), the scan
-- under ACCESS EXCLUSIVE is fast enough to be acceptable
-- Use lock_timeout to prevent the lock queue problem
SET lock_timeout = '5s';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
RESET lock_timeout;

Handling Existing NULL Values

Before adding a NOT NULL constraint, you need to ensure no NULL values exist. The VALIDATE step will fail if it finds any. Backfill NULLs first:

-- Step 0: Backfill NULL values (before adding the constraint)
-- Do this in batches to avoid long-running transactions

-- Check how many NULLs exist
SELECT count(*) FROM users WHERE email IS NULL;

-- Backfill in batches
UPDATE users
SET email = 'unknown@example.com'
WHERE id IN (
  SELECT id FROM users
  WHERE email IS NULL
  ORDER BY id
  LIMIT 10000
);
-- Repeat until no NULLs remain

-- Then proceed with the CHECK + VALIDATE pattern
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

Common Mistakes

1. Forgetting NOT VALID

-- BAD: Without NOT VALID, ADD CONSTRAINT scans the table
-- under SHARE ROW EXCLUSIVE (blocks writes)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL);

-- GOOD: NOT VALID makes it instant
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

2. Running SET NOT NULL on PG 11 after CHECK validation

-- BAD (on PG 11): SET NOT NULL still scans the table
-- even though the CHECK constraint is validated
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- This does a full scan under ACCESS EXCLUSIVE!

-- On PG 11: Just keep the CHECK constraint
-- On PG 12+: SET NOT NULL is instant (skips scan)

3. Not setting lock_timeout

-- BAD: Both steps should have lock_timeout
-- Even though the locks are brief, they can queue

-- GOOD: Set lock_timeout for both operations
SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
RESET lock_timeout;

-- VALIDATE doesn't need lock_timeout (it uses a safe lock)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

The Complete Pattern

Here is the full, production-safe pattern for adding NOT NULL to an existing column:

-- Migration file: 005_add_email_not_null.sql

-- 1. Backfill any existing NULLs (if they exist)
-- Run this in a separate migration or script before this one
-- UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- 2. Add NOT VALID CHECK constraint (instant, brief ACCESS EXCLUSIVE)
SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
RESET lock_timeout;

-- 3. Validate (full scan, but SHARE UPDATE EXCLUSIVE — safe)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- 4. Convert to formal NOT NULL (PG 12+ only — instant)
SET lock_timeout = '5s';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
RESET lock_timeout;

-- 5. Drop redundant CHECK (PG 12+ only)
SET lock_timeout = '5s';
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;
RESET lock_timeout;

Automate This Check

Catching a direct SET NOT NULL before it reaches production is exactly the kind of thing that should be automated. MigrationPilot flags SET NOT NULL without the CHECK pattern (rule MP002) and direct ADD CONSTRAINT without NOT VALID (rule MP030). It is version-aware, so on PG 12+ it knows that SET NOT NULL after a validated CHECK is safe. Add it to your CI pipeline to catch these patterns automatically.

npx migrationpilot analyze migrations/005_add_email_not_null.sql