Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 90 ยท Data Types

Range and Multirange Types

Learn PostgreSQL range and multirange types: bounds, the overlap and containment operators, and how ranges power booking systems. Live SQLite overlap demo.

Most databases store an interval as two columns, starts_at and ends_at, and leave every query to remember the rules: is the end included, does a booking that ends at ten conflict with one that starts at ten, what does NULL mean? Every team writes the same overlap condition, and every team gets it wrong at least once.

PostgreSQL has a better answer: range types. A range is a single value that holds a lower bound, an upper bound, and whether each bound is included. The database knows how to compare, intersect and index ranges, and an exclusion constraint can even forbid overlapping rows. This lesson explains the types and operators, shows how the same logic looks with two plain columns (which runs live here), and previews exclusion constraints, which get their own lesson in Exclusion Constraints and Temporal Design.

What runs live. SQLite has no range types, so the PostgreSQL syntax below is shown in read-only blocks. The live examples build the same overlap logic from starts_at and ends_at columns, which is exactly what a range does for you internally.

The built-in range types

PostgreSQL ships six single ranges, one for each common base type:

Range type Element type Typical use
int4range, int8range integer, bigint version spans, ID blocks
numrange numeric price tiers, measurements
daterange date holidays, contract periods
tsrange timestamp local-time schedules
tstzrange timestamptz bookings, shifts, validity windows

A range literal is text in brackets: '[1,5)'. The first character is [ (lower bound included) or ( (excluded); the last is ] (upper included) or ) (excluded). You can also use the constructor with an optional third argument:

SQL
-- PostgreSQL (read-only)
SELECT int4range(1, 5);               -- [1,5)
SELECT int4range(1, 5, '[]');         -- [1,6)   normalized
SELECT numrange(1, 5, '[]');          -- [1,5]   kept as written
SELECT daterange('2026-03-01', '2026-03-05');  -- [2026-03-01,2026-03-05)
SELECT int4range(10, NULL);           -- [10,)   no upper limit
SELECT int4range(5, 5);               -- empty

Two details are worth remembering. A NULL bound means unbounded, so [10,) is "10 and everything above", which is handy for "valid until further notice". And ranges over discrete types (integer, date) are normalized to the [) form: [1,5] becomes [1,6) because 6 is the next integer. Continuous types such as numeric and timestamptz cannot do that, so they keep whatever you wrote.

A range is one value

Lower bound Where it starts.
Upper bound Where it stops.
Inclusive or not [ and ] include, ( and ) exclude.

Default is [) : start included, end excluded.

1 / 2

Operators you will use

The operators work the same way on every range type:

Operator Meaning Example (int4range) Result
@> contains a value or range '[1,10)' @> 5 true
<@ is contained by '[2,4)' <@ '[1,10)' true
&& overlaps '[1,5)' && '[5,9)' false
<<, >> strictly left of, right of '[1,5)' << '[7,9)' true
-|- adjacent '[1,5)' -|- '[5,9)' true
* intersection '[1,5)' * '[3,9)' [3,5)
+ union (must be contiguous) '[1,5)' + '[5,9)' [1,9)
- difference '[1,9)' - '[3,5)' error: would split in two

The adjacent example shows why half-open ranges are pleasant: [1,5) and [5,9) touch but do not overlap, so two back-to-back bookings are fine. The function side is small: lower(r), upper(r), isempty(r), lower_inc(r), upper_inc(r), and range_merge(a, b), which returns the smallest range covering both.

A range is also a sortable, indexable value. A GiST index (see GIN and GiST Indexes) makes @> and && queries fast, which is what keeps "which bookings touch this window?" cheap on millions of rows.

The overlap rule, written by hand

Everything && does can be written with two comparisons. For half-open intervals, two periods overlap when each one starts before the other ends:

TEXT
a.starts_at < b.ends_at  AND  b.starts_at < a.ends_at

That one line is worth learning, because it works on any database. Here it finds conflicting bookings in a table with two plain columns. Bookings 2 and 3 clash (11:00 to 11:30 is shared), while bookings 1 and 2 only touch at 10:00 and are accepted. Booking 4 is in another room, so it is never compared:

SQL Find overlapping bookings with start and end columns

Press Run (or Ctrl + Enter) to see the result here.

The a.id < b.id condition makes each pair appear once instead of twice. The CHECK (starts_at < ends_at) plays the role of PostgreSQL's rule that a range's lower bound cannot exceed its upper bound.

Availability queries

The most common range question is not "which rows overlap each other" but "is this window free?". You test a wanted window against the existing rows. With ranges the PostgreSQL version is one WHERE room = 'Aspen' AND during && tstzrange(...). With two columns it is the same overlap test inside EXISTS:

SQL Which rooms are free for a wanted window?

Press Run (or Ctrl + Enter) to see the result here.

Aspen and Birch are both busy at 09:45 to 10:15 (the 09:00 to 10:00 and 09:30 to 10:30 bookings overlap it), and Cedar has no bookings at all. The last query is the @> operator by hand: it returns the afternoon Aspen booking, which contains 13:30.

You can also check the bound logic on its own. This example uses half-open integer intervals [lo, hi) and prints both && (overlap) and @> (contains) for every pair. [1,5) and [5,9) do not overlap, [4,6) overlaps both, and [10,10) is empty so it is filtered out:

SQL Overlap and containment on [lo, hi) pairs

Press Run (or Ctrl + Enter) to see the result here.

Exclusion constraints: let the database forbid overlaps

A UNIQUE constraint says "no two rows may be equal". An exclusion constraint generalizes that to "no two rows may conflict by this operator". Combine it with && and you get a guarantee no application bug can break: two bookings for the same room can never overlap, even when two requests race.

SQL
-- PostgreSQL (read-only)
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets gist handle plain equality on text

CREATE TABLE bookings (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room   text      NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

INSERT INTO bookings (room, during) VALUES
  ('Aspen', '[2026-03-01 09:00+00, 2026-03-01 10:00+00)');

INSERT INTO bookings (room, during) VALUES
  ('Aspen', '[2026-03-01 09:30+00, 2026-03-01 10:30+00)');
-- ERROR:  conflicting key value violates exclusion constraint "bookings_room_during_excl"

The second insert fails because the room is equal (=) and the periods overlap (&&). Back-to-back bookings are accepted. The constraint works inside the index, so it is safe under concurrency, which a "check first, then insert" query in application code is not. The full story is in Exclusion Constraints and Temporal Design.

Multiranges (PostgreSQL 14 and later)

A single range cannot represent "open 9 to 12 and 13 to 17", because there is a gap. A multirange is an ordered set of non-overlapping ranges: int4multirange, datemultirange, tstzmultirange, and so on. Every range type has one, and PostgreSQL creates it automatically.

SQL
-- PostgreSQL (read-only)
SELECT int4multirange(int4range(1, 3), int4range(5, 8));   -- {[1,3),[5,8)}
SELECT '{[1,5),[3,9)}'::int4multirange;                    -- {[1,9)}   merged
SELECT range_agg(during) FROM bookings WHERE room = 'Aspen';  -- all booked spans, merged
SELECT '{[1,3),[5,8)}'::int4multirange @> 6;               -- true

range_agg is the useful one: it aggregates many ranges into one multirange and merges overlaps and touching neighbours, so "the total time this room is occupied" becomes a single value. unnest(multirange) turns it back into ordinary rows. Multiranges support the same operators as ranges. Their +, * and - never fail on a non-contiguous result, because a multirange is allowed to have gaps.

When to use a range

Use a range when the pair of values means one thing: a validity period, a booking, a price tier, a version window. Keep two columns when the endpoints have separate meanings, are queried independently, or must be exported to tools that do not know PostgreSQL types. Ranges are PostgreSQL-specific, so an ORM may need a custom type, and your migration to another database will need a rewrite.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does int4range(1, 5, '[]') produce in PostgreSQL?

    int4range is a discrete type, so PostgreSQL normalizes the range to the half-open form. Including 5 becomes excluding 6, giving [1,6).
  2. 2. Two bookings are [10:00, 11:00) and [11:00, 12:00). Does && report an overlap?

    With the default [) bounds the upper bound is excluded, so back-to-back ranges do not overlap. The adjacent operator -|- would return true.
  3. 3. Which condition tests that half-open periods a and b overlap?

    Each period must start before the other one ends. The third option only detects one period containing the other, and the last one is the condition for no overlap.
  4. 4. Why use an exclusion constraint instead of checking for overlaps in application code?

    A check-then-insert in application code can pass twice at the same moment. The constraint is enforced inside the index, so one of the two inserts fails.

Summary

  • A range type stores lower bound, upper bound and bound inclusion in one value; NULL bounds mean unbounded.
  • Discrete types (int4range, daterange) normalize to [); numrange, tsrange and tstzrange keep what you wrote.
  • Key operators: @>, <@, &&, <<, >>, -|-, *, +.
  • With half-open bounds, the overlap test is a.start < b.end AND b.start < a.end, and back-to-back periods do not clash.
  • EXCLUDE USING gist (room WITH =, during WITH &&) makes overlapping bookings impossible.
  • Multiranges (14+) hold several disjoint ranges; range_agg merges many ranges into one.

Next: Composite Types, Domains and Custom Types.