Check Constraint Add Violated By Some Row

ADD CONSTRAINT for a CHECK is validating: before the constraint is recorded, PostgreSQL scans the whole table (ATRewriteTable) and tests every row against the predicate under an ACCESS EXCLUSIVE lock. The first row that fails raises 23514 and rolls the ALTER back, so the constraint is never half-applied. The error message intentionally does not name a row, because the failure is a property of the existing data set, not of one statement -- which is why the fix is to clean the data, not to change the DDL.

Once the table is clean the same ADD CONSTRAINT succeeds and the constraint becomes convalidated (fully trusted), after which any future rule-breaking INSERT or UPDATE is rejected at write time with the second 23514 form. Once the table is clean the same ADD CONSTRAINT succeeds and the constraint becomes convalidated (fully trusted), after which any future rule-breaking INSERT or UPDATE is rejected at write time with the second 23514 form.

Free Tier -- Basic Approach

Before touching anything, measure the blast radius with one count: how many rows break the rule the CHECK would enforce. Five. That single number tells you the cleanup is tiny and bounded -- not an unknown.

SELECT count(*) AS bad_rows FROM line_items WHERE NOT (quantity > 0);

bad_rows
----------
        5
(1 row)

SELECT count(*) AS bad_rows FROM line_items WHERE NOT (quantity > 0); -- reuse the exact predicate from the constraint so the count matches what ADD CONSTRAINT will reject.

Make the data obey the rule, then add the constraint. The five zero-quantity rows are bug artifacts with no business value, so they are deleted (DELETE 5); if the violators carried real meaning you would UPDATE them instead. With the table clean, the same ADD CONSTRAINT that failed now succeeds (ALTER TABLE).

DELETE FROM line_items WHERE NOT (quantity > 0);
ALTER TABLE line_items ADD CONSTRAINT line_items_qty_pos CHECK (quantity > 0);

DELETE 5
ALTER TABLE

DELETE FROM line_items WHERE NOT (quantity > 0); then ALTER TABLE line_items ADD CONSTRAINT line_items_qty_pos CHECK (quantity > 0); -- on a large hot table, prefer ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT to avoid holding ACCESS EXCLUSIVE for the full scan.

Confirm the constraint is present and validated, that zero rows break the rule, and that the constraint now enforces itself at write time. The rejected insert raises the OTHER 23514 form -- 'new row for relation ... violates check constraint ...' with DETAIL: Failing row contains (1001, ord-x, 0) -- which is the run-time enforcement you wanted all along.

SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'line_items'::regclass AND contype = 'c';

conname       | convalidated
--------------------+--------------
 line_items_qty_pos | t
(1 row)

SELECT count(*) AS bad_rows FROM line_items WHERE NOT (quantity > 0);

bad_rows
----------
        0
(1 row)

INSERT INTO line_items(order_ref, quantity) VALUES ('ord-x', 0);

ERROR:  new row for relation "line_items" violates check constraint "line_items_qty_pos"
DETAIL:  Failing row contains (1001, ord-x, 0).

Check pg_constraint for conname/convalidated = t, re-run the count(*) WHERE NOT (quantity > 0) (expect 0), and attempt INSERT ... VALUES ('ord-x', 0) to confirm it is rejected with SQLSTATE 23514 and a Failing row DETAIL.

📦 Version Matrix (PG 14--18)

The same failure-then-clean-fix is identical on PostgreSQL 14 through 18: ADD CONSTRAINT against dirty data reports 'is violated by some row', and after removing the violators the same ADD CONSTRAINT returns ALTER TABLE. The 23514 behavior of validating ADD CONSTRAINT is stable across all supported majors.

Frequently Asked Questions

References

• PostgreSQL: CREATE TABLE -- CHECK constraints
• PostgreSQL: ALTER TABLE -- ADD CONSTRAINT / NOT VALID / VALIDATE CONSTRAINT
• PostgreSQL: Error Codes (Class 23 -- Integrity Constraint Violation, 23514 check_violation)
• PostgreSQL: pg_constraint system catalog (convalidated)