SQL / CAPSTONE PROJECTS
Project: a booking system with constraints that hold
Build a bookings schema where the database itself refuses double bookings, using a range column, an exclusion constraint, and CHECKs on degenerate ranges.
What you will learn
- Store a stay as one daterange column so the half-open [) boundary is part of the value
- Forbid overlaps with EXCLUDE USING gist (room_id WITH =, stay WITH &&)
- Add CHECKs for empty and unbounded ranges; each defeats overlap checking differently
- Turn a lost race into zero rows with INSERT ... ON CONFLICT DO NOTHING RETURNING
Understanding Project: a booking system with constraints that hold
The rule a booking system lives or dies by, that no room is held by two guests at the same time, is not a statement about one row. It is a statement about every pair of rows, which is why the constraints you reach for first cannot express it: CHECK sees a single row, and UNIQUE only knows how to compare values for equality. An exclusion constraint is UNIQUE generalised to any operator, so EXCLUDE USING gist (room_id WITH =, stay WITH &&) reads as "no two rows may share a room_id and have overlapping stays", and Postgres enforces it with a GiST index the same way it enforces uniqueness with a B-tree.
Try to keep that rule in the application instead and you find the reason it belongs in the schema. The usual code checks availability with a SELECT and then inserts, but under READ COMMITTED a plain SELECT takes no lock on rows that do not exist yet, so two requests arriving together both see a free room and both insert. The exclusion constraint closes that window because the check happens inside the index insertion: the second transaction meets the first one's index entry, waits for its outcome, then fails with SQLSTATE 23P01. The guarantee comes from the index, not from your code remembering to look first.
Two range details decide whether the constraint matches how hotels actually work. daterange is a discrete type, so every value is canonicalised to [), which is what makes Ada's checkout day the same day Grace may check in; with inclusive upper bounds those two stays would be judged overlapping and one guest would be turned away. Timestamp ranges are not canonicalised, so hourly slots must be constructed as tstzrange(start, stop, '[)') or back-to-back appointments get rejected. The degenerate values matter too: an empty range overlaps nothing, so a zero-night booking sails past &&, and an unbounded range overlaps everything, so a single row with no upper bound takes the room off the market forever, which is what the two CHECKs are for.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE rooms (
room_id int PRIMARY KEY,
label text NOT NULL
);
CREATE TABLE bookings (
booking_id int PRIMARY KEY,
room_id int NOT NULL REFERENCES rooms,
guest text NOT NULL,
stay daterange NOT NULL,
CONSTRAINT stay_has_nights CHECK (NOT isempty(stay)),
CONSTRAINT stay_bounded CHECK (lower(stay) IS NOT NULL AND upper(stay) IS NOT NULL),
CONSTRAINT no_double_booking
EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
INSERT INTO rooms VALUES (1, '101'), (2, '102');
-- Ada leaves on the 5th and Grace arrives on the 5th: adjacent, not overlapping.
INSERT INTO bookings VALUES
(1, 1, 'Ada', daterange('2026-03-01', '2026-03-05')),
(2, 1, 'Grace', daterange('2026-03-05', '2026-03-08')),
(3, 2, 'Linus', daterange('2026-03-02', '2026-03-06'));
-- Two nights inside Ada's stay: refused by the schema, not by application code.
INSERT INTO bookings VALUES
(4, 1, 'Ken', daterange('2026-03-03', '2026-03-05'));An overlap rule constrains pairs of rows, so it belongs in an exclusion constraint over a range column rather than in a check-then-insert application path.
Worked examples
Cancelled bookings must stop blocking the calendar
A predicate on the exclusion constraint applies the no-overlap rule only to rows that are still active.
CREATE TABLE stays (
room_id int NOT NULL,
nights daterange NOT NULL,
status text NOT NULL CHECK (status IN ('booked', 'cancelled')),
CONSTRAINT one_booking_per_room_per_night
EXCLUDE USING gist (room_id WITH =, nights WITH &&) WHERE (status = 'booked')
);
INSERT INTO stays VALUES (1, daterange('2026-05-01', '2026-05-04'), 'booked');
INSERT INTO stays VALUES (1, daterange('2026-05-02', '2026-05-06'), 'booked');
UPDATE stays SET status = 'cancelled'
WHERE nights = daterange('2026-05-01', '2026-05-04');
INSERT INTO stays VALUES (1, daterange('2026-05-02', '2026-05-06'), 'booked');Example explained
Line 1WHERE (status = 'booked') makes the constraint's GiST index partial, so cancelled rows are simply not in it and cannot conflict with anything.
Line 2The second INSERT is refused while the first stay is active, and the DETAIL line names both keys, which is enough to build a "those nights are taken" message.
Line 3After the UPDATE the first row drops out of the index, so the identical INSERT now succeeds without deleting any history.
Line 4The audit trail and the calendar rule coexist in one table because the predicate, not the application, decides which rows the rule covers.
Booking without a pre-flight check
ON CONFLICT DO NOTHING turns a rejected overlap into an empty result set instead of an error the client must catch.
CREATE TABLE slots (
room_id int NOT NULL,
stay daterange NOT NULL,
CONSTRAINT slots_no_overlap EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
INSERT INTO slots VALUES (7, daterange('2026-07-10', '2026-07-12'));
INSERT INTO slots VALUES (7, daterange('2026-07-11', '2026-07-13'))
ON CONFLICT DO NOTHING
RETURNING room_id, stay;Example explained
Line 1ON CONFLICT DO NOTHING without a conflict target covers exclusion constraints as well as unique ones, so the overlap becomes a skipped row rather than SQLSTATE 23P01.
Line 2RETURNING reports only rows that were really written, so an empty result is the signal that the slot was already taken: one round trip, no error handling.
Line 3The INSERT 0 0 tag confirms the row was dropped rather than stored.
Line 4DO UPDATE is not available here, because an exclusion constraint cannot act as an ON CONFLICT arbiter.
The availability query uses the same operator
Searching for free rooms with && guarantees the search agrees with what the constraint will allow, continuing from the rooms and bookings above.
SELECT r.room_id
FROM rooms r
WHERE NOT EXISTS (
SELECT 1
FROM bookings b
WHERE b.room_id = r.room_id
AND b.stay && daterange('2026-03-06', '2026-03-08')
)
ORDER BY r.room_id;Example explained
Line 1The search predicate is the same && used inside the constraint, so "looks free" and "is legal to insert" can never disagree.
Line 2room_id 1 is excluded because Grace's [2026-03-05,2026-03-08) overlaps the requested window.
Line 3room_id 2 survives because Linus's stay ends exclusively on 2026-03-06, the day the request starts.
Line 4NOT EXISTS over (room_id, stay) can use the GiST index the constraint already built, so no extra index is needed for the search.
Important notes
room_id WITH = needs the btree_gist extension, because plain GiST has no equality operator class for integers. Where extensions are unavailable, write that element as (int4range(room_id, room_id, '[]')) WITH && and rely on the built-in range opclass.
MySQL and SQLite have no exclusion constraints; the portable equivalent is one row per bookable unit, such as a row per room and night with UNIQUE (room_id, night), which buys the same guarantee from an ordinary unique index at the cost of more rows.
Common mistakes
Keeping start_date and end_date as two columns and testing overlap with new_start BETWEEN b.start_date AND b.end_date: that misses an existing booking sitting entirely inside the new one and treats the turnover day as occupied, so you refuse valid bookings and still accept double bookings.
Trusting a SELECT that found no conflict and inserting in a separate statement: it passes every single-user test and double-books as soon as two requests overlap in time, because nothing locks the empty space between the two statements.
Writing CHECK (lower(stay) >= current_date) to forbid bookings in the past: the expression is evaluated again whenever the data is revalidated, so restoring last month's dump fails on rows that were perfectly legal when they were inserted. Enforce that in a BEFORE INSERT trigger instead.
Try it yourself
Change, predict, then run
Take the schema from the main example and add a second exclusion constraint so one guest cannot hold overlapping bookings in two different rooms, then insert Ada into room 2 for 2026-03-02 to 2026-03-04 and check which constraint name appears in the error.
Open the SQL workspaceCheck your understanding
A service checks availability with SELECT ... WHERE room_id = 1 AND stay && $1 and inserts only when that query returns no rows. The table has no exclusion constraint, isolation is the default READ COMMITTED, and two requests for the same room and dates arrive at the same instant. What happens?
- Both SELECTs find nothing to conflict with and both INSERTs succeed, because a SELECT takes no lock on rows that do not exist yet
- The second SELECT blocks until the first transaction commits, so only one booking is created
- The second transaction is aborted with a serialization failure once Postgres notices the conflicting write
- Both rows are inserted, and the later one is discarded at commit time
Show answer
The first transaction leaves nothing for the second one to see or wait on: the row it is about to write does not exist yet, and each SELECT reads its own snapshot, so both report the room free. Option 3 describes SERIALIZABLE, which would detect the conflict, or the exclusion constraint, which would raise 23P01, but neither is in play here, and no isolation level makes Postgres silently discard a committed row.