EC
23514
PostgreSQLERRORCommonIntegrity Constraint ViolationHIGH confidence

new row for relation violates check constraint

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
  1. 1Inserting a value that violates a range or domain constraint (e.g., negative price)
  2. 2Updating a column to a value that the CHECK expression rejects
  3. 3Importing data that was valid under old constraints but violates a newly added constraint
  4. 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
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

expected output

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 When the input value is genuinely invalid and should be rejected or corrected at the application level.

Correct the data to satisfy the constraint
-- 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 When the constraint is too strict for legitimate data and needs to be relaxed.

Widen or drop the CHECK constraint
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

Removes the data quality guarantee; invalid rows will accumulate and corrupt reports and calculations.

Sources
Official documentation ↗

src/backend/executor/execMain.c — ExecConstraints()

Check Constraints ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors