PostgreSQL: new row violates check constraint

What it means

A CHECK constraint is a rule every row must pass, such as price > 0 or discount <= price. Your INSERT or UPDATE produced a row for which the rule came out false, so the statement was rejected and nothing it did was kept:

ERROR:  new row for relation "products" violates check constraint "discount_le_price"
DETAIL:  Failing row contains (2, Mug, 12.00, 15.00, 5).

The constraint name tells you which rule. Unnamed ones get <table>_<column>_check.
DETAIL lists the row’s values in column order, after defaults and the update were applied.

A rule that comes out NULL passes. CHECK (price > 0) accepts a NULL price; add NOT NULL if the value is required. When several rules fail, the error names the first in alphabetical order by constraint name, so fixing one may reveal the next.

The same code, 23514, covers two relatives: check constraint "…" of relation "…" is violated by some row when you add a rule that existing rows break, and value for domain … violates check constraint "…" for a domain type’s rule.

Common causes

Bad input: a zero or negative amount, an end date before the start date, a status that isn’t in the allowed list.
A rule across columns, broken by updating one of them: lowering a price below its discount.
A rule that’s stricter than the data: a new status value the application now uses, or a length limit that was a guess.
Adding a rule to a table whose existing rows don’t pass it.

How to fix it

Read the rule

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass AND contype = 'c'
ORDER BY conname;

In psql, \d products lists the same under “Check constraints”. Compare the definition with the values in DETAIL.

Fix the data

If the rule is right, the statement isn’t: correct the value, or update the related columns together in one statement (SET price = 9.00, discount = 1.00), so the row is valid when the check runs.

Change the rule

If the rule is wrong (a status list that needs a new value, a limit that’s too tight), replace it:

ALTER TABLE orders DROP CONSTRAINT orders_status_check,
  ADD CONSTRAINT orders_status_check CHECK (status IN ('open', 'paid', 'refunded', 'disputed'));

Adding a check reads every row under an ACCESS EXCLUSIVE lock, so reads and writes wait for the whole scan; see below for the gentler way.

Add a rule to a table that has bad rows

NOT VALID adds the rule for new rows without checking old ones. Fix the old rows, then validate:

ALTER TABLE products ADD CONSTRAINT stock_nonneg CHECK (stock >= 0) NOT VALID;
SELECT * FROM products WHERE NOT (stock >= 0);
UPDATE products SET stock = 0 WHERE stock < 0;
ALTER TABLE products VALIDATE CONSTRAINT stock_nonneg;

VALIDATE scans the table under a lock that doesn’t block reads or writes; see adding a CHECK constraint for the locks.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE products (
  id integer PRIMARY KEY, name text,
  price numeric(8,2) CHECK (price > 0),
  discount numeric(4,2), stock integer,
  CONSTRAINT discount_le_price CHECK (discount <= price));

INSERT INTO products VALUES (1, 'Mug', 0, 0, 5);
INSERT INTO products VALUES (2, 'Mug', 12.00, 15.00, 5);

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Mug, 0.00, 0.00, 5).
ERROR:  new row for relation "products" violates check constraint "discount_le_price"
DETAIL:  Failing row contains (2, Mug, 12.00, 15.00, 5).

INSERT INTO products VALUES (3, 'Mug', NULL, 99.00, 5) succeeded: both rules are NULL for that row. With a valid row 4 (price 12.00, discount 2.00), UPDATE products SET price = -1 WHERE id = 4 broke both rules, and the error named discount_le_price, the first alphabetically. With a row whose stock was -2 in the table, ADD CONSTRAINT stock_nonneg CHECK (stock >= 0) failed with check constraint "stock_nonneg" of relation "products" is violated by some row; the NOT VALID, fix, VALIDATE sequence above worked. A domain:

ERROR:  value for domain seo_err_pg.email violates check constraint "email_check"

With \set VERBOSITY verbose, psql shows the code and the constraint’s name in its own field:

ERROR:  23514: new row for relation "products" violates check constraint "discount_le_price"
DETAIL:  Failing row contains (2, Mug, 12.00, 15.00, 5).
SCHEMA NAME:  seo_err_pg
TABLE NAME:  products
CONSTRAINT NAME:  discount_le_price

PostgreSQL 14.24 gives the same messages.

In Inlet

The structure editor lists a table’s check constraints and adds or changes them with the DDL shown first. Grid edits are staged and saved together in one transaction when you commit (⌘S), so a row that breaks a rule fails the save without leaving half of it written; Review shows the exact SQL first.

new row violates check constraint