Skip to content

Instantly share code, notes, and snippets.

@tennisonchan
Forked from fphilipe/exclude.sql
Created February 23, 2017 14:52
Show Gist options
  • Save tennisonchan/f17b96ad4b7c62fe34ae3de501f99534 to your computer and use it in GitHub Desktop.
Save tennisonchan/f17b96ad4b7c62fe34ae3de501f99534 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")).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment