23P01 — exclusion_violation

23P01 means an exclusion constraint found an existing row for which all configured comparison operators conflict. The selected range case uses &&, so it rejects an overlap rather than merely a duplicate value.

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

9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19 20

aliases: Unknown
class name: Integrity Constraint Violation
condition name: exclusion_violation
lang: en
sqlstate: 23P01

Definition PG 18

23P01 — exclusion_violation

At a glance {#at-a-glance}

23P01 means an exclusion constraint found an existing row for which all configured comparison operators conflict. The selected range case uses &&, so it rejects an overlap rather than merely a duplicate value.

Meaning {#meaning}

An exclusion constraint combines an index access method with operators such as range overlap. In the case registry, [5,12) conflicts with [1,10), while [10,12) is accepted because PostgreSQL’s half-open ranges do not overlap at 10. Operator classes and the configured operator combination determine the result; DEFERRABLE changes when the conflict is checked.

Diagnosis {#diagnosis}

Capture constraint_name, the key values in DETAIL, and whether the constraint is immediate or deferred. Inspect the existing rows with the same operator semantics; equality checks alone are insufficient. An immediate conflict is raised at the statement boundary, while a DEFERRABLE conflict can surface at SET CONSTRAINTS or COMMIT. The selected case is immediate and runs in autocommit mode, so its connection returns IDLE after the rejected insert.

Response {#response}

Choose a non-conflicting value or coordinate the conflicting booking/resource under the application’s concurrency policy. In autocommit, a failed statement ends its own transaction boundary and the connection can be reused. In an explicit transaction, an immediate error can leave the transaction aborted just as a deferred error can; issue ROLLBACK, or ROLLBACK TO SAVEPOINT when the operation was deliberately isolated in a savepoint, before retrying the complete operation. A DEFERRABLE conflict may be reported at SET CONSTRAINTS or COMMIT, so retry from the clean boundary required by that check point; retry only when the operation is safe and the conflict can actually resolve. Do not “fix” an overlap by changing range bounds without applying the business rule.

Observed diagnostics {#messages}

18.6 (Homebrew) / latest:SQLSTATE 23P01; primary conflicting key value violates exclusion constraint "bookings_no_overlap"; DETAIL Key (during)=([5,12)) conflicts with existing key (during)=([1,10)).; status_after_error IDLE. 10.21 (Debian 10.21-1.pgdg90+1) / pg10:SQLSTATE 23P01; primary conflicting key value violates exclusion constraint "bookings_no_overlap"; DETAIL Key (during)=([5,12)) conflicts with existing key (during)=([1,10)).; status_after_error IDLE.

Representative case {#case}

In this example, the second booking overlaps the existing range and is repaired by moving its lower bound to the existing upper bound. The complete setup, assertions, and cleanup are in the case export:

-- create
CREATE TABLE bookings(id integer PRIMARY KEY, during int4range NOT NULL, CONSTRAINT bookings_no_overlap EXCLUDE USING gist (during WITH &&));
-- seed
INSERT INTO bookings VALUES (1, int4range(1, 10));
-- trigger
INSERT INTO bookings VALUES (2, int4range(5, 12));
-- repair
INSERT INTO bookings VALUES (2, int4range(10, 12));
-- verify
SELECT id, during::text FROM bookings 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, mechanism, runtime. Selected runtime records: runtime.23P01-batch1-latest-20260909.latest, runtime.23P01-batch1-pg10-20260909.pg10.

Versions {#versions}

The locked catalogue observes 23P01 from the 9.0.0 boundary and in every listed formal snapshot. The selected immediate range case passes on 18.6 and 10.21; it does not test deferred or concurrent exclusion checks.

Related {#related}

Compare 23505 unique violation, 23514 CHECK violation, and 23001 RESTRICT violation.

Sources {#sources}

src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
src.execIndexing.18.6 (SHA-256 24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199)
doc.rangetypes.18.6 (SHA-256 cfeffb134d2acc2ec45141726583b041410665c4a2f8ecf66cd3f9484a7f0014) · official documentation

Compare versions

SQLSTATE evidence and recorded runtime versions

cases · overlapping_range_exclusion

{
  "assertions": [
    "SQLSTATE is 23P01",
    "The exclusion constraint is identified",
    "A non-overlapping range can be inserted afterward"
  ],
  "case_id": "overlapping_range_exclusion",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "A range column has a GiST exclusion constraint using \u0026\u0026",
    "The second range overlaps the committed first range"
  ],
  "repair": "Choose a non-overlapping interval or resolve the conflicting reservation according to business rules; do not treat exclusion as a generic duplicate-key check.",
  "trigger": "Insert an overlapping range.",
  "versions": [
    "10",
    "18"
  ]
}

claims · identity

{
  "claim_id": "identity",
  "limits": "The exclusion operator may compare more than equality; the operator class and constraint definition determine the conflict.",
  "method": "Read the fixed errcodes row.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "23P01 is exclusion_violation in Class 23."
}

claims · mechanism

{
  "claim_id": "mechanism",
  "limits": "A half-open range [10,12) does not overlap [1,10); changing bounds is a business rule, not a blanket retry.",
  "method": "Trace check_exclusion_or_unique_constraint and the range exclusion documentation.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.execIndexing.18.6",
    "doc.rangetypes.18.6",
    "src.calls.REL_18_6"
  ],
  "statement": "An exclusion constraint rejects a row when the configured operator comparisons are all true against an existing row; the range example uses \u0026\u0026 for overlap."
}

claims · runtime

{
  "claim_id": "runtime",
  "limits": "This case does not test DEFERRABLE exclusion constraints, concurrent waits, or every operator class.",
  "method": "Read the selected summaries and raw diagnostics.",
  "position": 2,
  "runtime": [],
  "sources": [
    "runtime.23P01-batch1-latest-20260909.latest",
    "runtime.23P01-batch1-pg10-20260909.pg10"
  ],
  "statement": "The selected immediate GiST range case returned 23P01 on PG18.6 and PG10.21, identified bookings_no_overlap, stayed IDLE, and committed a non-overlapping [10,12) range."
}

messages · conflict

{
  "limits": "The detail is dynamically built from the conflicting key values; the fallback is “Key conflicts with existing key.”",
  "message_id": "conflict",
  "path": "",
  "position": 0,
  "raw": {
    "detail_template": "Key %s conflicts with existing key %s.",
    "id": "conflict",
    "limits": "The detail is dynamically built from the conflicting key values; the fallback is “Key conflicts with existing key.”",
    "primary_template": "conflicting key value violates exclusion constraint \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.execIndexing.18.6"
    ],
    "sqlstate": "23P01"
  },
  "severity": "ERROR",
  "sources": [
    "src.execIndexing.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "conflicting key value violates exclusion constraint \" \"",
      "position": 0,
      "role": "",
      "template": "conflicting key value violates exclusion constraint \"%s\""
    },
    {
      "kind": "detail",
      "literal": "Key conflicts with existing key .",
      "position": 1,
      "role": "",
      "template": "Key %s conflicts with existing key %s."
    }
  ]
}

runtimes · runtime.23P01-batch1-latest-20260909.latest · 18.6 (Homebrew) · passed

{
  "cases": [
    "overlapping_range_exclusion"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23P01/cases.json",
      "sha256": "6dcb833ed64435a75016520f520cbb1abffc7d8751eb9195dc8ad5ffa2a9680e"
    },
    "cases": [
      "overlapping_range_exclusion"
    ],
    "id": "runtime.23P01-batch1-latest-20260909.latest",
    "observed": {
      "constraint": "bookings_no_overlap",
      "detail": "Key (during)=([5,12)) conflicts with existing key (during)=([1,10)).",
      "primary": "conflicting key value violates exclusion constraint \"bookings_no_overlap\"",
      "rows": [
        [
          1,
          "[1,10)"
        ],
        [
          2,
          "[10,12)"
        ]
      ],
      "sqlstate": "23P01",
      "status_after_error": "IDLE"
    },
    "raw": "verify/results/23P01-batch1-latest-20260909/latest/raw.jsonl",
    "raw_sha256": "b4e9ebe13743c12d84be1113ebdcec01c12ae79368e9b85b4ff1e01179dc7880",
    "run_id": "23P01-batch1-latest-20260909",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/23P01/snippets.json",
      "sha256": "475f58dda688a5864436d67e18f16abfbfae0b178acecaabe66ebaca7ab3c585"
    },
    "status": "passed",
    "summary": "verify/results/23P01-batch1-latest-20260909/latest/summary.json",
    "summary_sha256": "5e613eec8134801dfb3a603e7926418ee5d73b585546fd1aaa9004eeec553ef4",
    "target": "latest"
  },
  "run_id": "23P01-batch1-latest-20260909",
  "runtime_id": "runtime.23P01-batch1-latest-20260909.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}

runtimes · runtime.23P01-batch1-pg10-20260909.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed

{
  "cases": [
    "overlapping_range_exclusion"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23P01/cases.json",
      "sha256": "6dcb833ed64435a75016520f520cbb1abffc7d8751eb9195dc8ad5ffa2a9680e"
    },
    "cases": [
      "overlapping_range_exclusion"
    ],
    "id": "runtime.23P01-batch1-pg10-20260909.pg10",
    "observed": {
      "constraint": "bookings_no_overlap",
      "detail": "Key (during)=([5,12)) conflicts with existing key (during)=([1,10)).",
      "primary": "conflicting key value violates exclusion constraint \"bookings_no_overlap\"",
      "rows": [
        [
          1,
          "[1,10)"
        ],
        [
          2,
          "[10,12)"
        ]
      ],
      "sqlstate": "23P01",
      "status_after_error": "IDLE"
    },
    "raw": "verify/results/23P01-batch1-pg10-20260909/pg10/raw.jsonl",
    "raw_sha256": "1975e9348838d73f3433c2da02a3f3972dc3670e24bc89d73d631cd35b219165",
    "run_id": "23P01-batch1-pg10-20260909",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/23P01/snippets.json",
      "sha256": "475f58dda688a5864436d67e18f16abfbfae0b178acecaabe66ebaca7ab3c585"
    },
    "status": "passed",
    "summary": "verify/results/23P01-batch1-pg10-20260909/pg10/summary.json",
    "summary_sha256": "2b071d07ad5977b4fd9133f9815a92fc3fca27980659f26654b8dc3793abd767",
    "target": "pg10"
  },
  "run_id": "23P01-batch1-pg10-20260909",
  "runtime_id": "runtime.23P01-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.execIndexing.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execIndexing.c",
  "position": 2,
  "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#L917-L937"
}

sources · doc.rangetypes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/rangetypes.html#RANGETYPES-CONSTRAINT",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/rangetypes.sgml",
  "position": 3,
  "sha256": "cfeffb134d2acc2ec45141726583b041410665c4a2f8ecf66cd3f9484a7f0014",
  "source_id": "doc.rangetypes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/rangetypes.sgml#L540-L573"
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:8c95fb3325400efbfd8c98cece0651616cd3c09eb7bcdf360341f55f98d82f92

Text language: en · english-manuals:8c95fb3325400efbfd8c98cece0651616cd3c09eb7bcdf360341f55f98d82f92