PostgreSQL 18 · first-party documentation guide

PostgreSQL NOT VALID: why old invalid rows can remain

If adding the constraint succeeded but old invalid rows remain, check whether the constraint was added NOT VALID.

Start with the symptom

If adding the constraint succeeded but old invalid rows remain, check whether the constraint was added NOT VALID.

What the documentation establishes

In PostgreSQL 18, adding a foreign-key or CHECK constraint NOT VALID can skip scanning existing rows.

The constraint is not simply disabled: subsequent INSERT and UPDATE operations are still checked.

Five checks at a glance

Do not over-interpret the symptom

Validating historical rows is a separate VALIDATE CONSTRAINT step. The documentation describes a separate lock level for this validation.

Read-only preflight

• Constraint added ≠ historical rows validated.
• NOT VALID can skip validation of existing rows.
• NOT VALID is not NOT ENFORCED.
• Historical validation is a separate operation.
• Treat deployment and historical validation as separate evidence.

Evidence limits

Do not equate successful constraint deployment with completed historical validation. No production SQL or lock timing was tested.

This article interprets first-party documentation and uses hypothetical diagrams. It does not report a live device, database, customer-data, outage, recovery or performance test.

• https://www.postgresql.org/docs/current/sql-altertable.html

The public YouTube schedule may be linked here after the provider assigns the native video ID. The article and local video are already self-contained.