exclusion constraint violation
SQLSTATE 23P01 is a Postgres-specific error raised when an exclusion constraint is violated — that is, a row being inserted or updated conflicts with an existing row according to the exclusion constraint operators.
- 1Inserting or updating a row that overlaps with an existing row under an exclusion constraint (commonly time range or geometry overlap)
- 2A room booking, event scheduling, or other overlap-prevention constraint fires because the new row conflicts with an existing one
Booking that overlaps an existing reservation.
CREATE TABLE bookings ( room_id INT, during TSRANGE, EXCLUDE USING GIST (room_id WITH =, during WITH &&) ); INSERT INTO bookings VALUES (1, '[2024-01-01, 2024-01-05)'); INSERT INTO bookings VALUES (1, '[2024-01-03, 2024-01-08)'); -- overlaps
expected output
ERROR: conflicting key value violates exclusion constraint "bookings_room_id_during_excl"
Fix 1
Check for conflicts before inserting
WHEN When a user-facing error message should be shown for overlapping bookings.
SELECT COUNT(*) FROM bookings WHERE room_id = 1 AND during && '[2024-01-03, 2024-01-08)'::tsrange;
Why this works
Query for existing overlapping rows before attempting the insert, and show a user-friendly message if any are found.
Fix 2
Use SELECT FOR UPDATE to lock conflicting rows before inserting
WHEN In concurrent booking scenarios.
BEGIN; SELECT 1 FROM bookings WHERE room_id = 1 AND during && :new_range FOR UPDATE; -- if no rows: INSERT; else: raise conflict
Why this works
Locking overlapping rows prevents concurrent transactions from inserting conflicting bookings between the check and the insert.
Exclusion constraints and GIST-based exclusion introduced in Postgres 9.0.
Class 23 — Integrity Constraint Violation (Postgres-specific)
Postgres Exclusion Constraints ↗Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev