# 23P01 — exclusion_violation

> PostgreSQL SQLSTATE 23P01: exclusion_violation for conflicting exclusion-constraint operators.
---

# 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.

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `23P01` |
| Condition | `exclusion_violation` |
| Status | `active` |
| Known present by | `9.0.0` |
| 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_EXCLUSION_VIOLATION` |
| Aliases | `—` |

<!-- source facts: data/errcodes/23P01.json -->
<!-- END SQLSTATE FACTS -->

## 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](../data/cases/23p01.json):

<!-- BEGIN SQLSTATE SNIPPET: overlapping_range_exclusion -->
```sql
-- 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;
```
<!-- END SQLSTATE SNIPPET -->

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the checked case registry; see the [structured evidence](../data/evidence/23p01.json) and [case export](../data/cases/23p01.json).

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](../23505/), [23514 CHECK violation](../23514/), and [23001 RESTRICT violation](../23001/).

## Sources {#sources}

- [`src.errcodes.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt) (SHA-256 `6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba`)
- `src.calls.REL_18_6` (SHA-256 `9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf`)
- [`src.execIndexing.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execIndexing.c#L917-L937) (SHA-256 `24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199`)
- [`doc.rangetypes.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/rangetypes.sgml#L540-L573) (SHA-256 `cfeffb134d2acc2ec45141726583b041410665c4a2f8ecf66cd3f9484a7f0014`) · [official documentation](https://www.postgresql.org/docs/18/rangetypes.html#RANGETYPES-CONSTRAINT)