Finding Conflicting Schedules with PostgreSQL Range Types

Question

Problem

We have a table dealing with scheduling vacation for users and some business rules saying that certain groups of people can't all be out at the same time. The relevant pieces of the scheduling table look like

user_id (text)	out_date_range (daterange)
1	["2021-12-27", "2021-12-30")
2	["2021-12-24", "2021-12-30")
3	["2022-01-24", "2022-01-27")
1	["2022-01-18", "2022-01-23")
2	["2022-01-25", "2022-01-30")

The goal is to find out if a given user, x, can create a new vacation schedule with date_range = [A,B) given a list of users that cannot be out at the same time as x.

Example

For example, suppose user 1 wants to create a new vacation schedule with date_range ["2022-01-26", "2022-01-29") and we know users 1, 2, and 3 cannot all be out at the same time. Our query should indicate it's not possible to schedule this since users 2 and 3 are both already out 2022-01-26 (see point 2 in Additional Notes).

Additional Notes

1. The number of users in the groups that cannot all be out at the same time is dynamic, it will be passed in as a list of user_id's
2. The query only needs to indicate if scheduling is possible (true/false); if we can additionally indicate what date range causes conflicts that would be nice, but that's not a requirement.
3. We're using version 12.6 of PostgreSQL (so no multirange functionality)

(Naive) Attempted Solutions

1. Create an intersection aggregator and intersect schedules for all users in the "cannot be out together group". This would work (I think) if each user only had one schedule, but since the users have multiple schedules that won't overlap the intersection is always empty.
2. Try to cross join (one for each user in the group) to generate the possible date ranges that could conflict and then intersect on those. I'm confused here both because the number of cross joins would be dynamic and even if we end up with the correct tuples not sure how the intersection would really work.

Answer 1

With PostgreSQL v14, you could run the following query to find out if a new entry would conflict with previous ones:

SELECT range_intersect_agg(mr)
FROM (SELECT range_agg(out_date_range) AS mr
      FROM mytable
      WHERE user_id <> '1' GROUP BY user_id) AS q
WHERE mr && '["2022-01-26", "2022-01-29")'::daterange;

This requires the new multirange data types.

First we build a multirange of the off time for each user other than user 1, then we see if there is any date in all of these multiranges by intersecting them. If the result is not empty, there is a conflict.

This cannot be done with a database constraint, so either use locking (SELECT ... FOR NO KEY UPDATE) or SERIALIZABLE transactions to prevent anomalies.