PostgreSQL EXCLUDE constraint

exclude.sql

CREATE EXTENSION btree_gist;

CREATE TABLE room_reservations (
    room_id integer,
    reserved_at timestamptz,
    reserved_until timestamptz,
    canceled boolean DEFAULT false,
    EXCLUDE USING gist (
        room_id WITH =, tstzrange(reserved_at, reserved_until) WITH &&
    ) WHERE (not canceled)
);

INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 00:00', '2015-01-02 00:00'),
    (2, '2015-01-01 00:00', '2015-01-02 00:00');

-- Creating an overlapping reservation for room 1 is not possible:
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 10:00', '2015-01-02 14:00');
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 10:00:00+00","2015-01-02 14:00:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00"))])]).

-- Same for room 2:
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2014-12-31 23:00', '2015-01-01 00:01');
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2014-12-31 23:00:00+00","2015-01-01 00:01:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00")).

-- Canceling the reservation for room 1 makes it possible to add a new
-- reservation overlapping the canceled one:
UPDATE room_reservations SET canceled = true WHERE room_id = 1;
INSERT INTO room_reservations (room_id, reserved_at, reserved_until) VALUES
    (1, '2015-01-01 10:00', '2015-01-02 14:00');

-- Listing all reservations:
SELECT * FROM room_reservations;
--  room_id |      reserved_at       |     reserved_until     | canceled
-- ---------+------------------------+------------------------+----------
--        2 | 2015-01-01 00:00:00+00 | 2015-01-02 00:00:00+00 | f
--        1 | 2015-01-01 00:00:00+00 | 2015-01-02 00:00:00+00 | t
--        1 | 2015-01-01 10:00:00+00 | 2015-01-02 14:00:00+00 | f

-- Undoing the cancellation is not possible due to the new reservation.
UPDATE room_reservations SET canceled = false WHERE room_id = 1;
-- ERROR:  conflicting key value violates exclusion constraint "room_reservations_room_id_tstzrange_excl"
-- DETAIL:  Key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 00:00:00+00","2015-01-02 00:00:00+00")) conflicts with existing key (room_id, tstzrange(reserved_at, reserved_until))=(1, ["2015-01-01 10:00:00+00","2015-01-02 14:00:00+00")).

Thank you for such a nice explanation!

What if I had a "status" column instead of "canceled", and wanted to prohibit creating overlapping appointments when the status is "SCHEDULED" or "COMPLETED", but allow overlapping if the status is anything else. So, I could have overlapping appointments for the same room if their status is "PENDING", and could add more. But when any of those change their status to "SCHEDULED", no other overlapping appointment could be create.