PostgreSQL 23P01: exclusion constraint violation
A row conflicts with an existing row under an EXCLUDE constraint, typically an overlapping time range for the same room, resource or person.
- SQLSTATE
23P01- Name
exclusion_violation- Category
- Integrity Constraint Violation
What it means
An exclusion constraint generalizes uniqueness: no two rows may satisfy all the listed comparisons at once. The classic use is EXCLUDE USING gist (room_id WITH =, during WITH &&), meaning no two bookings for the same room may have overlapping ranges.
23P01 means the new or updated row conflicts with the existing row shown in the DETAIL line.
Common causes
- A double booking: the new range overlaps an existing one for the same key.
- Ranges with inclusive upper bounds (
[a,b]) that touch:[10:00,11:00]and[11:00,12:00]overlap at 11:00. The default half-open form[a,b)avoids this. - Two concurrent transactions inserting overlapping ranges; the second fails when the first commits.
How to fix it
-
Find the conflicting rows before writing, or show the user which booking is in the way.
SELECT * FROM bookings WHERE room_id = 4 AND during && tstzrange('2026-05-01 10:00', '2026-05-01 11:00'); -
Use half-open ranges so back-to-back bookings don't overlap.
INSERT INTO bookings (room_id, during) VALUES (4, tstzrange('2026-05-01 11:00', '2026-05-01 12:00', '[)')); -
Skip conflicting rows with
ON CONFLICT DO NOTHING, which works with exclusion constraints (DO UPDATEdoes not).INSERT INTO bookings (room_id, during) VALUES (4, tstzrange('2026-05-01 10:00', '2026-05-01 11:00')) ON CONFLICT ON CONSTRAINT bookings_room_during_excl DO NOTHING;
Reproduce it safely
The btree_gist extension lets a GiST index compare plain integers with =.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TEMP TABLE b (room int, during tstzrange,
EXCLUDE USING gist (room WITH =, during WITH &&));
INSERT INTO b VALUES (1, tstzrange('2026-01-01 10:00', '2026-01-01 11:00'));
INSERT INTO b VALUES (1, tstzrange('2026-01-01 10:30', '2026-01-01 12:00')); -- ERROR: conflicting key value violates exclusion constraint
Related errors
23505duplicate key violates unique constraint23514check constraint violation42P10invalid column reference
More integrity constraint violation errors
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.