SQLSTATE 23514check_violationHTTP 400
Postgres error 23514: check violation
new row for relation "x" violates check constraint "y"
A row failed a CHECK constraint.
Common causes
- The value breaks a rule the table enforces, such as a negative price or a status outside the allowed list.
- Adding a CHECK constraint to a table whose existing rows break it.
How to fix it
- Read the constraint (\d+ table, or pg_get_constraintdef) and validate the same rule in the app.
- When adding a constraint to a large table, add it NOT VALID, fix old rows, then VALIDATE CONSTRAINT.
alter table products add constraint price_positive check (price_cents > 0) not valid;
update products set price_cents = 1 where price_cents <= 0;
alter table products validate constraint price_positive;Through a REST API
PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 400 for this error and returns the SQLSTATE in the code field of the JSON error body.
Questions
What does Postgres error 23514 mean?
23514 is check_violation in class 23 (Integrity Constraint Violation). A row failed a CHECK constraint.
How do I fix 23514?
Read the constraint (\d+ table, or pg_get_constraintdef) and validate the same rule in the app. When adding a constraint to a large table, add it NOT VALID, fix old rows, then VALIDATE CONSTRAINT.
What HTTP status does a REST API return for 23514?
PostgREST, the REST layer behind supabase-js, answers 400.