Range Types and Exclusion Constraints

Model time intervals natively and prevent overlapping bookings with a database-level exclusion constraint

The beer_db models tap assignments with a tstzrange column called active_period. Each row says "this beer was on tap number N at this brewery from time A to time B." The exclusion constraint on (brewery_id, tap_number, active_period WITH &&) means: no two rows for the same brewery and tap number can have overlapping active periods.\n\nWithout a range type you would store start_time and end_time as separate columns. You could check for overlaps in application code, but nothing stops a concurrent transaction from inserting a conflicting row between your check and your insert. The exclusion constraint enforces this atomically at the database level — the same way a UNIQUE constraint prevents duplicate values, but generalised to the overlap relationship.\n\nThe GiST index required by the exclusion constraint also makes overlap queries fast.

Range Types in PostgreSQL

PostgreSQL has built-in range types for common use cases:

| Type | Covers | |------|--------| | tstzrange | timestamptz interval | | tsrange | timestamp (no TZ) interval | | daterange | date interval | | numrange | numeric interval | | int4range | integer interval |

A range has a lower bound, an upper bound, and inclusivity flags. [2024-01-01, 2024-03-31] includes both endpoints; [2024-01-01, 2024-03-31) excludes the upper.

Constructing Ranges

SELECT tstzrange('2024-01-01', '2024-06-30', '[)');
-- [2024-01-01 00:00:00+00, 2024-06-30 00:00:00+00)

Range Operators

-- Find tap assignments active at a specific moment
SELECT tap_number, active_period
FROM tap_assignments
WHERE active_period @> '2022-06-01 12:00:00+00'::timestamptz;

-- Find tap assignments that overlap a given period
SELECT tap_number, active_period
FROM tap_assignments
WHERE active_period && tstzrange('2022-05-01', '2022-08-01');

-- Extract bounds
SELECT
    lower(active_period) AS starts_at,
    upper(active_period) AS ends_at,
    upper(active_period) - lower(active_period) AS duration
FROM tap_assignments
LIMIT 5;

Exclusion Constraints

An exclusion constraint is like a UNIQUE constraint generalised to any operator. Instead of "no two rows can have the same value", it says "no two rows can satisfy this condition pair".

-- Already exists in beer_db — shown for reference:
ALTER TABLE tap_assignments
    ADD CONSTRAINT no_double_tap
    EXCLUDE USING gist (
        brewery_id   WITH =,        -- same brewery
        tap_number   WITH =,        -- same tap
        active_period WITH &&       -- overlapping period
    );

Attempting to insert a conflicting row raises: ERROR: conflicting key value violates exclusion constraint "no_double_tap"

Range type (tstzrange)

A native PostgreSQL type that represents an interval between two values. It stores the lower bound, upper bound, and inclusivity (open or closed for each endpoint) as a single column value. Range types support overlap (&&), containment (@>), and adjacent range operators, and they are indexed with GiST — not B-tree.

Exclusion constraint

A constraint that prevents any two rows from satisfying a given operator combination. A UNIQUE constraint is a special case: no two rows can have equal values. EXCLUDE USING gist generalises this: no two rows can have overlapping ranges (or any other operator that defines a conflict). It requires a GiST index, which it creates automatically.

&& (overlaps) operator

Returns true if two ranges share any point in time (or any value, for numeric ranges). Two ranges overlap unless one ends before the other begins. This is the operator used in the exclusion constraint — it is the formal definition of "you cannot have this beer on this tap at the same time as another beer".

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. Range type exercises use the tap_assignments table with its active_period tstzrange column and no_double_tap exclusion constraint.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📅 Inspect the Range Column

Select a few rows from tap_assignments showing the tap_number, active_period, and the extracted lower and upper bounds. Use lower() and upper() to see the individual timestamps.

SELECT tap_number, active_period, lower(active_period) AS starts_at, upper(active_period) AS ends_at FROM tap_assignments LIMIT 5;

tap_number | active_period | starts_at | ends_at ------------+-----------------------------------------------------+------------------------+------------------------ 1 | ["2022-01-01 00:00:00+00","2022-04-01 00:00:00+00") | 2022-01-01 00:00:00+00 | 2022-04-01 00:00:00+00 1 | ["2022-03-02 00:00:00+00","2022-05-31 00:00:00+00") | 2022-03-02 00:00:00+00 | 2022-05-31 00:00:00+00 1 | ["2022-05-01 00:00:00+00","2022-07-30 00:00:00+00") | 2022-05-01 00:00:00+00 | 2022-07-30 00:00:00+00 1 | ["2022-06-30 00:00:00+00","2022-09-28 00:00:00+00") | 2022-06-30 00:00:00+00 | 2022-09-28 00:00:00+00 1 | ["2022-08-29 00:00:00+00","2022-11-27 00:00:00+00") | 2022-08-29 00:00:00+00 | 2022-11-27 00:00:00+00 (5 rows)

📍 Contains: Which Tap Was Active on a Specific Date?

Find all tap assignments that were active on 2022-06-15. Use the @> 'point in time' operator to test containment.

SELECT ta.tap_number, b.name AS beer_name, ta.active_period FROM tap_assignments ta JOIN beers b ON b.id = ta.beer_id WHERE ta.active_period @> '2022-06-15 00:00:00+00'::timestamptz ORDER BY ta.tap_number LIMIT 10;

🔁 Overlap: Which Assignments Span a Given Window?

Find all tap assignments that overlap the window from 2022-07-01 to 2022-09-30. Use the && operator — it returns true if the ranges share any point.

SELECT ta.tap_number, b.name AS beer_name, ta.active_period FROM tap_assignments ta JOIN beers b ON b.id = ta.beer_id WHERE ta.active_period && tstzrange('2022-07-01', '2022-09-30') ORDER BY ta.tap_number LIMIT 10;

🚫 Trigger the Exclusion Constraint

Try to insert a tap assignment that conflicts with an existing one — same brewery, same tap number, overlapping period. The database should reject it with an exclusion constraint error. The brewery and beer IDs are resolved with subqueries — no copy-pasting UUIDs needed.

INSERT INTO tap_assignments (brewery_id, beer_id, tap_number, active_period) VALUES ( (SELECT brewery_id FROM tap_assignments LIMIT 1), (SELECT id FROM beers LIMIT 1), (SELECT tap_number FROM tap_assignments LIMIT 1), tstzrange('2022-01-15', '2022-03-01') );

brewery_id | tap_number | active_period --------------------------------------+------------+----------------------------------------------------- 1978e4fb-d507-0a77-2b4e-54de10000be9 | 1 | ["2022-01-01 00:00:00+00","2022-04-01 00:00:00+00") (1 row) ERROR: conflicting key value violates exclusion constraint "no_double_tap" DETAIL: Key (brewery_id, tap_number, active_period)=(1978e4fb-d507-0a77-2b4e-54de10000be9, 1, ["2022-01-15 00:00:00+00","2022-03-01 00:00:00+00")) conflicts with existing key (brewery_id, tap_number, active_period)=(1978e4fb-d507-0a77-2b4e-54de10000be9, 1, ["2022-01-01 00:00:00+00","2022-04-01 00:00:00+00")).

Lab complete! You can now work with range types and exclusion constraints:\n\n\n tstzrange(a, b) : ✅ create a timestamp range\n col @> timestamp : ✅ contains a point in time\n col && range : ✅ overlaps a period\n lower() / upper() : ✅ extract bounds\n EXCLUDE USING gist : ✅ prevent overlaps at the DB level\n

Enable JavaScript to run the live terminal and track your progress.