PostgreSQL唯一daterange索引未按预期生效问题求助

为什么唯一索引不生效？ #

你创建的唯一索引是基于精确值匹配的，PostgreSQL的唯一索引只会在booking_period完全相等且room_id相同时触发冲突，不会识别时段重叠（&&操作符）的场景，所以无法满足你“同一房间非取消状态下时段不重叠”的需求。

正确实现非重叠约束：排除约束 #

用排除约束可以直接实现范围重叠的校验，同时通过WHERE子句限定只对status <> 4的记录生效：

ALTER TABLE bookings
ADD CONSTRAINT bookings_non_overlapping_active
EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)
WHERE (status <> 4);

这个约束会阻止同一room_id下，status≠4的记录出现重叠的booking_period，完全符合业务规则。

补充索引优化最新时段查询 #

排除约束默认生成的GIST索引不支持指定排序方向，如果需要优先查询最新的预订时段，额外创建一个部分BTREE索引即可：

CREATE INDEX idx_bookings_latest_period ON bookings (booking_period DESC, room_id)
WHERE (status <> 4);

这个索引可以加速按最新时段排序的查询，比如查找指定房间的最近预订、筛选空闲房间时优先检索最新时段的记录。

测试验证 #

执行以下语句可以验证约束逻辑：

-- 触发约束冲突：room_id=5的时段重叠且status≠4
INSERT INTO bookings (room_id, status, booking_period) VALUES
(5, 2, '[2024-04-16, 2024-04-18)');

-- 正常插入：status=4（已取消）不受约束限制
INSERT INTO bookings (room_id, status, booking_period) VALUES
(5, 4, '[2024-04-16, 2024-04-18)');

内容的提问来源于stack exchange，提问作者Andreas M.