23P01 — exclusion_violation

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

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

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

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

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

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

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

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

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