Range Types in Postgres: The Built-in Solution to Overlap Problems

The most common shape I see hand-rolled in application code: "this thing is valid from time X to time Y." Two columns, valid_from and valid_to, with application logic to check overlaps and validity.

Postgres has a native type for this: range types. They store a from-to pair, support overlap queries directly, and combine with exclusion constraints to prevent invalid data at the schema level. Most teams I introduce them to wonder why they did not start with them.

The basics

CREATE TABLE bookings (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  resource_id BIGINT NOT NULL,
  during TSTZRANGE NOT NULL
);

INSERT INTO bookings (resource_id, during)
VALUES (1, tstzrange('2025-01-15 10:00', '2025-01-15 12:00'));

TSTZRANGE is a range over TIMESTAMPTZ. Other built-in ranges:

• INT4RANGE, INT8RANGE: integer ranges.
• NUMRANGE: numeric.
• DATERANGE: dates.
• TSRANGE: timestamps without timezone.

You can also create custom range types over any orderable type.

Overlap and containment

The key operators:

-- Overlap: do two ranges intersect?
SELECT * FROM bookings
WHERE during && tstzrange('2025-01-15 11:00', '2025-01-15 13:00');

-- Containment: is one range inside another?
SELECT * FROM bookings
WHERE during @> tstzrange('2025-01-15 10:30', '2025-01-15 11:30');

-- Adjacency
SELECT * FROM bookings
WHERE during -|- tstzrange('2025-01-15 12:00', '2025-01-15 14:00');

These are SQL-level — no application-side comparison logic needed.

Indexing for fast overlap queries

For overlap queries to be fast, use a GiST index:

CREATE INDEX bookings_during_gist ON bookings USING GIST (during);

Now WHERE during && '...' uses the index instead of seq-scanning.

Exclusion constraints: enforce no overlap

The killer feature. With the btree_gist extension and an EXCLUDE constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  resource_id BIGINT NOT NULL,
  during TSTZRANGE NOT NULL,
  EXCLUDE USING GIST (resource_id WITH =, during WITH &&)
);

No two bookings of the same resource can have overlapping ranges. Postgres enforces this. An INSERT of an overlapping booking fails with a constraint error.

The alternative — application-level overlap detection with locking — is harder to write correctly and has races. The exclusion constraint has none of that.

Inclusive vs exclusive bounds

Ranges have inclusive or exclusive bounds:

-- '[)' is the default: inclusive lower, exclusive upper
tstzrange('2025-01-15 10:00', '2025-01-15 12:00')
-- This range includes 10:00 but not 12:00

-- Specify explicitly
tstzrange('2025-01-15 10:00', '2025-01-15 12:00', '[]')  -- both inclusive
tstzrange('2025-01-15 10:00', '2025-01-15 12:00', '()')  -- both exclusive

For most time-range data, the [) default is right. Two bookings can be "adjacent" — one ends at 12:00, the next starts at 12:00 — without overlapping.

Unbounded ranges

A range with NULL on one side is unbounded:

-- All time after 2025-01-15
tstzrange('2025-01-15', NULL)

-- All time before 2025-01-15
tstzrange(NULL, '2025-01-15')

Useful for "valid from X onward" or "forever" semantics.

A worked example: scheduling

A team I worked with had a calendar booking system with race-condition bugs. Two users could double-book a meeting room because the application's overlap check was not transactional.

The fix:

CREATE TABLE meeting_room_bookings (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id BIGINT NOT NULL,
  during TSTZRANGE NOT NULL,
  user_id BIGINT NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

The race conditions disappeared because the database now enforces the rule. Two concurrent INSERTs that would overlap: one succeeds, the other gets a clean constraint error to handle.

The application code dropped from a hundred lines of overlap-checking-with-locks to twenty lines of "INSERT and handle the constraint error."

A worked example: price tiers

Versioned pricing where each price has a validity range:

CREATE TABLE product_prices (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT NOT NULL,
  price_cents INTEGER NOT NULL,
  valid_during TSTZRANGE NOT NULL,
  EXCLUDE USING GIST (product_id WITH =, valid_during WITH &&)
);

-- Insert today's price (valid forever until superseded)
INSERT INTO product_prices (product_id, price_cents, valid_during)
VALUES (1, 1000, tstzrange('2025-01-15', NULL));

-- Tomorrow, set a new price
UPDATE product_prices
SET valid_during = tstzrange(lower(valid_during), '2025-01-16')
WHERE product_id = 1 AND upper_inf(valid_during);

INSERT INTO product_prices (product_id, price_cents, valid_during)
VALUES (1, 1100, tstzrange('2025-01-16', NULL));

The EXCLUDE constraint guarantees no overlapping prices. Querying "price at time T":

SELECT price_cents
FROM product_prices
WHERE product_id = 1
  AND valid_during @> $1::timestamptz;

The @> operator (containment) finds the row whose validity range contains the given timestamp.

When ranges are wrong

Ranges are not always the right shape:

• Discrete events ("this happened at this moment") — just a timestamp.
• Aggregates over windows ("events in the last hour") — a query, not a range column.
• Open-ended user-defined intervals ("sometime tomorrow") — these need different modeling.

For data that is naturally a from-to pair where overlaps matter, ranges are excellent. For everything else, simpler types are simpler.

What I tell teams

When reviewing a schema with valid_from and valid_to columns:

• Convert to a range type. Cleaner. Indexable. Constrainable.
• Use EXCLUDE constraints to prevent overlap if overlaps would be invalid.
• Use the GiST index for query performance.

This is rarely a major refactor. It is usually a few lines of schema change and some application updates. The benefits — race-condition-free overlap checks, simpler queries — are immediate.