Incident brief
New row violates check constraint
A column or table has a CHECK constraint, and an INSERT or UPDATE produced a row where that check evaluated to false. PostgreSQL rejects the row.
In 10 seconds
- What
- New row violates check constraint
- What triggers it
- Create a table with a CHECK constraint, for example price > 0.
- The fix
- Fix the value so it satisfies the check before inserting.
- Proof
- Reproduced on PostgreSQL 16.14 → The INSERT with price = -10 was rejected with SQLSTATE 23514 because -10 > 0 is false, and the row was never stored.
The fix
What to do right now
The immediate, application-level response to this error.
- Fix the value so it satisfies the check before inserting.
- If the rule was wrong, ALTER the constraint rather than working around it in application code.
- Remember a CHECK constraint passes on NULL — pair it with NOT NULL if NULL should also be rejected.
CREATE TABLE catalog.products (
id integer primary key,
price numeric CHECK (price > 0)
);
-- a value that satisfies the check
INSERT INTO catalog.products (id, price) VALUES (1, 19.99);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §5.4.1 Check Constraints
It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value.Read the full section on postgresql.org →
The insert that violates the check
price > 0 evaluates to false for -10, so the CHECK constraint rejects the row before it's stored. The DETAIL line shows exactly which row failed.Checking the table afterward
The table is empty. The rejected insert left nothing behind to clean up.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a table with a CHECK constraint, for example price > 0.
- 2Try to insert a row that fails the check, for example a negative price.
- 3PostgreSQL rejects the row with SQLSTATE 23514.
One client: create a table with a CHECK (price > 0), insert a negative price, then check the table.
DROP TABLE IF EXISTS catalog.products;
CREATE TABLE catalog.products (
id integer primary key,
price numeric CHECK (price > 0)
);INSERT INTO catalog.products (id, price) VALUES (1, -10);SELECT * FROM catalog.products;What PostgreSQL actually returned
DROP TABLE
CREATE TABLEERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (1, -10). id | price
----+-------
(0 rows)The manual's own wording — a check constraint passes on true OR null — was tested directly: a NULL price was inserted into the same price > 0 column and it went through with no error at all.
Insert a row with price = NULL into the same table whose CHECK just rejected -10, and check whether it's accepted.
Without this
Above: a negative price is rejected by the check.
With this, tested
Below: a NULL price passes the same check, because the comparison itself evaluates to null, not false.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed