Postgresql - Constraints on Ranges - Two tables -

with constraints on ranges, prevent adding overlapping values in existing table. example, in following room_reservation table, make sure no room reserved in conflicting time.

create extension btree_gist; create table room_reservation (     room text,     during tsrange,     exclude using gist (room =, during &&) ); 

what need here consider table (rooms) having room , during fields , consider records within table while making reservation?

our specific scenario exam management. have invigilation table (room reservation) , time table of classes. once adding invigilation record, need make sure not coincide other invigilation record , make sure there no lecture @ time in room.

you cannot single exclusion constraint. instead, should use exclusion constraint on 1 table, invigilation, , use before insert trigger on same table checks if there conflict in second table, rooms. trigger function on first table simple range check on second table:

create function check_no_class() returns trigger $$ begin   perform * rooms   room = new.room     , during && new.during;   if found     return null;   else     return new;   end if; end; $$ language plpgsql;  create trigger check_rooms before insert on invigilation each row execute procedure check_no_class(); 

if class scheduled in room insert on invigilation fail.