PG - Select a record not reserved yet I have a PG (12) database with two tables: 

RESERVABLES

id               uuid


RESERVATIONS

id               uuid
reservable_id    uuid
reserved_between tsrange    


Where reservations.reservable_id is a foreign key on reservables.

Now, given a tsrange (i.e. [2020-02-01 14:30, 2020-02-01 15:30]) I need to find a Reservable not reserved yet for that time interval. However, I have no idea how to proceed.

A big plus would be to find the Reservable with the most free time (fewer reservations) for that date.
This is my (untested) query:

SELECT p.id
FROM reservables AS p
   LEFT JOIN reservations AS c
      ON p.id = c.reservable_id
         AND NOT c.reserved_between
                 &amp;&amp; tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]')
WHERE NOT EXISTS (SELECT 1 FROM reservations AS s
                  WHERE p.id = s.reservable_id
                  AND s.reserved_between
                      &amp;&amp; tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]'))
GROUP BY p.id
ORDER BY count(c.id) NULLS FIRST
LIMIT 1;


The important part is the NOT EXISTS condition, the left outer join, GROUP BY and ORDER BY are just to collect the big plus. If the performance is not good enough, go for the simpler query.