require-default-for-not-null-column
ADD COLUMN ... NOT NULL without a DEFAULT aborts the migration on any table that already contains rows.
- operation
- Columns
- lock taken
- no table lock
- remediation
- Manual rewrite
- category
- Constraints & keys
What triggers it
The AT_AddColumn subcommand of ALTER TABLE when the new column's constraints include CONSTR_NOTNULL but none of CONSTR_DEFAULT, CONSTR_IDENTITY, or CONSTR_GENERATED, and the column type isn't one of the SERIAL/BIGSERIAL/SMALLSERIAL pseudo-types.
What does not
Non-AlterTableStmt statements, ALTER TABLE with no commands, ADD COLUMN without NOT NULL, and any NOT NULL column that supplies its own value — DEFAULT, GENERATED ALWAYS AS IDENTITY, GENERATED ALWAYS AS (...) STORED, or a SERIAL-family type.
Where it applies
Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.
The lock, and what it blocks
None named — the danger here isn't a lock, it's that the statement aborts outright on any table with existing rows, rolling back the whole migration.
Why it matters
PostgreSQL has to write a value into the new column for every row that already exists, and without a DEFAULT there is nothing to write — the statement fails with "contains null values" and takes the whole migration with it. What makes this one worth catching in review is where it fails: an empty database accepts the identical statement, so it passes locally, passes in CI, and then aborts in staging or production, the only environments with rows in the table.
Unsafe, and safe
Flagged
ALTER TABLE users ADD COLUMN email TEXT NOT NULL; -- Fine on an empty table, fatal on a populated one
Safe alternative
-- On PG 11+ a constant default is a catalog-only change, no rewrite. ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT '';
What it assumes
Assumes the table has rows in the environment that matters. An empty table, or one seeded fresh in every environment including production, accepts the identical statement without complaint — the rule has no way to check row count without --database-url, so it flags the statement the same way regardless.
What the CLI prints
✗ [MP084] CRITICAL (line 1)
ADD COLUMN "email" NOT NULL on "users" has no DEFAULT. On a table that already has rows this aborts with: column "email" of relation "users" contains null values. It will pass on an empty CI database and fail in production.
Safe alternative:
-- Option A: give existing rows a value (PG 11+ does this without a rewrite):
ALTER TABLE users ADD COLUMN email <type> NOT NULL DEFAULT <value>;
-- Option B: if there is no sensible default, add it nullable and tighten later:
ALTER TABLE users ADD COLUMN email <type>;
-- backfill in batches, then:
ALTER TABLE users ADD CONSTRAINT users_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
Why: PostgreSQL has to give existing rows a value for the new column. Without a DEFAULT there is nothing to give them, so the statement fails with "column ... contains null values" and the whole migration rolls back. An empty CI database accepts the same statement happily, which is what makes this one dangerous: it passes every check you run before deploy and only fails in the environment that has data.
Docs: https://migrationpilot.dev/rules/mp084Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.
Turning it off
For one statement, put a comment on the line before it:
-- migrationpilot-disable MP084 ALTER TABLE users ADD COLUMN email TEXT NOT NULL;
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP084: false
# or keep it, and downgrade it
rules:
MP084:
severity: warningTry it
Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.
Run MP084 in the playground