23P01ERRORTier 2 — Caution✅ HIGH confidence

exclusion constraint violation

Category: Integrity Constraint ViolationVersions: Postgres 9.0+

What this means

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.

Why it happens

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

How to reproduce

Booking that overlaps an existing reservation.

trigger — this will ERROR

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

ERROR: conflicting key value violates exclusion constraint "bookings_room_id_during_excl"

Fix 1: Check for conflicts before inserting

When a user-facing error message should be shown for overlapping bookings.

fix

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

In concurrent booking scenarios.

fix

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.

Version notes

Postgres 9.0+Exclusion constraints and GIST-based exclusion introduced in Postgres 9.0.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

🔧 Source ref: Class 23 — Integrity Constraint Violation (Postgres-specific)

📖 Further reading: Postgres Exclusion Constraints

Confidence assessment

✅ HIGH confidence

Postgres-specific exclusion constraint violation. Well-documented and stable since 9.0.