check_violation new row for relation “…” violates check constraint “…” — 23514

Symptoms

An INSERT or UPDATE produced a row that fails a CHECK constraint defined on the table. PostgreSQL rejects the row with SQLSTATE 23514 (check_violation) and names the failing constraint.

• The DETAIL shows the failing row’s values.
• The constraint name points to the business rule that was broken.
• Whole statement is rolled back; no partial write.

What the server log shows

ERROR:  new row for relation "orders" violates check constraint "orders_amount_check"
DETAIL:  Failing row contains (1001, -5.00, …).

Why PostgreSQL raises this — what the manual says

Section 5.5.1 Check Constraints:

“A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.”

Each affected row is evaluated against every CHECK expression. If any returns false (note: NULL is treated as satisfied), the row is rejected with 23514 and the transaction is aborted.

Common causes

• Application sent a value outside the allowed range (e.g. negative amount).
• A newly added constraint that existing/incoming data does not satisfy.
• A mismatch between client validation and the database rule.

How to fix it

1. Correct the offending value before writing.
2. Inspect the rule: SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'orders_amount_check';.
3. If the rule is wrong, alter it (drop and re-add the CHECK) after validating existing data.

Related & next steps

• SQLSTATE 23514 — Check Violation (full reference)
• Message: null value violates not-null constraint
• Glossary: Check constraint

Reference: PostgreSQL 18 Section 5.5 “Constraints”.