Range Types

A range type represents a span of values of some underlying type — all the integers between two numbers, all the timestamps between two points in time, all the dates in a booking. Instead of modeling a range as two separate columns (start_date, end_date) and writing overlap logic by hand every time you query it, PostgreSQL lets you store and query the range as a single, well-defined value.

Type	Range of
`INT4RANGE`	Integers
`INT8RANGE`	Bigints
`NUMRANGE`	Numeric (exact decimal) values
`TSRANGE`	Timestamps without timezone
`TSTZRANGE`	Timestamps with timezone
`DATERANGE`	Dates

Bounds: inclusive and exclusive

A range literal is written as a lower bound, a comma, and an upper bound, wrapped in brackets that control whether each end is inclusive or exclusive: [ and ] are inclusive, ( and ) are exclusive.

Range literals

SQL

SELECT '[3, 7]'::int4range;    -- 3 through 7, both included
SELECT '[3, 7)'::int4range;    -- 3 through 6 — 7 excluded
SELECT '[2026-08-01, 2026-08-05)'::daterange;  -- a 4-night stay, checkout day excluded

A practical example: reservations

Date ranges are a natural fit for modeling a booking or reservation's duration.

Storing a reservation as a range

SQL

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    room_id        INTEGER NOT NULL,
    stay           DATERANGE NOT NULL
);

INSERT INTO reservations (room_id, stay) VALUES
    (101, '[2026-08-01, 2026-08-05)'),
    (101, '[2026-08-10, 2026-08-12)');

Range operators

Operator	Meaning
`@>`	Contains — the range contains this element or sub-range
`&&`	Overlaps — the two ranges share at least one point
`<<`	Strictly left of
`>>`	Strictly right of

Checking for overlapping bookings

SQL

-- Does a specific date fall within any existing reservation for room 101?
SELECT * FROM reservations
WHERE room_id = 101
  AND stay @> DATE '2026-08-03';

-- Would a new booking from Aug 4 to Aug 8 overlap any existing reservation?
SELECT * FROM reservations
WHERE room_id = 101
  AND stay && '[2026-08-04, 2026-08-08)'::daterange;

reservation_id | room_id |          stay
---------------+---------+------------------------
             1 |     101 | [2026-08-01,2026-08-05)

That second query is exactly the check a booking system needs to run before confirming a new reservation — "does this proposed date range overlap anything already on the books for this room?" — expressed as a single, efficient condition instead of a hand-rolled start_date < ? AND end_date > ? comparison.

Note

Range types get even more powerful when paired with an **exclusion constraint** (`EXCLUDE USING gist (room_id WITH =, stay WITH &&)`), which tells PostgreSQL to reject any `INSERT` or `UPDATE` that would create an overlapping range for the same room at the database level — no application code required, and no race condition between a "check for overlap" query and the following insert. That's something that's notably awkward to enforce correctly in most other databases, which have no equivalent to range types or exclusion constraints and are left relying on application-level checks or manual locking.

Range types (INT4RANGE, NUMRANGE, TSRANGE, DATERANGE, and more) store a span of values as a single value.
Bounds use [ / ] for inclusive and ( / ) for exclusive.
@> tests whether a range contains a value or sub-range; && tests whether two ranges overlap.
A date range column is a natural, queryable way to model a reservation or booking window.
Combined with an exclusion constraint, range types let PostgreSQL enforce "no overlapping reservations" directly at the database level.