ERROR database migration_error ai_generated true

ERROR: column "X" of relation "Y" contains null values

ID: database/migration-not-null-no-default

Also available as: JSON · Markdown
93%Fix Rate
91%Confidence
52Evidence
2023-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
16 active — — —

Root Cause

PostgreSQL rejects adding a NOT NULL constraint to a column that contains null values, or adding a new NOT NULL column without a default value to a table with existing rows. The fix requires either backfilling existing data, providing a default, or splitting the migration into multiple steps.

generic

Workarounds

  1. 95% success Add the column as nullable, backfill existing rows, then add the NOT NULL constraint 
    Split into three migration steps: 1) ALTER TABLE ADD COLUMN new_col type; (nullable). 2) UPDATE table SET new_col = <default_value> WHERE new_col IS NULL; 3) ALTER TABLE ALTER COLUMN new_col SET NOT NULL; This approach is safe for production because existing rows are never in a violating state. For large tables, batch the UPDATE to avoid long-running transactions.

    Sources: https://www.postgresql.org/docs/16/sql-altertable.html

  2. 92% success Use a DEFAULT value in the migration to populate existing rows automatically 
    ALTER TABLE ADD COLUMN new_col type NOT NULL DEFAULT <value>; PostgreSQL 11+ adds the column with the default value for all existing rows without rewriting the table (metadata-only operation). New rows also get the default. This is the fastest approach for adding a non-null column to a large table.

    Sources: https://www.postgresql.org/docs/16/sql-altertable.html

  3. 88% success Split the migration into multiple deployments for zero-downtime changes 
    Deploy in phases: Phase 1 — add nullable column and update application code to write non-null values. Phase 2 — run backfill job (UPDATE in batches of 1000-10000 rows). Phase 3 — add NOT NULL constraint. This prevents long table locks and allows rollback at each phase. Use a CHECK constraint (NOT VALID) first, then VALIDATE CONSTRAINT separately to avoid full table lock.

    Sources: https://www.postgresql.org/docs/16/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT

Dead Ends

Common approaches that don't work:

  1. Dropping the column and recreating it as NOT NULL 90% fail

    Dropping a column permanently destroys all data in that column across every row in the table. If the table has production data, this causes irreversible data loss. Even in development, this disrupts other migrations that depend on the column existing and breaks rollback sequences.

  2. Running ALTER TABLE ... SET NOT NULL on a column that still has null values 95% fail

    PostgreSQL performs a full table scan to verify no nulls exist before applying the NOT NULL constraint. If any row has a null value in that column, the entire ALTER TABLE statement fails and rolls back. On large tables, this scan can lock the table for extended periods before ultimately failing.

  3. Deleting all rows with null values to satisfy the NOT NULL constraint 85% fail

    Deleting rows to satisfy a schema constraint destroys production data. Rows with null values in one column may contain critical data in other columns. This approach also does not prevent future nulls from appearing between the DELETE and the ALTER TABLE, unless done within a transaction that locks the table.

Error Chain

Leads to: