PostgreSQL 18 Changed NOT NULL — Update Your Migration Rules

The Old NOT NULL Workaround (PG 12–17)

Before PostgreSQL 18, adding a NOT NULL constraint to an existing column was dangerous. The naive approach — ALTER TABLE ... SET NOT NULL — scans the entire table under an ACCESS EXCLUSIVE lock. On a table with millions of rows, this blocks all queries for seconds or minutes.

The community workaround has been the same since PostgreSQL 12:

-- Step 1: Add a CHECK constraint with NOT VALID (instant, no scan)
ALTER TABLE users
  ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (scans table, but only SHARE lock)
ALTER TABLE users
  VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Now SET NOT NULL is instant (PG sees the CHECK)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the redundant CHECK
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

This 4-step dance works, but it's verbose, easy to get wrong, and requires remembering to clean up the temporary CHECK constraint afterward. Every migration linter — Squawk, strong_migrations, MigrationPilot — teaches this pattern.

PG18: Native SET NOT NULL NOT VALID

PostgreSQL 18 added native support for SET NOT NULL NOT VALID. The 4-step workaround collapses to 2 steps:

-- PG18+: Native approach (2 steps instead of 4)

-- Step 1: Mark column NOT NULL without scanning (instant)
ALTER TABLE users ALTER COLUMN email SET NOT NULL NOT VALID;

-- Step 2: Validate (scans table under SHARE lock, not ACCESS EXCLUSIVE)
ALTER TABLE users VALIDATE NOT NULL email;

What happens under the hood

SET NOT NULL NOT VALID records the NOT NULL constraint in the system catalog (pg_attribute.attnotnull) but marks it as not-yet-validated. PostgreSQL will enforce the constraint for new INSERTs and UPDATEs immediately, but does not verify existing rows.

VALIDATE NOT NULL then scans existing rows under a SHARE lock (concurrent reads and writes are allowed) and, once all rows pass, marks the constraint as fully validated.

Migration linter implications

If you're running PG18+ and your migration linter still suggests the CHECK constraint workaround, it's giving you outdated advice. Worse, if you're still writing the old pattern, you're adding unnecessary complexity.

MigrationPilot v1.5.0 includes rule MP081 that detects the old CHECK workaround on PG18+ and suggests the simpler native syntax.

PG18: NOT ENFORCED Constraints

PostgreSQL 18 also introduced NOT ENFORCED constraints, borrowed from the SQL standard. A NOT ENFORCED constraint exists in metadata but PostgreSQL does not actually enforce it:

-- This constraint exists in pg_constraint but does NOT reject invalid data
ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive
  CHECK (total > 0) NOT ENFORCED;

-- This INSERT will succeed even though total is negative!
INSERT INTO orders (total) VALUES (-100);  -- No error

When NOT ENFORCED is useful

• Query planner hints: Foreign key constraints with NOT ENFORCED can help the planner generate better join plans without the overhead of enforcing referential integrity at write time.
• Gradual migration: Add a NOT ENFORCED constraint first, fix existing bad data, then ALTER the constraint to be enforced.
• Documentation: Record business rules in the schema even when enforcement happens at the application layer.

The risk

The danger is that a developer sees a CHECK constraint in the schema and assumes the database enforces it. NOT ENFORCED silently allows invalid data. Six months later, someone queries the table expecting all rows to satisfy the constraint, and gets corrupted results.

MigrationPilot v1.5.0 includes rule MP082 that warns whenever it detects NOT ENFORCED in a migration, ensuring teams consciously acknowledge the trade-off.

PG18: Foreign Key Collation Validation

PostgreSQL 18 tightened validation of foreign key constraints involving non-deterministic collations (ICU collations like und-x-icu). Previously, you could create a FK between columns using ICU collations even though equality comparisons with non-deterministic collations can be ambiguous.

-- PG18 may reject this if the columns use non-deterministic collation
CREATE TABLE child (
  parent_name TEXT COLLATE "und-x-icu",
  FOREIGN KEY (parent_name) REFERENCES parent(name)
);

-- The problem: with non-deterministic collation, 'cafe' and 'caf\u00e9'
-- might compare as equal, making FK lookups unreliable

This is a subtle correctness issue. Most teams won't hit it unless they're using ICU collations for internationalization. But if you are, a migration that worked on PG17 might fail on PG18 — or worse, succeed but produce incorrect referential integrity checks.

MigrationPilot v1.5.0 includes rule MP083 that flags foreign key constraints involving non-deterministic collations on PG18+.

What You Should Do

If you're upgrading to PG18

1. Search your migration files for CHECK ... IS NOT NULL ... NOT VALID. These are candidates for the simpler PG18 native syntax.
2. Audit any NOT ENFORCED constraints. Make sure every NOT ENFORCED usage has a comment explaining why enforcement is intentionally skipped.
3. Check FK columns for ICU collations. If you use und-x-icu or locale-specific ICU collations, verify that your FK references still work correctly.
4. Update your migration linter. Make sure it's PG18-aware. Set --pg-version 18 to enable PG18-specific rules.

If you're staying on PG16/17

Nothing changes. The CHECK constraint workaround remains the correct approach for PG12–17. MigrationPilot's existing rules (MP002, MP018) continue to guide you to the right pattern for your PostgreSQL version.

Which Linters Are PG18-Aware?

As of March 2026, no other PostgreSQL migration linter has PG18-specific rules. Squawk's last PostgreSQL-version-specific update targeted PG12 features. strong_migrations provides version-specific advice for Rails but hasn't added PG18 patterns yet.

Try It Now

# Check a migration against PG18 rules
npx migrationpilot analyze migration.sql --pg-version 18

# Or add to your GitHub Action
- uses: mickelsamuel/migrationpilot@v1
  with:
    migration-path: "migrations/*.sql"
    pg-version: "18"

MigrationPilot is open-source (MIT), runs 83 safety rules in milliseconds, and requires no database connection. The PG18 rules (MP081–MP083) are included in the free tier.