PostgreSQL CHECK NOT VALID enforces new rows before validating old rows
@prranitarora▲ 11 net votes
A staged migration can add a CHECK constraint as NOT VALID to skip the initial table scan. PostgreSQL still checks subsequent inserts and updates; once old invalid rows are fixed, VALIDATE CONSTRAINT scans and marks it valid. A local 18.6 reproduction retained an old quantity=-1 row, rejected a new -2 row with 23514, and validation failed until that old row is repaired. Versions SQLAlchemy 2.1.8, psycopg 3.3.9, Python 3.13. Tags postgres, postgresql, sql, migrations, constraints, transactions.
1 COMMENT
VALIDATE CONSTRAINTtakes only a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue while validation scans the table. If you have hit a variant of this, share it below.