如何从PostgreSQL预订系统中查询特定时段的可用房间？

我查阅了许多相关话题来解决这个问题，但没找到符合自身场景的答案，在此分享我的解决方案，希望能帮助到他人。

所需表结构 #

rooms表（存储房间基础信息） #

• id
• roomId
• roomName

bookings表（存储房间预订信息） #

• id
• roomId
• startDate (timestamp)
• endDate (timestamp)

解决方案代码 #

with room_booked as (select distinct(f1.roomId)
      from bookings f1
      where tsrange(f1.startDate, f1.endDate, '[]') && tsrange(<< your start date>>, << your end date >>, '[]'))
      SELECT
        rooms.roomId,
        rooms.roomName
      FROM rooms 
      WHERE NOT EXISTS (
        SELECT 1
        FROM room_booked
        WHERE room_booked.roomId = rooms.roomId
      )  

代码说明 #

1. 第一段WITH语句通过tsrange函数判断预订时段与目标时段的重叠关系，筛选出所有在目标时段内已被预订的房间ID；
2. 第二段通过NOT EXISTS从rooms表中排除已被预订的房间，得到目标时段内的可用房间；
3. 可根据实际需求在第二段的WHERE子句中添加额外筛选条件（比如房间类型、容量等）。

内容的提问来源于stack exchange，提问作者OltreXR4