Skip to content
Tutorials Center
My learning

Topics

Lesson 83 of 90 ยท Projects

Project: Booking System with Ranges and Constraints

Build a meeting-room booking schema in PostgreSQL: tstzrange, a GiST exclusion constraint that forbids double bookings, availability queries and reports.

Double bookings are the classic bug of reservation software: two people click "book" for the same room at almost the same moment, both requests check that the room is free, and both succeed. In this project you build a small meeting-room booking system where that bug is not just unlikely but impossible, because the rule lives in the database. You will use range types and an exclusion constraint, then write the queries a real application needs: which rooms are free, what are the free gaps in a day, and how busy is each room.

You should have read Range and Multirange Types and Exclusion Constraints and Temporal Design first. This project puts them together.

What runs live. The PostgreSQL schema with tstzrange and the GiST exclusion constraint cannot run in SQLite, so it is shown read-only. Everything about the logic runs live: the overlap test, a trigger that rejects conflicting bookings, availability searches, free-gap computation and utilization reports, all written with starts_at and ends_at columns.

Requirements

Write the rules down before the schema. The system must:

  1. Hold a list of rooms, each with a capacity.
  2. Let people book a room for a time window.
  3. Never allow two active bookings for the same room to overlap. Back-to-back bookings (one ends at 10:00, the next starts at 10:00) are fine.
  4. Let people cancel a booking, after which the slot is free again, while the cancelled row stays for history.
  5. Answer: which rooms of at least N seats are free for a window? What are the free gaps in a room today? How many hours is each room booked?

The booking model

rooms Name and capacity.
bookings Room, window, who, status.
One rule Same room, overlapping window, both active: forbidden.
1 / 2

The PostgreSQL schema

Here is the real design. The window is one tstzrange column, so the database understands it as a single value. The default half-open bounds [) make back-to-back bookings legal.

SQL
-- PostgreSQL (read-only)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE rooms (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text    NOT NULL UNIQUE,
  capacity integer NOT NULL CHECK (capacity > 0)
);

CREATE TABLE bookings (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    bigint      NOT NULL REFERENCES rooms (id),
  booked_by  text        NOT NULL,
  during     tstzrange   NOT NULL,
  status     text        NOT NULL DEFAULT 'confirmed'
             CHECK (status IN ('confirmed', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now(),

  CHECK (NOT isempty(during)),
  CHECK (lower_inc(during) AND NOT upper_inc(during)),   -- insist on [)
  CHECK (lower(during) IS NOT NULL AND upper(during) IS NOT NULL),

  EXCLUDE USING gist (room_id WITH =, during WITH &&)
    WHERE (status = 'confirmed')
);

The interesting line is the last one. It reads: no two rows may have an equal room_id and overlapping during, but only among rows whose status is confirmed. The WHERE makes it a partial constraint, so cancelled bookings no longer block the slot. btree_gist is needed so a GiST index can handle the plain = on room_id next to the range operator. The three CHECK constraints close loopholes: empty ranges, ranges with the wrong bound style, and unbounded ranges that would block a room forever.

Making a booking is now a plain insert, and the database either accepts it or refuses it:

SQL
-- PostgreSQL (read-only)
INSERT INTO bookings (room_id, booked_by, during)
VALUES (1, 'maria', tstzrange('2026-03-02 09:00+00', '2026-03-02 10:00+00'));   -- ok

INSERT INTO bookings (room_id, booked_by, during)
VALUES (1, 'li', tstzrange('2026-03-02 09:30+00', '2026-03-02 10:30+00'));
-- ERROR:  conflicting key value violates exclusion constraint "bookings_room_id_during_excl"
-- SQLSTATE 23P01 (exclusion_violation)

INSERT INTO bookings (room_id, booked_by, during)
VALUES (1, 'li', tstzrange('2026-03-02 10:00+00', '2026-03-02 11:00+00'));      -- ok, back to back

Your application catches SQLSTATE 23P01 and tells the user "that slot was just taken". There is no check-then-insert race, because when two sessions insert conflicting rows at the same time, one of them waits for the other to commit, and then fails.

The same logic, live

SQLite has no ranges or exclusion constraints, but it has everything needed to model the rule. This example creates the schema with two columns, then simulates the app-side "check, then insert" pattern in a single statement: INSERT ... WHERE NOT EXISTS. Three requests arrive in order; the second overlaps the first and is silently not inserted, and the third is back to back with the first and is accepted:

SQL Booking requests with an overlap check

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

Only two rows exist: Maria's and the back-to-back one by Li. In PostgreSQL this pattern would be unsafe on its own: two sessions can run the NOT EXISTS at the same instant, both see no conflict, and both insert. SQLite serializes writers, which hides that problem here. That is exactly why the PostgreSQL design uses the exclusion constraint instead.

A trigger as a stand-in for the constraint

A BEFORE INSERT trigger can reject conflicting rows with an error, which is closer to what the exclusion constraint does. SQLite supports RAISE(ABORT, message) in triggers. The example below creates the trigger, adds valid rows (including two back-to-back bookings and a cancelled one that no longer blocks), and ends with an insert that conflicts. That last statement fails on purpose, so the run ends with the trigger's error message:

SQL A trigger that rejects double bookings

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

The first three inserts succeed (two confirmed bookings and one cancelled), and the last statement fails with the error room is already booked for that time. Notice how the WHEN NEW.status = 'confirmed' clause mirrors the WHERE (status = 'confirmed') of the partial exclusion constraint. A trigger written in PL/pgSQL would be equally correct only if it also took a lock, for example with pg_advisory_xact_lock(room_id), which is more code than the one-line constraint and still easier to get wrong.

Availability queries

The first question every booking page asks: which rooms with enough seats are free for this window? In PostgreSQL, with ranges:

SQL
-- PostgreSQL (read-only)
SELECT r.id, r.name, r.capacity
FROM rooms r
WHERE r.capacity >= 8
  AND NOT EXISTS (
    SELECT 1 FROM bookings b
    WHERE b.room_id = r.id
      AND b.status = 'confirmed'
      AND b.during && tstzrange('2026-03-02 09:30+00', '2026-03-02 10:00+00')
  )
ORDER BY r.capacity, r.name;

The GiST index created by the exclusion constraint also serves this query, because it covers room_id and during with the same operators. The live version is the same query with the overlap test written out. It also computes the free gaps of one room for the day, with a window function: each booking's prev_end is the previous booking's end (or the start of the workday for the first), and a gap exists wherever prev_end is earlier than the booking's start. The last gap, after the final booking, is added with a second SELECT:

SQL Free rooms for a window and free gaps in a day

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

Birch is busy (its confirmed booking overlaps the window) so only Dune qualifies; its 09:45 booking is cancelled and does not count. Aspen has four free gaps: 08:00 to 09:00, 10:00 to 11:00, 12:30 to 15:00 and 16:00 to 17:00. In PostgreSQL the same gap logic works unchanged with lower(during) and upper(during), or you can use range_agg and subtract the booked multirange from the workday range, which returns the gaps directly.

Reports: how busy is each room?

A report is just aggregation over durations. In PostgreSQL, upper(during) - lower(during) returns an interval, and sum() over intervals works. In SQLite, subtract Julian day numbers. This query lists every room, including rooms with no bookings, with the number of confirmed bookings and the hours booked:

SQL Hours booked per room

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

The status filter sits in the ON clause, not in WHERE, so rooms with no confirmed bookings still appear with zero hours. Putting it in WHERE would turn the left join into an inner join and silently drop Cedar. This is one of the most common reporting mistakes.

Hardening the design

Once the core works, a few details separate a demo from a system you can run:

  • Time zones. Store timestamptz, which PostgreSQL keeps in UTC. Add a timezone column to rooms (or buildings) and render times with AT TIME ZONE. Let the browser send an absolute instant, not "9 o'clock". See Dates, Times and Time Zones.
  • Slot granularity. If rooms are bookable in 15-minute steps, add a CHECK that both bounds are multiples of 15 minutes: CHECK (extract(minute FROM lower(during))::int % 15 = 0). The database then also refuses nonsense windows from buggy clients.
  • Recurring bookings. Do not store infinite series. Store a recurrence rule and generate concrete rows for a rolling window (say 90 days), so the exclusion constraint still guards every real occurrence.
  • Per-user limits. The constraint stops room conflicts, not "one person has ten rooms at once". Add a second exclusion constraint on (booked_by WITH =, during WITH &&) if a person cannot be in two places.
  • Cancellation. Change status rather than deleting. Because the exclusion constraint is partial on status = 'confirmed', the slot becomes free instantly.
  • Cleanup. Old rows can be archived by month with Declarative Partitioning. Note that unique and exclusion constraints on a partitioned table must include the partition key (exclusion constraints on partitioned tables need PostgreSQL 17 or later), so decide whether you need partitioning before you add it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is a check-then-insert query in application code not enough to prevent double bookings in PostgreSQL?

    Under concurrency both sessions can see an empty slot and both insert. An exclusion constraint is enforced inside the index, so the second conflicting insert waits and then fails.
  2. 2. What does the WHERE (status = 'confirmed') on the exclusion constraint achieve?

    The WHERE clause makes the constraint partial: rows that do not match it are ignored by the constraint, so a cancelled booking no longer blocks anyone.
  3. 3. Why does the report put b.status = 'confirmed' in the LEFT JOIN's ON clause instead of WHERE?

    A WHERE condition on the right-hand table removes the NULL rows produced by the outer join, turning it into an inner join. In the ON clause it only decides which rows match.
  4. 4. Which SQLSTATE should the application catch to show a friendly message for a double booking?

    A violated exclusion constraint raises 23P01. Unique violations are 23505, and 40001 belongs to serialization failures in strict isolation levels.

Summary

  • Model each booking window as one tstzrange with half-open bounds so back-to-back bookings are legal.
  • EXCLUDE USING gist (room_id WITH =, during WITH &&) WHERE (status = 'confirmed') makes overlapping confirmed bookings impossible, even under concurrency.
  • Use extra CHECK constraints to forbid empty, unbounded and wrongly bounded ranges.
  • Availability is NOT EXISTS (... during && wanted); free gaps come from LAG over sorted bookings or from range_agg.
  • Put outer-join filters in the ON clause so empty rooms stay in reports.
  • A trigger can imitate the rule, but only the constraint is atomic by itself.

Next: Project: Product Catalog with JSONB and Search.