Exclusion Constraints — Database-Enforced No-Overlap Rules

An exclusion constraint generalises the unique constraint: instead of "no two rows can be equal on these columns", it says "no two rows can satisfy this operator on these columns". The most powerful use: preventing overlapping ranges at the database level, without any application code.

-- Room bookings with a full exclusion constraint (revisited from Lesson 0)
-- The constraint guarantees: same room + overlapping period = rejected
CREATE TABLE IF NOT EXISTS conference_rooms (
    id          BIGSERIAL PRIMARY KEY,
    room        TEXT NOT NULL,
    organiser   TEXT,
    booked_for  tstzrange NOT NULL,       -- timestamp with time zone range
    attendees   INT,
    EXCLUDE USING gist (
        room      WITH =,                 -- same room
        booked_for WITH &&                -- overlapping time
    )
);

-- This insert succeeds
INSERT INTO conference_rooms (room, organiser, booked_for, attendees)
VALUES ('Boardroom', 'Alice', tstzrange('2024-06-01 09:00+00','2024-06-01 11:00+00'), 8);

-- ❌ This insert FAILS with: "conflicting key value violates exclusion constraint"
-- ❌ Same room, overlapping time — the constraint REJECTS it (this is the demo)
INSERT INTO conference_rooms (room, organiser, booked_for, attendees)
VALUES ('Boardroom', 'Bob', tstzrange('2024-06-01 10:30+00','2024-06-01 12:00+00'), 5);

More exclusion constraint patterns

-- Nurse shift scheduling: no nurse can be on two overlapping shifts
CREATE TABLE IF NOT EXISTS nurse_shifts (
    id          BIGSERIAL PRIMARY KEY,
    nurse_id    INT NOT NULL,
    ward        TEXT,
    shift       tstzrange NOT NULL,
    EXCLUDE USING gist (
        nurse_id WITH =,
        shift    WITH &&
    )
);

-- Doctor appointment slots: one patient per slot per doctor
CREATE TABLE IF NOT EXISTS appointments (
    id          BIGSERIAL PRIMARY KEY,
    doctor_id   INT,
    patient_id  INT,
    slot        tstzrange NOT NULL,
    EXCLUDE USING gist (
        doctor_id WITH =,
        slot      WITH &&
    )
);