SQLSTATE 23P01Severity lowLab verified

Incident brief

New row violates exclusion constraint

A table has an EXCLUDE constraint, and a new row conflicts with an existing row under the constraint's comparison — most commonly, two overlapping time ranges for the same resource.

Reproduced on PostgreSQL 16.14Verified 2026-07-15 (Docker lab, PostgreSQL 16.14)Reviewed by Verified against PostgreSQL 16.14 in an isolated lab environment

In 10 seconds

What: New row violates exclusion constraint
What triggers it: Create a table with EXCLUDE USING gist to prevent overlapping ranges for the same key.
The fix: Pick a range that does not overlap an existing one for the same key.
Proof: Reproduced on PostgreSQL 16.14 → The second reservation for room 1, overlapping the first by 30 minutes, was rejected with SQLSTATE 23P01 and only the original reservation remained.

The fix

What to do right now

The immediate, application-level response to this error.

Pick a range that does not overlap an existing one for the same key.
Query for conflicts (using the && operator) before inserting, if the application needs a friendlier error than a constraint violation.
Use EXCLUDE, not a plain UNIQUE constraint, whenever the rule is about overlap rather than exact duplicates.

Fix — SQL

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE booking.reservations (
  room_id integer,
  during tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- a non-overlapping range for the same room is accepted
INSERT INTO booking.reservations (room_id, during)
  VALUES (1, '[2026-01-01 11:00, 2026-01-01 12:00)');

Diagnose

See it live on the server

Run these against the affected instance to confirm the diagnosis before you act.

Standard triage — not specific to this error

These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.

This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.

What is running right now

Active backends, how long each has been running, and what it is waiting on.

SELECT pid,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS running_for,
       left(query, 80)     AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
  AND  pid <> pg_backend_pid()
ORDER  BY running_for DESC NULLS LAST;

Who is blocking whom

Turn raw blocking PIDs into the actual queries on both sides of the wait.

SELECT blocked.pid    AS blocked_pid,
       blocked.query  AS blocked_query,
       blocking.pid   AS blocking_pid,
       blocking.query AS blocking_query
FROM   pg_stat_activity AS blocked
JOIN   LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN   pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

Locks that are still waiting

Every lock a backend has requested but not yet been granted.

SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       COALESCE(c.relname, l.transactionid::text) AS object
FROM   pg_locks l
LEFT   JOIN pg_class c ON c.oid = l.relation
WHERE  NOT l.granted
ORDER  BY l.pid;

Reproduce & verify

A real, single-session PostgreSQL reproduction

A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.

1Create a table with EXCLUDE USING gist to prevent overlapping ranges for the same key.
2Insert a row, then try to insert a second row for the same key with an overlapping range.
3PostgreSQL rejects the second row with SQLSTATE 23P01.

One client: create a room-booking table with an EXCLUDE constraint, insert one reservation, then try to insert an overlapping one for the same room.

Schema setup

CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS booking.reservations;
CREATE TABLE booking.reservations (
  room_id integer,
  during tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
INSERT INTO booking.reservations (room_id, during) VALUES (1, '[2026-01-01 10:00, 2026-01-01 11:00)');

The insert that violates the exclusion constraint

-- same room, overlapping time range
INSERT INTO booking.reservations (room_id, during) VALUES (1, '[2026-01-01 10:30, 2026-01-01 11:30)');

Checking the table afterward

SELECT * FROM booking.reservations ORDER BY room_id, during;

What PostgreSQL actually returned

Schema setup output

NOTICE:  extension "btree_gist" already exists, skipping
CREATE EXTENSION
DROP TABLE
CREATE TABLE
INSERT 0 1

The insert that violates the exclusion constraint output

ERROR:  conflicting key value violates exclusion constraint "reservations_room_id_during_excl"
DETAIL:  Key (room_id, during)=(1, ["2026-01-01 10:30:00","2026-01-01 11:30:00")) conflicts with existing key (room_id, during)=(1, ["2026-01-01 10:00:00","2026-01-01 11:00:00")).

Checking the table afterward output

 room_id |                    during                     
---------+-----------------------------------------------
       1 | ["2026-01-01 10:00:00","2026-01-01 11:00:00")
(1 row)

Related & next steps

Follow the thread

Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.

Glossary

MVCC

Verification

PG 16.14

Last verified: 2026-07-15 (Docker lab, PostgreSQL 16.14)
Reviewed by: Verified against PostgreSQL 16.14 in an isolated lab environment
Audit status: reviewed