查找与 PostgreSQL 范围类型冲突的计划

Question

问题

我们有一个表格处理为用户安排假期和一些业务规则，说某些人群不能同时外出。调度表的相关部分如下所示

<th style="text-align： center;">user_id (text)["2021-12-24", "2021-12-30")["2022-01-18", "2022-01-23")

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

使用 PostgreSQL v14，您可以运行以下查询来找出新条目是否与以前的条目冲突：

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;

这需要新的多范围数据类型。

首先，我们为用户 1 以外的每个用户构建一个多范围的关闭时间，然后我们通过相交来查看所有这些多范围中是否有任何日期。 如果结果不为空，则存在冲突。

这不能用数据库约束来完成，所以使用锁定(SELECT ... FOR NO KEY UPDATE)或SERIALIZABLE事务来防止异常。