What NOT VALID means for PostgreSQL CHECK constraints

Picture the situation: you have a table that records background job executions, and an older worker wrote -1 into duration_ms whenever a job exited before its timer finished. Someone used -1 as an in-band sentinel for an unknown duration so the column could stay NOT NULL. You now want to enforce a rule that recorded durations must be zero or positive, while existing rows in the table still carry -1.

PostgreSQL gives you a two-step way to add a CHECK constraint without scanning existing rows during the initial schema change: add the constraint as NOT VALID, clean up the historical data, and run VALIDATE CONSTRAINT later. Because the syntax says NOT VALID, the wording alone makes it worth checking what PostgreSQL defers and what it enforces right away. Adding a NOT VALID check constraint skips the initial existing-row scan, while still enforcing the rule on incoming writes.

Once the transaction that adds the NOT VALID constraint commits, PostgreSQL evaluates the check expression against every newly inserted row and every updated row. We can start by creating the table and seeding two rows:

To try these statements in a scratch database, keep autocommit enabled because several examples intentionally trigger errors; inside an explicit transaction block, a failed statement aborts the transaction and requires ROLLBACK before you can continue.

CREATE TABLE job_runs (
    id bigint PRIMARY KEY,
    duration_ms integer NOT NULL,
    label text NOT NULL
);

INSERT INTO job_runs VALUES
    (1, -1, 'legacy'),
    (2, 25, 'valid');

In this table, duration_ms already has a column-level NOT NULL constraint. That matters because SQL CHECK expressions pass whenever the predicate evaluates to true or unknown (NULL), as explained in the PostgreSQL constraints documentation. A check written as CHECK (duration_ms >= 0) rejects negative integers, while the separate NOT NULL definition is what rejects NULL values with SQLSTATE 23502 (which our test script also confirmed after validation). Here, row 1 holds the legacy -1 sentinel, and row 2 holds a valid duration of 25.

Adding the constraint skips existing rows but enforces new writes#

To avoid scanning the table while holding an exclusive lock, we add a standalone CHECK constraint on this nonpartitioned table with the NOT VALID modifier, alongside a local lock timeout so the statement aborts quickly if another session holds a conflicting lock.

BEGIN;
SET LOCAL lock_timeout = '200ms';
ALTER TABLE job_runs
    ADD CONSTRAINT job_runs_duration_nonnegative
    CHECK (duration_ms >= 0) NOT VALID;
COMMIT;

Querying the table and the pg_constraint catalog right after commit shows that the legacy row is untouched and the constraint is marked unvalidated.

SELECT id, duration_ms, label
FROM job_runs
ORDER BY id;
-- 1 | -1 | legacy
-- 2 | 25 | valid

SELECT convalidated
FROM pg_constraint
WHERE conrelid = 'job_runs'::regclass
  AND conname = 'job_runs_duration_nonnegative';
-- f

The catalog column convalidated is f (false), which records that the existing rows have not been validated against the predicate, and row 1 still has -1. However, the constraint is already active for new writes:

INSERT INTO job_runs VALUES (3, -2, 'new');
-- ERROR, SQLSTATE 23514: violates check constraint

INSERT INTO job_runs VALUES (3, 30, 'new');
-- INSERT 0 1

Inserting a new row with -2 fails immediately with SQLSTATE 23514, while inserting 30 succeeds. Operational enforcement for incoming writes begins when the constraint is added and committed; VALIDATE CONSTRAINT is not the activation step. Before you commit a NOT VALID check constraint, every writer that can emit a negative duration must already be updated, including background workers, import jobs, retry queues, and any in-flight executions started by older code.

Why updating an unrelated column on a legacy row fails#

Because untouched legacy rows remain in the table while convalidated is false, it is natural to wonder what happens when an application updates another column on one of those rows without touching duration_ms. We can test this by updating only label on both existing rows:

UPDATE job_runs SET label = 'reviewed' WHERE id = 1;
-- ERROR, SQLSTATE 23514: violates check constraint

UPDATE job_runs SET label = 'reviewed' WHERE id = 2;
-- UPDATE 1

Updating label on row 2 succeeds because its duration is 25. Updating label on row 1 fails with SQLSTATE 23514, even though duration_ms does not appear in the SET clause.

In PostgreSQL, an UPDATE creates a new row version, and constraint enforcement evaluates the resulting row rather than only the columns modified by the statement. As the ALTER TABLE documentation notes, a NOT VALID constraint is enforced against subsequent inserts and updates. In the PostgreSQL 18.1 executor (ExecConstraints and ExecRelCheck in src/backend/executor/execMain.c), the engine checks enabled check constraints against the candidate tuple being written to the table without skipping constraints whose convalidated flag is false.

This has a direct deployment consequence: fixing the writers that emit negative durations is necessary, but it does not make legacy violating rows editable. A code path that never writes duration_ms will still fail with 23514 if it updates a status note, retry count, or label on a row where duration_ms is still -1. Untouched violating rows can stay at rest in the table until cleanup, but if your application must update those historical rows before backfill completes, you need to clean them up first or ensure the update path also sets a valid duration.

Cleaning up legacy rows and validating the constraint#

If you run VALIDATE CONSTRAINT while row 1 still violates the predicate, PostgreSQL scans the table, encounters the invalid row, and aborts the validation:

ALTER TABLE job_runs
    VALIDATE CONSTRAINT job_runs_duration_nonnegative;
-- ERROR, SQLSTATE 23514: constraint is violated by some row

When validation fails with SQLSTATE 23514, the catalog change rolls back: convalidated stays f, and new inserts and updates continue to be checked. Once every existing row satisfies the predicate, validation can succeed:

UPDATE job_runs
SET duration_ms = 0, label = 'corrected'
WHERE id = 1;
-- UPDATE 1

ALTER TABLE job_runs
    VALIDATE CONSTRAINT job_runs_duration_nonnegative;
-- ALTER TABLE

SELECT convalidated
FROM pg_constraint
WHERE conrelid = 'job_runs'::regclass
  AND conname = 'job_runs_duration_nonnegative';
-- t

Setting duration_ms = 0 here is only an artificial value to make this two-row example pass validation, not a recommendation to rewrite unknown durations to zero in production. In a real migration, you must decide how your domain represents an unknown duration and how your writers behave before enforcing the invariant, such as making the column nullable, tracking completion status in a separate column, or computing the real duration from stored timestamps.

Locking across constraint addition and validation#

Splitting constraint addition from validation changes which table lock is held during the full-table scan, though both phases still take locks. As detailed in the PostgreSQL locking documentation and the ALTER TABLE reference, each command holds its lock until the end of its transaction:

1. ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID takes an ACCESS EXCLUSIVE lock. It avoids scanning existing rows, but ACCESS EXCLUSIVE conflicts with all other table lock modes, including ACCESS SHARE from ordinary SELECT queries. If an open reader already holds a lock on the table, the ALTER TABLE statement waits behind it, and can delay later reads and writes while it waits or holds the lock. Setting a lock_timeout (such as the 200ms bound in our example) bounds how long the statement waits before failing with SQLSTATE 55P03; it limits lock waiting, but does not establish production sizing, statement execution time or application impact.
2. ALTER TABLE ... VALIDATE CONSTRAINT ... scans existing rows under a SHARE UPDATE EXCLUSIVE lock. That mode is compatible with ACCESS SHARE (SELECT) and ROW EXCLUSIVE (ordinary INSERT, UPDATE, and DELETE), so normal read and write table locks can still be granted. It is neither lock-free nor zero-impact: it conflicts with VACUUM, schema changes, index builds, and other validations, and the table scan still consumes I/O and CPU.

Because PostgreSQL retains table locks until transaction end, you must commit ADD CONSTRAINT ... NOT VALID before starting a separate transaction for VALIDATE CONSTRAINT. Running both in a single transaction keeps the initial ACCESS EXCLUSIVE lock held across the entire validation scan, defeating the two-transaction split.

Method and lab limits#

We ran these checks on PostgreSQL 18.1 (x86_64-apple-darwin23.6.0) under default READ COMMITTED isolation using a two-row seed table (duration_ms = -1 and 25). All 15 checks in test-not-valid.mjs passed, as recorded in test-results.json.

Beyond the SQL outputs shown above, the harness verified the lock behaviors across two sessions. When an open reader held a lock on job_runs, ADD CONSTRAINT ... NOT VALID timed out with a 200ms lock-wait budget and SQLSTATE 55P03 and created no constraint. When unblocked, the ADD CONSTRAINT transaction held AccessExclusiveLock in pg_locks until commit. During the later validation check, VALIDATE CONSTRAINT held ShareUpdateExclusiveLock in pg_locks; ordinary SELECT, INSERT, and UPDATE statements from a second session succeeded after the VALIDATE CONSTRAINT statement had finished while its transaction and ShareUpdateExclusiveLock remained open.

This controlled lab tests SQL semantics and lock-mode compatibility on a small synthetic table. It does not test concurrent scan contention, sustained load, throughput or downtime, application or ORM integration, or a Proofline detection run. Proofline examines changes in their target environment using recorded evidence; see how Proofline works.