Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@fphilipe
Last active January 9, 2024 14:59
Show Gist options
  • Star 46 You must be signed in to star a gist
  • Fork 12 You must be signed in to fork a gist
  • Save fphilipe/0a2a3d50a9f3834683bf to your computer and use it in GitHub Desktop.
Save fphilipe/0a2a3d50a9f3834683bf to your computer and use it in GitHub Desktop.
PostgreSQL EXCLUDE constraint
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")).
@TilavovD
Copy link

TilavovD commented Feb 9, 2023

Thank you for such a nice explanation!

@mtbossa
Copy link

mtbossa commented Aug 1, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment