A check constraint violation occurs when you attempt to insert or update data that doesn't meet the boolean condition defined by a CHECK constraint on a table column. This is PostgreSQL's way of enforcing data integrity rules at the database level.
PostgreSQL check constraints are conditions defined on table columns to ensure inserted or updated data meets specific criteria. When a CHECK constraint is violated, PostgreSQL throws error code 23514 (check_violation). The constraint evaluates a boolean expression on every insert and update—if the expression evaluates to false, the operation is rejected and the constraint is violated. The error message will indicate which constraint was violated and details of the failing row. Understanding the constraint definition is the first step to fixing the issue.
The error message shows which constraint was violated. If you need the full constraint definition, query the system catalog:
SELECT conname, consrc
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass
AND contype = 'c';Replace your_table with the actual table name. The consrc column shows the boolean expression the constraint is checking.
Understand what condition the CHECK constraint is enforcing. For example, if the constraint is age >= 18, then any INSERT or UPDATE with age < 18 will fail. Check the consrc output from the previous step to see exactly what expression must be satisfied.
Before inserting or updating, verify that your data meets the constraint condition. For example, if the constraint is salary > 0, ensure your salary value is positive:
-- Check if your value would pass the constraint
SELECT age >= 18 AS passes_constraint FROM (SELECT -5 AS age) t;
-- Returns false for age = -5Correct the problematic value in your INSERT or UPDATE statement. For example:
-- FAILS: violates check constraint
INSERT INTO employees (name, age) VALUES ('John', -5);
-- SUCCEEDS: age is now positive
INSERT INTO employees (name, age) VALUES ('John', 25);This is the most common fix—ensure the data being inserted or updated satisfies the constraint condition.
If the constraint logic is incorrect or outdated, you can alter it:
-- Drop the old constraint
ALTER TABLE employees DROP CONSTRAINT age_check;
-- Add a new, corrected constraint
ALTER TABLE employees ADD CONSTRAINT age_check CHECK (age >= 0);Be careful—dropping a constraint won't fix existing data that violates it. If existing rows fail the new constraint, the ALTER TABLE will fail.
Remember that CHECK constraints are satisfied when the boolean expression evaluates to true OR NULL. This means NULL values always pass the constraint:
-- Both of these succeed, even with age = NULL
INSERT INTO employees (name, age) VALUES ('John', 25);
INSERT INTO employees (name, age) VALUES ('Jane', NULL);If you need to prevent NULL values in addition to the CHECK constraint, add a NOT NULL constraint:
ALTER TABLE employees ALTER COLUMN age SET NOT NULL;PostgreSQL assumes CHECK constraints are immutable—they always return the same result for the same input row. This is why PostgreSQL only evaluates CHECK constraints during INSERT and UPDATE operations, not continuously. If your constraint references functions with side effects or mutable conditions, you may encounter unexpected behavior.
For complex cross-table validation (e.g., ensuring a child row's parent exists), use FOREIGN KEY constraints instead. If you need one-time validation against other rows, use a trigger that throws an error when the condition is violated.
In large tables, query pg_constraint to inspect constraint definitions directly. Use information_schema.check_constraints to view all check constraints in your database:
SELECT table_name, constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'public';Deferrable constraints (DEFERRABLE INITIALLY DEFERRED) allow constraint checks to be postponed until transaction commit, which can be useful when inserting multiple related rows. However, standard CHECK constraints cannot be deferrable—only UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUDE constraints support deferrable behavior.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL