How to fix ERROR: new row for relation violates check constraint in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 28, 2025· 224 views

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.

Quick fix

Fixes 23514

SELECT conname, consrc
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass
  AND contype = 'c';
  1. Identify the failing check constraint
  2. Review the constraint definition
  3. Validate your data against the constraint
  4. Fix the data to match the constraint
postgresql — error
SELECT conname, consrc
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass
  AND contype = 'c';
ERROR: new row for relation violates check constraint

What this error means

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.

Typical symptoms

  • INSERT or UPDATE operations fail with 'violates check constraint' error message
  • Error message includes the constraint name and details of the failing row
  • Application receives SQLSTATE code 23514
  • Some data passes validation while similar data fails unexpectedly

Common causes

  1. Data being inserted or updated doesn't satisfy the boolean expression defined in the CHECK constraint
  2. Column value is outside the allowed range specified by the constraint (e.g., negative age when constraint requires >= 0)
  3. String value doesn't match the constraint pattern or format requirements
  4. Timestamp or date value violates a date range constraint (e.g., end_date before start_date)
  5. Misunderstanding of constraint logic, especially when constraints reference multiple columns
  6. NULL handling confusion—constraints only fail when the expression evaluates to false, not NULL

How to fix "ERROR: new row for relation violates check constraint"

1Identify the failing check constraint

The error message shows which constraint was violated. If you need the full constraint definition, query the system catalog:

sql
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.

2Review the constraint definition

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.

3Validate your data against the constraint

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:

sql
-- Check if your value would pass the constraint
SELECT age >= 18 AS passes_constraint FROM (SELECT -5 AS age) t;
-- Returns false for age = -5
4Fix the data to match the constraint

Correct the problematic value in your INSERT or UPDATE statement. For example:

sql
-- 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.

5If the constraint is too restrictive, modify it

If the constraint logic is incorrect or outdated, you can alter it:

sql
-- 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.

6Handle NULL values correctly

Remember that CHECK constraints are satisfied when the boolean expression evaluates to true OR NULL. This means NULL values always pass the constraint:

sql
-- 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:

sql
ALTER TABLE employees ALTER COLUMN age SET NOT NULL;

Advanced notes

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:

sql
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.

Frequently asked questions

What does "23514" mean?
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.
How do I fix "23514"?
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.
What causes "23514"?
Data being inserted or updated doesn't satisfy the boolean expression defined in the CHECK constraint Column value is outside the allowed range specified by the constraint (e.g., negative age when constraint requires >= 0) String value doesn't match the constraint pattern or format requirements
Is the "23514" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading