23514ERRORTier 1 — Safe✅ HIGH confidence

new row for relation violates check constraint

Category: Integrity Constraint ViolationVersions: All Postgres versions

What this means

A row being inserted or updated failed a CHECK constraint expression. Postgres evaluates all CHECK constraints after the tuple is formed but before it is committed to the heap.

Why it happens

1Inserting a value that violates a range or domain constraint (e.g., negative price)
2Updating a column to a value that the CHECK expression rejects
3Importing data that was valid under old constraints but violates a newly added constraint
4Application logic mismatch: the client does not enforce the same rule as the database

How to reproduce

An INSERT violates a CHECK constraint requiring price to be positive.

trigger — this will ERROR

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  price NUMERIC CHECK (price > 0)
);

INSERT INTO products (price) VALUES (-5.00); -- triggers 23514

ERROR: new row for relation "products" violates check constraint "products_price_check" DETAIL: Failing row contains (1, -5.00).

Fix 1: Correct the data to satisfy the constraint

When the input value is genuinely invalid and should be rejected or corrected at the application level.

fix

-- Insert a valid value:
INSERT INTO products (price) VALUES (9.99);

-- Or clamp in the query (only if business rules allow it):
INSERT INTO products (price) VALUES (GREATEST(0.01, -5.00));

Why this works

CHECK constraints are evaluated by ExecConstraints() in the executor after the new tuple is built from column values. The expression is evaluated in the context of the new row; if it returns false or NULL the tuple is rejected.

Fix 2: Widen or drop the CHECK constraint

When the constraint is too strict for legitimate data and needs to be relaxed.

fix

ALTER TABLE products DROP CONSTRAINT products_price_check;
ALTER TABLE products ADD CONSTRAINT products_price_check CHECK (price >= 0);

Why this works

DROP CONSTRAINT removes the CHECK expression from pg_constraint. The subsequent ADD CONSTRAINT adds a new expression. By default ADD CONSTRAINT scans all existing rows to verify them; use NOT VALID to skip the scan and only validate new rows going forward.

What not to do

✗

Drop the CHECK constraint permanently to silence the error

Why it's wrong: Removes the data quality guarantee; invalid rows will accumulate and corrupt reports and calculations.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

🔧 Source ref: src/backend/executor/execMain.c — ExecConstraints()

📖 Further reading: Check Constraints

Confidence assessment

✅ HIGH confidence

Stable and well-documented. CHECK constraint evaluation is consistent across versions. Edge case: CHECK constraints are not evaluated for NULL input by default; a constraint CHECK (price > 0) permits NULL values unless the column is also NOT NULL.