Range Types — Overlap Detection with daterange, tsrange & int4range

Range types model an interval between two values. PostgreSQL ships built-in range types for integers, numerics, dates, and timestamps. A single && operator replaces six lines of >= / <= / IS NULL comparisons — and lets you enforce no-overlap at the constraint level.

Built-in Range Types

Brackets: [ = inclusive, ) = exclusive — matching mathematical interval notation.

-- btree_gist is required for GiST exclusion on TEXT columns
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Setup: a room booking table with a range-type booked_for
CREATE TABLE IF NOT EXISTS room_bookings_ts (
    id          BIGSERIAL PRIMARY KEY,
    room        TEXT NOT NULL,
    guest       TEXT,
    booked_for  daterange NOT NULL,
    rate_cents  INT,
    -- Enforce: no two bookings for the same room can overlap
    EXCLUDE USING gist (room WITH =, booked_for WITH &&)
);

CREATE INDEX IF NOT EXISTS idx_room_bookings_ts_range
    ON room_bookings_ts USING gist (room, booked_for);

-- Seed some bookings
INSERT INTO room_bookings_ts (room, guest, booked_for, rate_cents)
VALUES
    ('101', 'Alice',   daterange('2024-03-01','2024-03-05'), 12000),
    ('101', 'Bob',     daterange('2024-03-07','2024-03-10'), 12000),
    ('102', 'Carol',   daterange('2024-03-01','2024-03-15'), 15000),
    ('103', 'Dave',    daterange('2024-03-10','2024-03-20'), 18000),
    ('101', 'Eve',     daterange('2024-03-20','2024-03-25'), 12000)
ON CONFLICT DO NOTHING;

Core Range Operators

-- && = overlaps
-- @> = contains  (left contains right)
-- <@ = contained by
-- -|- = adjacent
-- + = union,  * = intersection,  - = difference

-- Which bookings overlap a requested booked_for?
SELECT id, room, booked_for
FROM room_bookings_ts
WHERE room = '101'
  AND booked_for && daterange('2024-03-04', '2024-03-08');

-- Availability check: is room 2 free for a given booked_for?
SELECT NOT EXISTS (
    SELECT 1 FROM room_bookings_ts
    WHERE room = '102'
      AND booked_for && daterange('2024-03-16', '2024-03-20')
) AS is_available;

-- Find all rooms available for an entire booked_for
SELECT DISTINCT room
FROM room_bookings_ts
WHERE room NOT IN (
    SELECT room FROM room_bookings_ts
    WHERE booked_for && daterange('2024-03-05', '2024-03-12')
);