Range Types and Exclusion Constraints

PostgreSQL can store a range of values — like "from this date to that date" — as a single column value, instead of two separate start/end columns.

Range Types

Type	Description
int4range / int8range	Range of integers
numrange	Range of numeric values
daterange	Range of dates
tsrange / tstzrange	Range of timestamps (with/without timezone)

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER,
  during TSTZRANGE
);

INSERT INTO bookings (room_id, during)
VALUES (1, '[2024-06-01 09:00, 2024-06-01 10:00)');

[ / ] mean the bound is included; ( / ) mean it's excluded — the example above includes 9:00 but excludes 10:00, so a booking ending at 10:00 and one starting at 10:00 don't overlap.

Range Operators

SELECT * FROM bookings WHERE during @> '2024-06-01 09:30'::TIMESTAMPTZ;
-- bookings that CONTAIN this specific point in time

SELECT * FROM bookings
WHERE during && '[2024-06-01 09:30, 2024-06-01 09:45)'::TSTZRANGE;
-- bookings that OVERLAP with this time window

EXCLUDE Constraint — Preventing Overlaps at the Database Level

The real power of range types: you can make it impossible to insert two overlapping bookings for the same room, enforced by the database itself.

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER,
  during TSTZRANGE,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

This reads as: "no two rows may have the same room_id AND an overlapping during range." EXCLUDE constraints require the btree_gist extension for equality checks alongside a GiST index:

CREATE EXTENSION IF NOT EXISTS btree_gist;

INSERT INTO bookings (room_id, during) VALUES (1, '[2024-06-01 09:00, 2024-06-01 10:00)');
INSERT INTO bookings (room_id, during) VALUES (1, '[2024-06-01 09:30, 2024-06-01 10:30)');
-- ERROR: conflicting key value violates exclusion constraint
-- (this overlaps the first booking for the same room)

Common Errors & Troubleshooting

• ERROR: function gist_int4_ops does not exist (or similar) creating an EXCLUDE constraint — the btree_gist extension hasn't been enabled; run CREATE EXTENSION IF NOT EXISTS btree_gist; first.
• Two ranges that "shouldn't" overlap still conflicting — check your inclusive/exclusive bounds ([/)); a booking ending at 10:00 with an inclusive upper bound (] instead of )) DOES overlap one starting at exactly 10:00.
• ERROR: conflicting key value violates exclusion constraint — this is the constraint working as intended, not a bug — it means you tried to insert an overlapping range for the same key; adjust the requested range or reject the booking in your application logic.

Key Takeaways

• Range types store a lower and upper bound as one value, with inclusive/exclusive brackets controlling the edges.
• @> tests containment (of a point or another range); && tests overlap between two ranges.
• EXCLUDE USING GIST lets the database itself reject overlapping bookings/reservations — no application-level race condition possible.