↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQLSTATE

23514 — check_violation

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

8.18.28.38.49.09.19.29.39.49.59.61011121314151617181920
aliases
Unknown
class name
Integrity Constraint Violation
condition name
check_violation
lang
en
sqlstate
23514

Definition PG 18

23514 — check_violation

At a glance {#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 {#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 {#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 {#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 {#messages}

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 {#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 {#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 {#related}

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

Sources {#sources}

Compare versions

SQLSTATE evidence and recorded runtime versions

cases · check_constraint_insert
{
  "assertions": [
    "SQLSTATE is 23514",
    "The named relation and constraint are identified",
    "A valid row can be inserted after the error"
  ],
  "case_id": "check_constraint_insert",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "The target table has a named CHECK constraint",
    "The inserted value fails that predicate"
  ],
  "repair": "Correct the value or deliberately revise the constraint after reviewing existing data; do not disable validation to hide a contract violation.",
  "trigger": "Insert a row whose value violates the CHECK expression.",
  "versions": [
    "10",
    "18"
  ]
}
claims · identity
{
  "claim_id": "identity",
  "limits": "It denotes a failed CHECK or related partition/default constraint path, not only a named table CHECK.",
  "method": "Read the fixed errcodes row.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "23514 is check_violation in Class 23."
}
claims · row-path
{
  "claim_id": "row-path",
  "limits": "A CHECK expression that evaluates to NULL is accepted by PostgreSQL; use NOT NULL when NULL itself is invalid.",
  "method": "Trace the executor report group and errtableconstraint metadata.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.execMain.18.6",
    "src.calls.REL_18_6"
  ],
  "statement": "The executor reports 23514 with the relation and failed CHECK name when a row evaluates to false."
}
claims · schema-path
{
  "claim_id": "schema-path",
  "limits": "Repairing data may require a migration or a different partition route; do not assume every 23514 is a duplicate or syntax problem.",
  "method": "Compare the fixed source call groups.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.tablecmds.18.6",
    "src.calls.REL_18_6",
    "src.execPartition.18.6",
    "src.typecmds.18.6",
    "src.execIndexing.18.6"
  ],
  "statement": "Constraint validation, partition routing, and domain checks can produce different 23514 templates."
}
claims · runtime
{
  "claim_id": "runtime",
  "limits": "The case does not cover deferred checks, partition routing, domain checks, or validation-time errors.",
  "method": "Read the selected summaries and raw diagnostics.",
  "position": 3,
  "runtime": [],
  "sources": [
    "runtime.23514-batch1-latest-20260909.latest",
    "runtime.23514-batch1-pg10-20260909.pg10"
  ],
  "statement": "The selected named CHECK case returned 23514 on PG18.6 and PG10.21, identified amount_positive/items, stayed IDLE, and committed a valid row."
}
messages · row
{
  "limits": "The failing-row detail is conditional and can be suppressed by context or configuration.",
  "message_id": "row",
  "path": "",
  "position": 0,
  "raw": {
    "detail_template": "Failing row contains %s.",
    "id": "row",
    "limits": "The failing-row detail is conditional and can be suppressed by context or configuration.",
    "primary_template": "new row for relation \"%s\" violates check constraint \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.execMain.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execMain.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "new row for relation \" \" violates check constraint \" \"",
      "position": 0,
      "role": "",
      "template": "new row for relation \"%s\" violates check constraint \"%s\""
    },
    {
      "kind": "detail",
      "literal": "Failing row contains .",
      "position": 1,
      "role": "",
      "template": "Failing row contains %s."
    }
  ]
}
messages · partition-no-route
{
  "limits": "The partition-key substitution is dynamically built; the path is not the named CHECK template.",
  "message_id": "partition-no-route",
  "path": "",
  "position": 1,
  "raw": {
    "detail_template": "Partition key of the failing row contains %s.",
    "id": "partition-no-route",
    "limits": "The partition-key substitution is dynamically built; the path is not the named CHECK template.",
    "primary_template": "no partition of relation \"%s\" found for row",
    "severity_source": "ERROR",
    "sources": [
      "src.execPartition.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execPartition.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "no partition of relation \" \" found for row",
      "position": 0,
      "role": "",
      "template": "no partition of relation \"%s\" found for row"
    },
    {
      "kind": "detail",
      "literal": "Partition key of the failing row contains .",
      "position": 1,
      "role": "",
      "template": "Partition key of the failing row contains %s."
    }
  ]
}
messages · partition-constraint
{
  "limits": "The relation and failing-row description are dynamic.",
  "message_id": "partition-constraint",
  "path": "",
  "position": 2,
  "raw": {
    "detail_template": "Failing row contains %s.",
    "id": "partition-constraint",
    "limits": "The relation and failing-row description are dynamic.",
    "primary_template": "new row for relation \"%s\" violates partition constraint",
    "severity_source": "ERROR",
    "sources": [
      "src.execMain.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execMain.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "new row for relation \" \" violates partition constraint",
      "position": 0,
      "role": "",
      "template": "new row for relation \"%s\" violates partition constraint"
    },
    {
      "kind": "detail",
      "literal": "Failing row contains .",
      "position": 1,
      "role": "",
      "template": "Failing row contains %s."
    }
  ]
}
messages · domain-validation
{
  "limits": "The column and table identify the existing domain use; this is validation of a new domain constraint.",
  "message_id": "domain-validation",
  "path": "",
  "position": 3,
  "raw": {
    "id": "domain-validation",
    "limits": "The column and table identify the existing domain use; this is validation of a new domain constraint.",
    "primary_template": "column \"%s\" of table \"%s\" contains values that violate the new constraint",
    "severity_source": "ERROR",
    "sources": [
      "src.typecmds.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.typecmds.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" of table \" \" contains values that violate the new constraint",
      "position": 0,
      "role": "",
      "template": "column \"%s\" of table \"%s\" contains values that violate the new constraint"
    }
  ]
}
messages · without-overlaps-empty
{
  "limits": "The column and relation are dynamic; this is the empty-value guard, distinct from a conflict with an existing key.",
  "message_id": "without-overlaps-empty",
  "path": "",
  "position": 4,
  "raw": {
    "id": "without-overlaps-empty",
    "limits": "The column and relation are dynamic; this is the empty-value guard, distinct from a conflict with an existing key.",
    "primary_template": "empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.execIndexing.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execIndexing.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "empty WITHOUT OVERLAPS value found in column \" \" in relation \" \"",
      "position": 0,
      "role": "",
      "template": "empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\""
    }
  ]
}
runtimes · runtime.23514-batch1-latest-20260909.latest · 18.6 (Homebrew) · passed
{
  "cases": [
    "check_constraint_insert"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23514/cases.json",
      "sha256": "86b43f2a10da9ff67b7b53b05fe3d16121b922f713be9f02f7c8caf558f73012"
    },
    "cases": [
      "check_constraint_insert"
    ],
    "id": "runtime.23514-batch1-latest-20260909.latest",
    "observed": {
      "constraint": "amount_positive",
      "detail": "Failing row contains (2, -1).",
      "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
      "rows": [
        [
          1,
          10
        ],
        [
          2,
          1
        ]
      ],
      "sqlstate": "23514",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23514-batch1-latest-20260909/latest/raw.jsonl",
    "raw_sha256": "11302e6a32ae75c2f4938099bcbcfeeb774ad58e3f404574c56b0c81ea828012",
    "run_id": "23514-batch1-latest-20260909",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/23514/snippets.json",
      "sha256": "0dcd48b5f9b3c4264c7843f77edae204d2a37b55a2b41bb2570febfdf6391d83"
    },
    "status": "passed",
    "summary": "verify/results/23514-batch1-latest-20260909/latest/summary.json",
    "summary_sha256": "ea15d7fcfc868444fd08a27f5a428e75f5d129e53732fa0ac19449bcc0e589a4",
    "target": "latest"
  },
  "run_id": "23514-batch1-latest-20260909",
  "runtime_id": "runtime.23514-batch1-latest-20260909.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}
runtimes · runtime.23514-batch1-pg10-20260909.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed
{
  "cases": [
    "check_constraint_insert"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23514/cases.json",
      "sha256": "86b43f2a10da9ff67b7b53b05fe3d16121b922f713be9f02f7c8caf558f73012"
    },
    "cases": [
      "check_constraint_insert"
    ],
    "id": "runtime.23514-batch1-pg10-20260909.pg10",
    "observed": {
      "constraint": "amount_positive",
      "detail": "Failing row contains (2, -1).",
      "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
      "rows": [
        [
          1,
          10
        ],
        [
          2,
          1
        ]
      ],
      "sqlstate": "23514",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23514-batch1-pg10-20260909/pg10/raw.jsonl",
    "raw_sha256": "e0a09ba6b64cc6ba16b2038fb8fee8541d55b047369068ccca8b2406c5700699",
    "run_id": "23514-batch1-pg10-20260909",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/23514/snippets.json",
      "sha256": "0dcd48b5f9b3c4264c7843f77edae204d2a37b55a2b41bb2570febfdf6391d83"
    },
    "status": "passed",
    "summary": "verify/results/23514-batch1-pg10-20260909/pg10/summary.json",
    "summary_sha256": "599d10014ead75a34e9a0f59a7485ba0cdbfc350fa01456201c8bda00b29aa10",
    "target": "pg10"
  },
  "run_id": "23514-batch1-pg10-20260909",
  "runtime_id": "runtime.23514-batch1-pg10-20260909.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}
sources · src.errcodes.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt"
}
sources · src.calls.REL_18_6
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}
sources · src.execMain.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execMain.c",
  "position": 2,
  "sha256": "33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d",
  "source_id": "src.execMain.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execMain.c#L2076-L2081"
}
sources · src.tablecmds.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/tablecmds.c",
  "position": 3,
  "sha256": "422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9",
  "source_id": "src.tablecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L6502-L6532"
}
sources · doc.ddl-constraints.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/ddl-constraints.html",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/ddl.sgml",
  "position": 4,
  "sha256": "ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7",
  "source_id": "doc.ddl-constraints.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/ddl.sgml"
}
sources · src.execPartition.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execPartition.c",
  "position": 5,
  "sha256": "97951428f673d4eb6dd23141817874c29640aaac0d6dc74015c993cbd0636dd6",
  "source_id": "src.execPartition.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execPartition.c#L328-L335"
}
sources · src.typecmds.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/typecmds.c",
  "position": 6,
  "sha256": "60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb",
  "source_id": "src.typecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/typecmds.c#L3288-L3293"
}
sources · src.execIndexing.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execIndexing.c",
  "position": 7,
  "sha256": "24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199",
  "source_id": "src.execIndexing.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execIndexing.c#L1176-L1181"
}

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:b011581c87322615fa999fcacb52ea1485b2352d3b24aca109a54334f3d12a9e

Text language: en · english-manuals:b011581c87322615fa999fcacb52ea1485b2352d3b24aca109a54334f3d12a9e