How to Use Range Data Types in PostgreSQL

Anyone who’s built a booking system, a scheduling tool, or anything involving date ranges knows the pain of writing overlap-detection logic by hand — a pile of start_date <= X AND end_date >= Y conditions that are easy to get subtly wrong. PostgreSQL’s range types exist specifically to solve this class of problem properly, with dedicated operators for overlap, containment, and adjacency, plus real constraint support through exclusion constraints. This article covers everything you need to actually use them in production.

What Are Range Data Types?

A range type represents a span of values between a lower and upper bound, over some base type. Instead of tracking a start and an end as two separate columns, you store a single range value that understands its own boundaries and can be compared against other ranges using purpose-built logic.

PostgreSQL ships with several built-in range types:

You can also define your own custom range types over other base types if needed, using CREATE TYPE ... AS RANGE.

Basic Syntax

Range literals use bracket notation borrowed from mathematics: [ and ] mean inclusive, ( and ) mean exclusive.

SELECT '[3,7]'::int4range;    -- includes both 3 and 7
SELECT '(3,7)'::int4range;    -- excludes both 3 and 7
SELECT '[3,7)'::int4range;    -- includes 3, excludes 7

For discrete types like integers, PostgreSQL actually normalizes ranges to a canonical form on storage — [3,7) (inclusive-lower, exclusive-upper) is the canonical form for integer ranges regardless of how you wrote the literal:

SELECT '(3,7]'::int4range;
-- [4,8)   (normalized automatically)

This normalization is worth internalizing early, because it means comparing two integer ranges for exact equality is reliable even if they were entered with different bracket styles.

Creating a Table with Range Columns

CREATE TABLE room_bookings (
    booking_id serial PRIMARY KEY,
    room_id integer NOT NULL,
    during tstzrange NOT NULL
);

INSERT INTO room_bookings (room_id, during)
VALUES (101, '[2026-09-01 09:00, 2026-09-01 10:00)');

Using tstzrange with a half-open interval ([start, end)) is the standard, recommended pattern for time bookings — it means back-to-back bookings (one ending exactly when the next starts) don’t count as overlapping, which matches how people actually expect scheduling to work.

Range Operators

This is where range types genuinely pay for themselves. Instead of manual comparison logic, you get dedicated operators:

SELECT '[3,7)'::int4range @> 5;
-- true   (range contains element)

SELECT '[3,7)'::int4range @> '[4,6)'::int4range;
-- true   (range contains range)

SELECT '[3,7)'::int4range && '[5,10)'::int4range;
-- true   (ranges overlap)

SELECT '[3,7)'::int4range << '[10,15)'::int4range;
-- true   (strictly left of)

SELECT '[10,15)'::int4range >> '[3,7)'::int4range;
-- true   (strictly right of)

SELECT '[3,7)'::int4range -|- '[7,10)'::int4range;
-- true   (adjacent — ranges meet exactly at a boundary with no gap or overlap)

The overlap operator && is the one you’ll reach for constantly in real applications:

SELECT * FROM room_bookings
WHERE room_id = 101
  AND during && '[2026-09-01 09:30, 2026-09-01 10:30)'::tstzrange;

That single condition replaces what would otherwise be a multi-clause manual overlap check, and it’s far less prone to off-by-one boundary bugs.

Extracting Bounds

SELECT lower('[3,7)'::int4range);      -- 3
SELECT upper('[3,7)'::int4range);      -- 7
SELECT lower_inc('[3,7)'::int4range);  -- true (lower bound is inclusive)
SELECT upper_inc('[3,7)'::int4range);  -- false (upper bound is exclusive)
SELECT isempty('[3,3)'::int4range);    -- true (empty range)

Unbounded Ranges

Ranges can be open-ended on either side, representing “everything from X onward” or “everything up to X”:

SELECT '[5,)'::int4range;    -- 5 and everything above
SELECT '(,10)'::int4range;   -- everything below 10

SELECT '[2026-01-01,)'::daterange @> '2030-06-15'::date;
-- true

This is genuinely useful for representing things like “employment start date with no end date yet” without needing a nullable end-date column plus special-case logic everywhere you query it.

Combining Ranges: Union, Intersection, and Difference

SELECT '[3,7)'::int4range + '[5,10)'::int4range;
-- [3,10)   (union — only valid if ranges overlap or are adjacent)

SELECT '[3,7)'::int4range * '[5,10)'::int4range;
-- [5,7)    (intersection)

SELECT '[3,7)'::int4range - '[5,10)'::int4range;
-- [3,5)    (difference)

Note that union (+) will raise an error if the two ranges don’t overlap or touch, since the result wouldn’t be representable as a single contiguous range.

Preventing Overlaps with Exclusion Constraints

This is arguably the single most powerful practical feature enabled by range types: you can enforce, at the database level, that no two rows in a table have overlapping ranges — without triggers, without application-level locking tricks, and without race conditions.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE room_bookings
ADD CONSTRAINT no_overlapping_bookings
EXCLUDE USING gist (room_id WITH =, during WITH &&);

This constraint says: for any two rows, it’s not allowed for room_id to be equal AND during to overlap at the same time. Try to double-book a room:

INSERT INTO room_bookings (room_id, during)
VALUES (101, '[2026-09-01 09:30, 2026-09-01 10:30)');
-- ERROR:  conflicting key value violates exclusion constraint "no_overlapping_bookings"

This is far more reliable than application-level overlap checks, which are always vulnerable to race conditions under concurrent inserts unless you add explicit locking. The database enforces it atomically as part of the transaction.

You’ll need the btree_gist extension for exclusion constraints that mix an equality check (room_id WITH =) with a range check (during WITH &&), since it provides the GiST operator class support for the equality part.

Practical Use Cases

1. Booking and Scheduling Systems

Room bookings, appointment slots, equipment reservations — anywhere double-booking must be prevented, ranges plus exclusion constraints are the textbook solution.

2. Price or Rate Validity Periods

CREATE TABLE product_prices (
    product_id integer NOT NULL,
    price numeric(10,2) NOT NULL,
    valid_during daterange NOT NULL,
    EXCLUDE USING gist (product_id WITH =, valid_during WITH &&)
);

This guarantees a product never has two conflicting prices active on the same date — a surprisingly common real-world data integrity problem that’s trivial to solve this way.

3. Employment or Membership Periods

CREATE TABLE employment_periods (
    employee_id integer NOT NULL,
    period daterange NOT NULL,
    EXCLUDE USING gist (employee_id WITH =, period WITH &&)
);

4. Numeric Bucketing and Tiered Pricing

CREATE TABLE shipping_tiers (
    tier_name text,
    weight_range numrange
);

INSERT INTO shipping_tiers VALUES
('Light', '[0,2)'), ('Medium', '[2,10)'), ('Heavy', '[10,)');

SELECT tier_name FROM shipping_tiers WHERE weight_range @> 5.5;
-- Medium

Indexing Range Columns

For equality and basic operations, a B-tree index works. But for the operators that make ranges genuinely useful — @>, &&, <<, >> — you want a GiST index:

CREATE INDEX idx_room_bookings_during ON room_bookings USING gist (during);

This is also what powers exclusion constraints under the hood, since EXCLUDE USING gist builds exactly this kind of index automatically.

Troubleshooting Common Issues

Unexpected exact-equality failures between visually different ranges. Remember discrete-type ranges are normalized to [inclusive, exclusive) form on storage. '(3,7)'::int4range and '[4,6]'::int4range are actually the same stored value and will compare equal, even though they don’t look identical as literals.

Exclusion constraint errors on insert that seem to come from nowhere. This usually means an overlap really does exist — check with a manual && query before assuming it’s a false positive. It’s also worth double-checking whether you intended a half-open [start,end) range; using fully-inclusive [start,end] ranges for time-based bookings often causes back-to-back, non-conflicting bookings to be incorrectly flagged as overlapping.

“Operator does not exist” errors mixing range and non-range types. Range operators are type-specific — comparing an int4range against a plain integer works for @> (contains) but not for && (overlaps), since overlap requires two ranges. Wrap scalar values appropriately depending on which operator you’re using.

Missing btree_gist extension when creating exclusion constraints with equality checks. If your exclusion constraint mixes a WITH = clause on a scalar column with a WITH && clause on a range, you need btree_gist installed first, or Postgres will complain it can’t find an appropriate operator class.

Empty range surprises. '[5,5)'::int4range is empty, not a single-point range containing 5. If you need to represent a genuine single point, use an inclusive-inclusive range like [5,5], though be aware this won’t get canonicalized the same way for discrete types unless you’re careful about your comparisons.

Best Practices

• Prefer half-open ranges ([start, end)) for time-based scheduling — it correctly treats back-to-back periods as non-overlapping.
• Always add a GiST index if you’re filtering on &&, @>, <<, or >> — a B-tree index won’t be used for those operators.
• Use exclusion constraints instead of application-level overlap checks whenever overlap prevention is a real business rule — it’s atomic, race-condition-free, and self-documenting in the schema.
• Be explicit about bound inclusivity in your literals rather than relying on defaults, especially in application code generating range values dynamically.
• When designing a booking-style schema, decide upfront whether you need “gaps” between bookings and reflect that in whether ranges touch or must have space between them.

Multirange Types

PostgreSQL 14 introduced multirange types, which represent a collection of non-contiguous ranges as a single value — genuinely useful when a single logical entity has multiple separate valid periods rather than one continuous span.

CREATE TABLE employee_active_periods (
    employee_id integer PRIMARY KEY,
    active_periods datemultirange
);

INSERT INTO employee_active_periods (employee_id, active_periods)
VALUES (1, '{[2020-01-01,2021-06-01), [2022-03-01,)}');

SELECT active_periods @> '2022-06-01'::date
FROM employee_active_periods
WHERE employee_id = 1;
-- true

Multiranges support largely the same operators as regular ranges (@>, &&, containment and overlap logic), but represent gapped, non-contiguous periods honestly rather than forcing you to either store multiple separate rows or approximate a gapped history as one continuous range. This is a genuinely useful improvement for modeling things like “periods of active employment across multiple stints at the same company” or “windows during which a subscription was active, accounting for a pause and resume.”

Custom Range Types

Beyond the built-in range types, you can define your own range type over any base type that has a meaningful notion of ordering:

CREATE TYPE float8range AS RANGE (subtype = float8);

SELECT '[1.5, 3.2)'::float8range;

This is less commonly needed than the built-in types, but it’s available for cases where your domain has a custom base type (perhaps a custom numeric domain, or an enum with meaningful order) that would benefit from the same range-comparison machinery.

Aggregating Ranges

range_agg(), available alongside multirange support, lets you collapse a set of overlapping or adjacent ranges into their merged form directly in a query:

SELECT room_id, range_agg(during) AS combined_bookings
FROM room_bookings
GROUP BY room_id;

This is genuinely useful for reporting “total booked time” style queries, since overlapping or back-to-back individual bookings get merged into contiguous blocks automatically, rather than requiring you to write custom merge logic in application code.

A More Complete Booking System Example

Pulling several of the concepts in this article together into something closer to a real production pattern:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE meeting_rooms (
    room_id serial PRIMARY KEY,
    room_name text NOT NULL
);

CREATE TABLE bookings (
    booking_id serial PRIMARY KEY,
    room_id integer NOT NULL REFERENCES meeting_rooms(room_id),
    booked_by text NOT NULL,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE INDEX idx_bookings_during ON bookings USING gist (during);

-- Find all available 1-hour slots for a room on a given day by checking against existing bookings
CREATE OR REPLACE FUNCTION is_room_available(p_room_id integer, p_slot tstzrange)
RETURNS boolean AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1 FROM bookings
        WHERE room_id = p_room_id AND during && p_slot
    );
END;
$$ LANGUAGE plpgsql;

SELECT is_room_available(1, '[2026-09-01 14:00, 2026-09-01 15:00)'::tstzrange);

Even with this availability-checking function in place for a friendlier pre-check in application code, the exclusion constraint on the table remains the real source of correctness — it guarantees no double-booking can ever occur even under concurrent requests, while the function just provides a fast, convenient way to check availability before attempting an insert.

Range Types in Reporting Queries

Beyond overlap prevention, ranges are genuinely useful for reporting queries that need to reason about coverage and gaps — questions like “which days in this month had no active price” or “how much total time was this room actually booked.”

-- Find gaps in coverage for a given product's pricing history
WITH ordered AS (
    SELECT valid_during, LEAD(lower(valid_during)) OVER (ORDER BY lower(valid_during)) AS next_start
    FROM product_prices
    WHERE product_id = 42
)
SELECT upper(valid_during) AS gap_start, next_start AS gap_end
FROM ordered
WHERE upper(valid_during) < next_start;

This kind of gap analysis, while it still requires a bit of window-function work on top of ranges, is considerably more tractable with proper range columns and their bound-extraction functions (lower(), upper()) than trying to reconstruct the same logic from separate start/end date columns with manual comparison logic scattered throughout the query.

Choosing Bound Inclusivity Deliberately

A subtle but important design decision with range types is deciding, upfront, whether your bounds should be inclusive or exclusive, and being consistent about it across your schema. A few concrete guidelines that hold up well in practice:

• Time-based scheduling (bookings, shifts, appointments): use half-open [start, end) ranges. This means an appointment ending at 3:00 PM and another starting at 3:00 PM are correctly treated as non-overlapping.
• Inclusive date ranges for things like “valid from this date through this date” (a promotion, a membership tier): using fully inclusive [start_date, end_date] daterange values is often more intuitive for date-only data, since there’s no sub-day granularity to worry about — just be consistent about it, since daterange actually canonicalizes to a half-open form internally regardless of how you write the literal.
• Numeric buckets (pricing tiers, weight classes): half-open ranges avoid ambiguity about which bucket a boundary value belongs to — a weight of exactly 10 should unambiguously fall into either the “under 10” or “10 and above” bucket, never both or neither.

Getting this decision right early avoids a very specific, very annoying class of bug: two ranges that look like they shouldn’t overlap according to your business logic, but do (or don’t) according to the actual stored inclusivity, discovered only when a specific boundary value triggers unexpected behavior in production.

Quick Reference for Range Construction Functions

Beyond literal syntax, Postgres provides constructor functions that are often clearer in application code generating ranges dynamically from variables:

SELECT numrange(3, 7);              -- [3,7)  default bounds
SELECT numrange(3, 7, '[]');        -- [3,7]  explicit inclusive bounds
SELECT daterange('2026-01-01', '2026-12-31', '[]');
SELECT tstzrange(now(), now() + interval '1 hour');

Using these constructors instead of hand-built literal strings avoids a whole class of string-formatting bugs, especially when the bound values themselves come from user input or computed expressions rather than fixed literals.

Wrapping Up

Range types solve a category of problem — “does this span of values overlap or contain another span” — that developers have historically hand-rolled with brittle, boundary-bug-prone comparison logic. Between the built-in operators for overlap and containment and the ability to enforce non-overlap guarantees directly through exclusion constraints, PostgreSQL gives you a correctness guarantee that’s genuinely difficult to replicate reliably at the application layer. If your schema has a start_date/end_date pair anywhere, it’s worth asking whether a proper range column — and possibly an exclusion constraint — would serve you better.