Skip to content

23514 — check_violation

PostgreSQL SQLSTATE 23514: check_violation with CHECK and partition validation boundaries.

23514 — check_violation

At a glance

23514 means a CHECK or related row constraint evaluated as false. The selected named constraint is amount_positive; the server reports the table and constraint and the invalid row detail.

Field Value
SQLSTATE 23514
Condition check_violation
Status active
Known present by 7.4
Locked snapshots 9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3
Macros ERRCODE_CHECK_VIOLATION
Aliases —

Meaning

For an ordinary table row, the executor evaluates the CHECK expression and reports 23514 when it is false; a CHECK expression that evaluates to NULL passes PostgreSQL’s CHECK rule, so add NOT NULL when NULL itself is forbidden. Partition routing can report no partition of relation ... found for row with a partition-key detail, while a partition constraint check reports a partition-constraint message. Domain validation has its own “values that violate the new constraint” template, and an empty WITHOUT OVERLAPS value is another source path. These are the same SQLSTATE with different mechanisms and messages.

Diagnosis

Record constraint_name, table_name, and the failing-row or partition-key detail. Re-evaluate the expression with the actual types and NULL behavior, including implicit casts and trigger changes. For a partition error, inspect the partition bounds and the route selected by the key; for a domain or validation error, identify the schema object whose rule was checked. The selected autocommit case became IDLE after the error; explicit transactions still require the caller’s rollback or handler boundary.

Response

Correct the value or the business rule, then retry. If the rule is changing, validate existing rows and deploy the new constraint deliberately; do not disable a CHECK to hide bad data. For partitions, route the row to a partition whose bound admits it rather than treating the error as a generic row retry.

Observed diagnostics

18.6 (Homebrew) / latest:SQLSTATE 23514; primary new row for relation "items" violates check constraint "amount_positive"; DETAIL Failing row contains (2, -1).; status_after_error IDLE. 10.21 (Debian 10.21-1.pgdg90+1) / pg10:SQLSTATE 23514; primary new row for relation "items" violates check constraint "amount_positive"; DETAIL Failing row contains (2, -1).; status_after_error IDLE.

Representative case

In this example, the first insert violates the named CHECK because amount is -1; changing it to 1 is the concrete repair. The complete setup, assertions, and cleanup are in the case export:

-- create
CREATE TABLE items(id integer PRIMARY KEY, amount integer CONSTRAINT amount_positive CHECK (amount > 0));
-- seed
INSERT INTO items VALUES (1, 10);
-- trigger
INSERT INTO items VALUES (2, -1);
-- repair
INSERT INTO items VALUES (2, 1);
-- verify
SELECT id, amount FROM items ORDER BY id;

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the checked case registry; see the structured evidence and case export.

Authored evidence IDs: identity, row-path, schema-path, runtime. Selected runtime records: runtime.23514-batch1-latest-20260909.latest, runtime.23514-batch1-pg10-20260909.pg10.

Versions

The locked catalogue observes the condition by 7.4 and in all listed formal snapshots. The selected runtime covers one immediate named CHECK INSERT on 18.6 and 10.21. It does not cover partition routing, domain validation, validation-time errors, or the WITHOUT OVERLAPS path. The fixed DDL documentation describes CHECK as passing when its expression is true or NULL; it does not define a DEFERRABLE CHECK path.

Compare 23502 NOT NULL violation, 23P01 exclusion violation, and 23505 unique violation.

Sources