http://www.postgresql.org/docs/devel/static/rangetypes.html
CREATE TABLE room_reservation ( room TEXT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) );
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)'); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )).
Used to have to rely on the application to do these sorts of constraints.
[1] http://pgfoundry.org/projects/temporal/
http://www.postgresql.org/docs/devel/static/rangetypes.html
CREATE TABLE room_reservation ( room TEXT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) );
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)'); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )).
Used to have to rely on the application to do these sorts of constraints.