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")).