5 ms·

The documentation for Postgresql range types describes how to do exactly this. https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-CONSTRAINT https:/

by sk5t 6y ago

The documentation for Postgresql range types describes how to do exactly this. https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPE... Edit: and if you didn't want to use postgres, you could have "starttime" and "endtime" columns and reject any bad bookings with a before insert / before update trigger.

gavinray 6y ago
This approach is the best and works really well if you don't need to do a join on a related table to look up information. If you need to use data outside of the current table for exclusions/check constraints, you have to write a trigger function (as far as I know). I had to solve this recently, where the actual start/end times were stored on a related table. I'm no SQL wizard, but I'd love to share my solution in case it helps others (it might be terrible). Note: I changed the actual tables/domain to be generic, this is a poor example and it made more sense for my usecase, but this shows general approach. -- Let's pretend we have these tables (awful design, but for sake of example): -- room <-> reservation <-> reservation_info -- Where "reservation_info" has "start_time" and "end_time" CREATE FUNCTION check_for_overlapping_reservations() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF ( -- Find the newly created reservation and join it with the info record to grab "start_time" and "end_time" for check below with this_reservation as ( select * from reservation inner join reservation_info on reservation_info.id = reservation.reservation_info_id where reservation.id = NEW.id ), bookings_for_timerange as ( -- Select every other reservation, where the reservation is happening in the same room select * from reservation as other_reservation, this_reservation inner join reservation_info as other_reservation_info on other_reservation_info.id = other_reservation.reservation_info_id where other_reservation.room_id = this_reservation.room_id AND -- And the timerange from start to end overlaps the newly created record tstzrange(this_reservation.start_time, this_reservation.end_time) && tstzrange(other_reservation_info.start_time, other_reservation_info.end_time) -- Get a count of all the records, it should only be 1. If it's greater than one, there's overlap. select count(*) from bookings_for_timerange ) > 1 THEN RAISE EXCEPTION 'Room is already reserved during this time period'; END IF; RETURN NEW; END;$$;
- sk5t 6y ago
  TBH it's a bit more verbose and less performant than need be, but hey, if it works, rock on! But don't use 'select *' in production code. Consider next time something like if exists (select from sometable t1 join sometable t2 on t1.resource_id = t2.resource_id and t1.res_id <> t2.res_id and tstzrange(t1.start, t1.end) && tstzrange(t2.start, t2.end) where t1.res_id = new.res_id ) then ... raise exception
  - gavinray 6y ago
    Ahh, that looks much cleaner. Thanks for the knowledge =)

by sk5t 6y ago

gavinray 6y ago

This approach is the best and works really well if you don't need to do a join on a related table to look up information. If you need to use data outside of the current table for exclusions/check constraints, you have to write a trigger function (as far as I know). I had to solve this recently, where the actual start/end times were stored on a related table. I'm no SQL wizard, but I'd love to share my solution in case it helps others (it might be terrible). Note: I changed the actual tables/domain to be generic, this is a poor example and it made more sense for my usecase, but this shows general approach. -- Let's pretend we have these tables (awful design, but for sake of example): -- room <-> reservation <-> reservation_info -- Where "reservation_info" has "start_time" and "end_time" CREATE FUNCTION check_for_overlapping_reservations() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF ( -- Find the newly created reservation and join it with the info record to grab "start_time" and "end_time" for check below with this_reservation as ( select * from reservation inner join reservation_info on reservation_info.id = reservation.reservation_info_id where reservation.id = NEW.id ), bookings_for_timerange as ( -- Select every other reservation, where the reservation is happening in the same room select * from reservation as other_reservation, this_reservation inner join reservation_info as other_reservation_info on other_reservation_info.id = other_reservation.reservation_info_id where other_reservation.room_id = this_reservation.room_id AND -- And the timerange from start to end overlaps the newly created record tstzrange(this_reservation.start_time, this_reservation.end_time) && tstzrange(other_reservation_info.start_time, other_reservation_info.end_time) -- Get a count of all the records, it should only be 1. If it's greater than one, there's overlap. select count(*) from bookings_for_timerange ) > 1 THEN RAISE EXCEPTION 'Room is already reserved during this time period'; END IF; RETURN NEW; END;$$;

sk5t 6y ago
TBH it's a bit more verbose and less performant than need be, but hey, if it works, rock on! But don't use 'select *' in production code. Consider next time something like if exists (select from sometable t1 join sometable t2 on t1.resource_id = t2.resource_id and t1.res_id <> t2.res_id and tstzrange(t1.start, t1.end) && tstzrange(t2.start, t2.end) where t1.res_id = new.res_id ) then ... raise exception
- gavinray 6y ago
  Ahh, that looks much cleaner. Thanks for the knowledge =)