Exclusion Constraint Conflicts With Existing Rows

An exclusion constraint generalizes UNIQUE: instead of forbidding two rows that are equal, it forbids two rows that are 'true' together under a set of operators -- here equal room_id (=) and overlapping ranges (&&), backed by a gist index (btree_gist supplies gist support for the scalar room_id). Creating the constraint builds that index and checks every existing row against every other; if any pair satisfies all the operators it is a violation, so the create aborts with 23P01. The data predates the rule, so the overlaps are already there -- exactly like trying to add UNIQUE to a column that already has duplicates.

Free Tier -- Basic Approach

Count the overlapping pairs that block the constraint with a self-join on the same room and an overlapping range.

SELECT count(*) AS overlapping_pairs
FROM room_bookings a JOIN room_bookings b
  ON a.room_id = b.room_id AND a.id < b.id AND a.during && b.during;

overlapping_pairs
-------------------
                 5
(1 row)

SELECT count(*) AS overlapping_pairs FROM room_bookings a JOIN room_bookings b ON a.room_id = b.room_id AND a.id < b.id AND a.during && b.during. Five pairs overlap -- the exact number of conflicts you must resolve before the EXCLUDE constraint can be created.

Resolve the conflicts, then add the constraint. Here the overlapping additions are the later booking of each pair; deleting them leaves one clean booking per room so the ADD CONSTRAINT validates and succeeds.

DELETE FROM room_bookings WHERE id IN (
  SELECT b.id FROM room_bookings a JOIN room_bookings b
    ON a.room_id = b.room_id AND a.id < b.id AND a.during && b.during
);
ALTER TABLE room_bookings ADD CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&);

DELETE 5
ALTER TABLE

DELETE FROM room_bookings WHERE id IN (SELECT b.id FROM room_bookings a JOIN room_bookings b ON a.room_id = b.room_id AND a.id < b.id AND a.during && b.during) removes the 5 later bookings (DELETE 5). Then ALTER TABLE room_bookings ADD CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&) completes (ALTER TABLE) because no overlapping pair remains.

Confirm the constraint exists, an overlapping insert is rejected with 23P01, and a non-overlapping insert is accepted -- proving the rule blocks exactly the double-bookings and nothing else.

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'room_bookings'::regclass AND contype = 'x';

conname   | contype
------------+---------
 no_overlap | x
(1 row)

INSERT INTO room_bookings(room_id, during) VALUES (1, tsrange(TIMESTAMP '2025-01-01 02:30', TIMESTAMP '2025-01-01 03:30'));

ERROR:  23P01: conflicting key value violates exclusion constraint "no_overlap"
DETAIL:  Key (room_id, during)=(1, ["2025-01-01 02:30:00","2025-01-01 03:30:00")) conflicts with existing key (room_id, during)=(1, ["2025-01-01 02:00:00","2025-01-01 03:00:00")).
SCHEMA NAME:  public
TABLE NAME:  room_bookings
CONSTRAINT NAME:  no_overlap
LOCATION:  check_exclusion_or_unique_constraint, execIndexing.c:874

INSERT INTO room_bookings(room_id, during) VALUES (1, tsrange(TIMESTAMP '2025-06-01 00:00', TIMESTAMP '2025-06-01 01:00'));

INSERT 0 1

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'room_bookings'::regclass AND contype = 'x' shows no_overlap with contype x. Inserting an overlapping booking for room 1 fails with 'conflicting key value violates exclusion constraint', while a non-overlapping booking for room 1 succeeds (INSERT 0 1).

📦 Version Matrix (PG 14--18)

Try the create-over-conflicts and the resolve-then-create on PostgreSQL 14 through 18. Every version refuses the constraint while overlaps exist and accepts it once they are gone.

Frequently Asked Questions

References

• PostgreSQL: CREATE TABLE (EXCLUDE constraints)
• PostgreSQL: btree_gist
• PostgreSQL: Range Types (overlap operator &&)
• PostgreSQL: Error Codes (Class 23 -- Integrity Constraint Violation)