Postgresql - Constraints on Ranges - Two tables

Question

With constraints on ranges, we prevent adding any overlapping values in an existing table. For example, in the following room_reservation table, we make sure that no room is reserved in a conflicting time.

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

What we need here is to also consider another table (rooms) also having room and during fields and consider the records within that table while making a reservation?

Our specific scenario is exam management. We have an invigilation table (room reservation) and also a time table of classes. Once we are adding an invigilation record, we need to make sure that it does not coincide with any other invigilation record and make sure that there is no lecture at that time in that room.

Patrick · Accepted Answer · 2016-04-19 12:18:23Z

1

You cannot do that with a single exclusion constraint. Instead, you should use the exclusion constraint on one table, say invigilation, and then use a BEFORE INSERT trigger on that same table that checks if there is a conflict in the second table, say rooms. The trigger function on the first table would do a simple range check on the second table:

CREATE FUNCTION check_no_class() RETURNS trigger AS $$
BEGIN
  PERFORM * FROM rooms
  WHERE room = NEW.room
    AND during && NEW.during;
  IF FOUND THEN
    RETURN NULL;
  ELSE
    RETURN NEW;
  END IF;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER check_rooms
BEFORE INSERT ON invigilation
FOR EACH ROW EXECUTE PROCEDURE check_no_class();

If a class is scheduled in a room then the insert on invigilation will fail.

answered Apr 19, 2016 at 12:18

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

banbar Over a year ago

Thanks @Patrick! I was trying to add one more trigger based on updates on the invigilation table (i.e. a current invigilation record is updated to a new one). To do this, I created another trigger (check_rooms_2) by only changing the second line to "BEFORE UPDATE ON invigilation". However it seems that this did not work out?!

banbar Over a year ago

Having tested again, the updates are accepted (i.e. the record is not highlighted in blue). However if it is a conflicting update, once the invigilation table is refreshed, the table reverts back to the initial content.

Collectives™ on Stack Overflow

Postgresql - Constraints on Ranges - Two tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related