prefer-pg18-not-null-not-valid

What It Detects

On PG18+, use a native NOT NULL constraint added NOT VALID instead of the CHECK constraint workaround.

Why It's Dangerous

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.

Bad Example

-- 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;

Good Example

-- 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;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP081: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP081:
    severity: warning