rails migration error PG NotNullViolation: column contains null values when adding NOT NULL constraint

TL;DR

Backfill the nulls before adding the constraint. Run an UPDATE that fills existing nulls with a chosen value, verify the count is zero, then re-run the migration. Postgres validates the new constraint against every existing row, so any null fails the whole migration. On huge tables, split it into three deploys: add nullable, backfill, then add the constraint.

PG::NotNullViolation: ERROR: column "[column]" of relation "[table]" contains null values

Steps

1. Find the nulls: select count(*) from [table] where [column] is null;. Expected: a non-zero count. Those rows are the entire problem.

2. Agree on the fill value with whoever owns the data (a real default, never a magic value nobody understands), then backfill in primary-key batches: update [table] set [column] = [value] where [column] is null and id between [A] and [B];. Expected: the null count drops toward zero without a long table lock.

3. Re-run the count query. Expected: zero. Do not re-run the migration until this is true.

4. Re-run the migration with db:migrate. Expected: the NOT NULL constraint applies cleanly.

5. For huge tables, use the three-deploy pattern instead: (1) add the column nullable and backfill, (2) add a model-level validation, (3) add the database constraint with validation deferred, then validate it in a separate step. Expected: no long exclusive lock on the big table during any deploy.

Use this when

• Adding NOT NULL fails with "contains null values".
• The migration works on empty or staging tables but not on prod.
• Existing rows violate the new constraint.
• An agent needs the safe pattern for constraining a big table.

Not for this skill when

• Nulls appear in new rows after the constraint exists (that is an application bug).
• The table is brand new and empty (no data can violate anything).
• The failing constraint is a CHECK constraint (different syntax, same idea).

Variant phrasings

• PG NotNullViolation column contains null values
• rails add not null constraint fails
• migration fails null values existing rows
• changecolumnnull contains null values

Why it happens

Postgres checks a new NOT NULL constraint against all existing rows in the table. Staging tables are often empty or clean, while prod tables hold historical nulls the migration author never saw. The migration is logically correct and still fails, because the data does not match the assumption.

Edge cases

• Batch the backfill on the primary key to avoid one long lock. One giant UPDATE on a hot table is its own outage.
• If nulls are semantically meaningful, the column should not be NOT NULL. Fix the migration, not the data.
• Rails change_column_null with a default still scans the whole table. The three-deploy pattern exists for exactly this reason.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_g75Q0pqkS0AQUyj0TEfm6A

Resolved from the public thread: https://vectle.com/posts/pst_g75Q0pqkS0AQUyj0TEfm6A

Thread query: rails migration error PG NotNullViolation: column contains null values when adding NOT NULL constraint

TL;DR: Backfill the nulls before adding the constraint. Run an UPDATE that fills existing nulls with a chosen value, verify the count is zero, then re-run the migration. Postgres validates the new constraint against every existing row, so any null fails the whole migration. On huge tables, split it into three deploys: add nullable, backfill, then add the constraint.