PostgreSQL CHECK Constraints: Row-Local Rules and Why Cross-Row Comparisons Break Dumps
PostgreSQL CHECK constraints may only reference the row being inserted or updated. A CHECK that compares against other rows can pass simple tests yet fail during pg_dump restore, because rows load in an order that may not satisfy it.
On this page
Key idea
In PostgreSQL, a CHECK constraint is a Boolean expression evaluated against the new or updated row only. It may reference columns of that row, constants, immutable functions, and operators, but it must not reference other rows or other tables. PostgreSQL does not enforce this restriction at definition time, so a cross-row CHECK can be created and may appear to work in small tests. It cannot, however, guarantee the invariant, because later changes to the referenced row can falsify the condition without re-checking the original row. The documented consequence is that a database dump and restore can fail: rows are reloaded in an order that may not satisfy the constraint, even when the final database state is consistent. For cross-row or cross-table rules, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints, or enforce the rule in application logic or a trigger. Remember also that a CHECK passes when the expression evaluates to NULL, so pair it with NOT NULL when nulls must be excluded.
Permissible CHECK Constraints
A CHECK constraint in PostgreSQL is the most generic constraint type. It attaches a Boolean expression to a column or to the table, and the expression is evaluated whenever a row is inserted or updated. The expression should involve the constrained column, otherwise it serves little purpose. Column constraints and table constraints are interchangeable in many cases, and a table constraint can reference several columns of the same row.
The expression may use columns of the row being checked, literal constants, operators, and functions. It must not reference table data other than the new or updated row. This is a documented restriction, not a stylistic preference. The constraint is checked against the candidate row in isolation, so it has no access to other rows at evaluation time.
A subtlety that surprises many practitioners is null handling. A CHECK constraint is satisfied when the expression evaluates to true or to the null value. Because most expressions yield null when any operand is null, a CHECK does not by itself prevent null values in the constrained columns. To forbid nulls, add a NOT NULL constraint, which is functionally equivalent to CHECK (column IS NOT NULL) but more efficient in PostgreSQL.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (price > discounted_price)
);The Risk of Cross-Row References
PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. The documentation is explicit that a CHECK violating this rule may appear to work in simple tests, but it cannot guarantee that the database will not reach a state in which the constraint condition is false. The reason is that the condition depends on other rows, and those rows can change after the original row was validated. Nothing re-checks the original row when the referenced row is modified.
This is a correctness problem, not merely a performance one. A constraint that only holds at insert time gives a false sense of integrity. The database can drift into a state where the invariant is violated, and no error is raised at the moment of drift. The failure surfaces later, often at the worst possible time.
The same reasoning applies to functions used inside a CHECK. A function that reads other tables introduces the same cross-row dependency, even though the syntax looks local. The restriction is about what the expression can observe, not about how it is written.
Dump and Restore Failures
The documented consequence of a cross-row CHECK is that a database dump and restore can fail. During restore, rows are loaded in an order determined by the dump, and that order may not satisfy the constraint at each intermediate step. The restore can fail even when the complete database state is consistent with the constraint, because the constraint is evaluated row by row as data is inserted.
This makes the problem operationally serious. A backup that restores cleanly in development may fail in production if row ordering differs, if the data volume changes the load order, or if the dump was taken at a different point in the data lifecycle. The failure is not deterministic with respect to the final state; it depends on the path taken to reach that state.
The practical lesson is that a constraint which cannot be evaluated from a single row cannot be relied upon as a declarative guarantee. It may pass tests, and it may even pass a restore on one occasion, but it does not provide the integrity property it appears to provide.
Recommended Alternatives
When a rule genuinely spans rows or tables, PostgreSQL offers declarative constraints designed for that purpose. Use UNIQUE for uniqueness across rows, EXCLUDE for range and overlap rules, and FOREIGN KEY for referential integrity. These constraints are enforced by the database against the relevant rows and are maintained correctly as data changes.
For rules that none of these express, use a trigger or application-level validation, and document clearly that the rule is not a declarative constraint. A trigger can observe other rows and can be written to re-validate on the relevant changes, but it carries its own complexity and must be maintained carefully.
A useful mental model is to ask whether the rule can be decided from the candidate row alone. If yes, a CHECK is appropriate and cheap. If no, the rule belongs to a constraint type that understands the relationship, or to procedural code that you accept as the enforcement point.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);Applicability
- Does the CHECK expression reference only columns of the row being inserted or updated?
- Does the rule involve other rows or other tables, which would require UNIQUE, EXCLUDE, or FOREIGN KEY instead?
- Are nullable columns paired with NOT NULL where nulls must be excluded?
- Have you tested a dump and restore of the table to confirm the constraint survives reloading?
- Is the constraint named so it can be identified and altered later?
Where this applies
This article describes PostgreSQL behavior as documented for supported versions (14 through 18 at the time of writing). The exact wording of error messages and the behavior of dump and restore can vary by version and by the tools used (pg_dump, pg_restore, or logical replication). The examples are illustrative and assume a default installation with no custom constraint triggers. The article does not cover deferred constraints, exclusion constraint operators, or trigger-based enforcement in detail; those require separate treatment. It also does not claim that any particular restore will fail, only that a cross-row CHECK cannot guarantee integrity and can cause a restore to fail depending on row load order.