---
title: "What NOT VALID means for PostgreSQL CHECK constraints | Proofline"
description: "Adding a PostgreSQL CHECK constraint with NOT VALID skips the initial scan of existing rows, but immediately enforces the check against new inserts and any update to an existing row."
url: https://proofline.sh/learn/postgres-not-valid-constraints/
---

[Learning center](/learn/)

# What NOT VALID means for PostgreSQL CHECK constraints

Adding a PostgreSQL CHECK constraint with NOT VALID skips the initial scan of existing rows, but immediately enforces the check against new inserts and any update to an existing row.

Breaking migrations The Proofline team November 6, 2026 7 min read

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](https://www.postgresql.org/docs/18/ddl-constraints.html). 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](https://www.postgresql.org/docs/18/sql-altertable.html) 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`](https://github.com/postgres/postgres/blob/REL_18_1/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](https://www.postgresql.org/docs/18/explicit-locking.html) and the [`ALTER TABLE` reference](https://www.postgresql.org/docs/18/sql-altertable.html), 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`](/blog-assets/postgres-not-valid-constraints/test-not-valid.mjs) passed, as recorded in [`test-results.json`](/blog-assets/postgres-not-valid-constraints/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](/how-it-works/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free150)

## Related reading

-   ### [PostgreSQL migration review for rolling deployments](/learn/database-migration-review/)
    
    Breaking migrations The Proofline team October 7, 2026 6 min read
