Last active
December 9, 2024 22:21
-
-
Save fphilipe/0a2a3d50a9f3834683bf to your computer and use it in GitHub Desktop.
PostgreSQL EXCLUDE constraint
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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")). |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.