prefer-pg18-not-null-not-valid

What triggers it

The AT_AddConstraint subcommand of ALTER TABLE when the added constraint is a CONSTR_CHECK with skip_validation set (i.e. NOT VALID) whose expression structurally matches an IS NOT NULL test on one column — only checked once ctx.pgVersion is 18 or higher.

What does not

Any migration targeting PostgreSQL below 18 returns before the statement is even parsed for a constraint. ALTER TABLE statements with no commands, CHECK constraints that aren't NOT VALID, and CHECK expressions that don't structurally match a NOT NULL test are all skipped.

Where it applies

Applies to PostgreSQL 18 and later. It works on the SQL text alone — no database connection needed.

On PG17 and earlier the CHECK-based workaround is still the only safe way to add NOT NULL to a populated table; this rule only fires once the target is PG18+, where it's no longer necessary.

The lock, and what it blocks

None — both the old workaround and the PG18 native form use NOT VALID plus VALIDATE CONSTRAINT, so the locking is the same either way. This rule is about statement count, not lock behavior.

Why it matters

PostgreSQL 18 stores NOT NULL constraints in pg_constraint, so ALTER TABLE ... ADD CONSTRAINT ... NOT NULL col NOT VALID marks a column NOT NULL without scanning the table, and VALIDATE CONSTRAINT checks the existing rows under a lock that allows reads and writes. The old workaround of adding a CHECK (col IS NOT NULL) NOT VALID constraint is no longer needed.

Unsafe, and safe

-- PG18+: old workaround, no longer needed
ALTER TABLE users ADD CONSTRAINT users_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;

-- PG18+ native approach (simpler):
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  NOT NULL email NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

Deploy and transaction boundaries

The ADD CONSTRAINT ... NOT NULL ... NOT VALID step and the VALIDATE CONSTRAINT step are two separate statements — plan-fix documents the lock and deploy boundary between them so validation doesn't happen in the same transaction as the addition.

What it assumes

Assumes ctx.pgVersion reflects the real target server — on a fleet running mixed major versions, the rule can suggest simplifying a workaround that's still required on servers not yet upgraded to 18. The structural NOT-NULL match also only recognizes one expression shape, so a differently-written but equivalent CHECK could be missed.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

⚠ [MP081] WARNING (line 2)
  CHECK (email IS NOT NULL) NOT VALID on "users" can be simplified on PG18+. Use a native NOT NULL constraint added NOT VALID instead.

  Safe alternative:
  -- PG18+ native approach (simpler, same safety):
  ALTER TABLE users ADD CONSTRAINT users_email_not_null
    NOT NULL email NOT VALID;
  ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

  Why: PostgreSQL 18 stores NOT NULL constraints in pg_constraint, so ALTER TABLE ... ADD CONSTRAINT ... NOT NULL col NOT VALID marks a column NOT NULL without scanning the table (instant), and VALIDATE CONSTRAINT checks the existing rows under a lock that allows reads and writes. The old workaround of adding a CHECK (col IS NOT NULL) NOT VALID constraint is no longer needed and adds unnecessary complexity.
  Docs: https://migrationpilot.dev/rules/mp081

Generated 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 MP081
ALTER TABLE users ADD CONSTRAINT users_email_nn

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP081: false

# or keep it, and downgrade it
rules:
  MP081:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.