PostgreSQL Range Types and Exclusion Constraints: Prevent Double-Booking at the Database Level

What if your database could refuse a double-booking before your application code even runs? Most developers reach for triggers, stored procedures, or application-layer checks to prevent overlapping reservations — but PostgreSQL has had a cleaner answer built into the type system for years. Range types model intervals of values as first-class citizens, and exclusion constraints turn those ranges into integrity guarantees that hold even under race conditions. If you are building anything that schedules time, space, or numeric intervals, these two features belong in your toolkit.

Built-in Range Types in PostgreSQL

PostgreSQL represents a range with inclusive/exclusive bounds using square and parenthesis notation: [ or ] for inclusive, ( or ) for exclusive. The default construction for integer and date ranges uses an inclusive lower bound and an exclusive upper bound — [lower, upper) — which matches how humans naturally think about calendar intervals (a week runs from Monday through Saturday night, not including the following Monday).

The six built-in range types cover the most common use cases:

• int4range — ranges over 32-bit integers
• int8range — ranges over 64-bit integers (bigint)
• numrange — ranges over arbitrary-precision numeric values
• tsrange — timestamp ranges without timezone
• tstzrange — timestamp ranges with timezone (use this for booking systems)
• daterange — ranges over calendar dates

-- Range type syntax: [lower, upper) = inclusive lower, exclusive upper
SELECT '[2026-01-01, 2026-12-31]'::daterange;
SELECT '[1, 10)'::int4range;       -- includes 1, excludes 10
SELECT '(1, 10]'::int4range;       -- excludes 1, includes 10
SELECT '[2026-03-01, 2026-03-31]'::tstzrange;

-- Test containment
SELECT '[2026-01-01, 2026-12-31]'::daterange @> '2026-06-15'::date;  -- TRUE
SELECT '[1,10)'::int4range @> 10;   -- FALSE (exclusive upper)
SELECT '[1,10]'::int4range @> 10;   -- TRUE

PostgreSQL normalizes discrete ranges automatically. '[1,10)'::int4range and '[1,9]'::int4range are stored identically because integers have no values between 9 and 10. Continuous types like numrange and tstzrange preserve the original bound type because values between any two points may exist.

Range Type Operators and Functions

Range types ship with a complete operator vocabulary. Unlike text pattern matching or JSON containment, these operators map directly to a GiST index — so your queries are both expressive and fast.

Core Operators

-- @>  Contains element or range
SELECT '[1,10]'::int4range @> 5;           -- true
SELECT '[1,10]'::int4range @> '[3,7]'::int4range;  -- true

-- &&  Overlaps
SELECT '[1,10]'::int4range && '[8,15]'::int4range;  -- true (overlap at 8-10)
SELECT '[1,10]'::int4range && '[11,20]'::int4range; -- false (adjacent, not overlapping)

-- -|- Adjacent
SELECT '[1,10]'::int4range -|- '[11,20]'::int4range;  -- true

-- +  Union
SELECT '[1,5]'::int4range + '[3,10]'::int4range;  -- [1,10]

-- *  Intersection
SELECT '[1,10]'::int4range * '[5,15]'::int4range;  -- [5,10]

-- -  Difference
SELECT '[1,10]'::int4range - '[5,15]'::int4range;  -- [1,5)

The overlap operator && is the workhorse of booking systems. Notice that adjacent ranges — where one ends exactly where the other begins — do not overlap. A guest checking out on March 15 and a guest checking in on March 15 use the same room on different days; the ranges [2026-03-10, 2026-03-15) and [2026-03-15, 2026-03-20) are adjacent, not overlapping.

Key Functions

SELECT lower('[1,10]'::int4range);   -- 1
SELECT upper('[1,10]'::int4range);   -- 10
SELECT isempty('empty'::int4range);  -- true
SELECT lower_inc('[1,10]'::int4range);  -- true (inclusive lower)
SELECT upper_inc('[1,10]'::int4range);  -- true (inclusive upper)

-- Build range from values
SELECT int4range(1, 10);           -- [1,10)
SELECT int4range(1, 10, '[]');     -- [1,10] (inclusive both)
SELECT tstzrange(now(), now() + interval '1 hour');

The constructor functions accept a third argument for the bound type string: '[]' for inclusive on both sides, '[)' for the default inclusive/exclusive, '(]' for exclusive/inclusive, and '()' for exclusive on both sides. Use these constructors rather than string casting when building ranges from dynamic values to avoid injection risks and to let PostgreSQL infer the correct type.

Multirange Types (PostgreSQL 14+)

A multirange represents a set of non-contiguous ranges as a single value. Every range type in PostgreSQL 14 and later automatically has a corresponding multirange type: int4multirange, int8multirange, nummultirange, datemultirange, tsmultirange, and tstzmultirange.

-- Multirange represents multiple non-contiguous ranges
SELECT '{[1,5], [8,12]}'::int4multirange;

-- Check if a value is in any of the ranges
SELECT '{[1,5], [8,12]}'::int4multirange @> 3;   -- true
SELECT '{[1,5], [8,12]}'::int4multirange @> 6;   -- false (gap between 5 and 8)

-- Aggregate ranges into a multirange
SELECT range_agg(booking_period)
FROM room_bookings
WHERE room_id = 101;
-- Returns all booked periods for room 101 as a multirange

The range_agg aggregate function is particularly useful for computing occupied intervals. Given a room's bookings, range_agg returns a multirange covering all booked periods. You can then subtract this from the full availability window to find open slots without writing complex self-join queries. The complement of a multirange — what's not booked — is computable with the - difference operator applied against the full period range.

Exclusion Constraints with GiST

An exclusion constraint generalizes the concept of a uniqueness constraint. Where a unique constraint says "no two rows may have the same value in this column," an exclusion constraint says "no two rows may satisfy this combination of operators across these columns simultaneously." For booking systems, the relevant condition is: no two rows for the same resource may have overlapping time ranges.

Room Booking System

A hotel room booking table is the canonical exclusion constraint example. Without the constraint, two concurrent inserts for the same room in overlapping periods will both succeed — a race condition that application-level checks cannot reliably prevent. With the exclusion constraint, PostgreSQL uses a GiST index to detect conflicts atomically.

-- Without exclusion constraint: double-booking is possible
-- With exclusion constraint: PostgreSQL enforces no overlapping bookings

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  id SERIAL PRIMARY KEY,
  room_id INT NOT NULL,
  guest_name TEXT NOT NULL,
  booking_period TSTZRANGE NOT NULL,

  -- GiST index required for exclusion constraint
  CONSTRAINT no_overlapping_bookings
    EXCLUDE USING GIST (
      room_id WITH =,           -- same room
      booking_period WITH &&    -- overlapping period
    )
);

-- Create GiST index (automatically created by EXCLUDE, but explicit for clarity)
CREATE INDEX idx_room_bookings_gist
ON room_bookings USING GIST (room_id, booking_period);

-- This insert succeeds
INSERT INTO room_bookings (room_id, guest_name, booking_period)
VALUES (101, 'Alice', '[2026-03-10, 2026-03-15)');

-- This insert fails: same room, overlapping period
INSERT INTO room_bookings (room_id, guest_name, booking_period)
VALUES (101, 'Bob', '[2026-03-12, 2026-03-17)');
-- ERROR: conflicting key value violates exclusion constraint "no_overlapping_bookings"

-- Adjacent bookings are allowed (check-out day = check-in day is fine)
INSERT INTO room_bookings (room_id, guest_name, booking_period)
VALUES (101, 'Bob', '[2026-03-15, 2026-03-20)');  -- succeeds

The exclusion constraint implicitly creates a GiST index on (room_id, booking_period). The explicit CREATE INDEX shown above is redundant but documents the index clearly in migrations. Note that the constraint only applies to non-null values by default — if booking_period or room_id is null, the row is not checked against others. Add NOT NULL constraints on both columns as shown above to close this gap.

Doctor Appointment Scheduling

The same pattern applies to any resource that can only serve one consumer at a time. A doctor's appointment calendar prevents double-booking a time slot, and the same index that enforces the constraint also speeds up availability queries.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  doctor_id INT NOT NULL,
  patient_id INT NOT NULL,
  slot TSTZRANGE NOT NULL,

  CONSTRAINT no_double_booking
    EXCLUDE USING GIST (doctor_id WITH =, slot WITH &&)
);

-- Find available slots
SELECT
  doctor_id,
  gs AS potential_slot
FROM generate_series(
  '2026-03-10 09:00'::timestamptz,
  '2026-03-10 17:00'::timestamptz,
  '30 minutes'::interval
) gs
CROSS JOIN (SELECT DISTINCT doctor_id FROM appointments) docs
WHERE NOT EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.doctor_id = docs.doctor_id
    AND a.slot && tstzrange(gs, gs + '30 minutes'::interval)
);

The availability query uses generate_series to produce candidate 30-minute slots across the working day, then filters out any slot that overlaps an existing appointment. The GiST index on (doctor_id, slot) makes the && check in the subquery efficient — rather than scanning all appointments, PostgreSQL uses the index to find only appointments for the given doctor that touch the candidate window.

Custom Range Types

When none of the built-in range types fit your domain, you can define your own with CREATE TYPE ... AS RANGE. The only requirement is that the subtype has a meaningful ordering. The optional subtype_diff function tells PostgreSQL how to compute the distance between two values, which improves GiST index quality and query planning accuracy.

-- Create custom range type for floats with 2-decimal precision
CREATE TYPE float_range AS RANGE (
  subtype = float8,
  subtype_diff = float8mi
);

-- Use it
SELECT '[1.5, 3.7]'::float_range @> 2.5::float8;  -- true

Custom range types automatically get a corresponding multirange type (PostgreSQL 14+) and a GiST operator class. The subtype_diff function (float8mi is the built-in double-precision subtraction function) significantly improves index selectivity estimates — without it, the planner cannot estimate how many rows fall within a range, which can lead to poor plan choices on large tables.

Performance: GiST Indexes for Ranges

Range operators are not supported by B-tree indexes. GiST (Generalized Search Tree) indexes are the standard choice; SP-GiST indexes support range types in some versions but offer fewer operator-class options. Understanding GiST characteristics avoids production surprises.

• Write cost: GiST indexes are more expensive to maintain than B-tree indexes. Batch inserts on heavily-indexed range columns benefit from disabling autovacuum temporarily or loading data before creating the index.
• Multicolumn GiST: The first column should be the most selective. Placing room_id before booking_period in the index definition lets PostgreSQL prune large portions of the index when filtering by room.
• Index-only scans: GiST does not support index-only scans in PostgreSQL. Queries will always hit the heap for non-indexed columns. Keep this in mind when profiling with EXPLAIN (ANALYZE, BUFFERS).
• BRIN is not an option: BRIN indexes work only on physically-correlated data and do not support range containment or overlap operators.

-- Check that the planner uses the GiST index for overlap queries
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM room_bookings
WHERE room_id = 101
  AND booking_period && '[2026-03-10, 2026-03-20)'::tstzrange;

-- Inspect index size
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'room_bookings';

Working with JusDB on PostgreSQL Schema Design

Range types and exclusion constraints are among the most underused features in PostgreSQL, yet they solve real production problems — double-bookings, scheduling conflicts, numeric interval overlaps — with less code and stronger correctness guarantees than the alternatives. Getting the schema right from the start avoids expensive retrofits when the application scales.

JusDB works with engineering teams on PostgreSQL schema design, index strategy, and constraint modeling for production systems. Whether you are designing a booking platform, a scheduling service, or a data pipeline that processes temporal intervals, we help you leverage PostgreSQL's type system to enforce invariants at the storage layer rather than patching them in application code.

If your team is building on PostgreSQL and wants to get the foundation right, get in touch or explore our PostgreSQL consulting services.

Related Posts

• PostgreSQL JSONB vs JSON: Performance and Indexing
• PostgreSQL Indexing Best Practices Guide
• PostgreSQL Multi-Tenant Schema Design