23514

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.

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.

Related

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

Sources

• src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
• src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
• src.execMain.18.6 (SHA-256 33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d)
• src.tablecmds.18.6 (SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9)
• src.execPartition.18.6 (SHA-256 97951428f673d4eb6dd23141817874c29640aaac0d6dc74015c993cbd0636dd6)
• src.typecmds.18.6 (SHA-256 60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb)
• src.execIndexing.18.6 (SHA-256 24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199)
• doc.ddl-constraints.18.6 (SHA-256 ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7) · official documentation

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·
• src/backend/executor/execMain.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/tablecmds.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• doc/src/sgml/ddl.sgml · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/executor/execPartition.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/typecmds.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/executor/execIndexing.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7

Message templates

new row for relation "%s" violates check constraint "%s"

Failing row contains %s.

no partition of relation "%s" found for row

Partition key of the failing row contains %s.

new row for relation "%s" violates partition constraint

Failing row contains %s.

column "%s" of table "%s" contains values that violate the new constraint

empty WITHOUT OVERLAPS value found in column "%s" in relation "%s"

Reproduction & repair cases

Recorded runtime evidence

{
  "rows": [
    [
      1,
      10
    ],
    [
      2,
      1
    ]
  ],
  "table": "items",
  "detail": "Failing row contains (2, -1).",
  "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
  "sqlstate": "23514",
  "constraint": "amount_positive",
  "status_after_error": "IDLE"
}

{
  "rows": [
    [
      1,
      10
    ],
    [
      2,
      1
    ]
  ],
  "table": "items",
  "detail": "Failing row contains (2, -1).",
  "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
  "sqlstate": "23514",
  "constraint": "amount_positive",
  "status_after_error": "IDLE"
}