Postgres removing 'smaller' ranges from a 'bigger' range and return a aggregate of ranges

Question

I have a booking system that works with double bookings. A person books a room for a tsrange, and then can book appointments on top of that room reservation. I was able to make the constraints work fine, of course the tsrange of the appointments must be contained on the reservation tsrange.

Now, I need a query that returns a tsranges[] of the ranges that a reservation is idle, ie no appointments have been made at that time yet, but I am not sure how to do that...Well, I have a rough idea of how I might implement this in plpgsql looping over the appointments, but I wonder if there is a more elegant solution with plain SQL, maybe using a recursive CTE or a window function?

For example consider I have a reservation on range: '[2010-01-01 08:00, 2010-01-01 18:00)'.

And the following appointments on that reservation: '[2010-01-01 08:00, 2010-01-01 09:00)';'[2010-01-01 11:00, 2010-01-01 12:00)';'[2010-01-01 14:00, 2010-01-01 17:00)'

The output of such function be something like: '[2010-01-01 09:00, 2010-01-01 11:00)','[2010-01-01 12:00, 2010-01-01 14:00)','[2010-01-01 17:00, 2010-01-01 18:00)'

Here is a sample dbfiddle with the simplified schema :

create table reservation (
  id numeric,
  room_id numeric,
  during tsrange
 );
 
 create table appointment (
   id serial,
   on_reservation numeric,
   during tsrange
 );
 
 insert into reservation (id, room_id, during)
        VALUES (1, 1, '[2010-01-01 08:00, 2010-01-01 18:00)');
        
insert into appointment (id, on_reservation, during)
        VALUES (2, 1, '[2010-01-01 08:00, 2010-01-01 09:00)');

insert into appointment (id, on_reservation, during)
        VALUES (3, 1, '[2010-01-01 10:00, 2010-01-01 12:00)');

insert into appointment (id, on_reservation, during)
        VALUES (4, 1, '[2010-01-01 14:00, 2010-01-01 17:00)');

I am still not familiarized with multi-range support added in pg14 but if that makes things easier I can upgrade...

Answer 1

With PostgreSQL v14 and your data model, that could be as simple as

SELECT r.id,
       /* convert the reservation to a multirange */
       tsmultirange(r.during)
       -
       /* aggregate the appointments to a multirange */
       range_agg(a.during)
       AS free_slots
FROM reservation AS r
   JOIN appointment AS a ON a.on_reservation = r.id
GROUP BY r.id, r.during;