如何在PostgreSQL中基于date+time值创建EXCLUDE约束？

我有一张名为bookings的数据库表，结构如下：

column name | data type
----------------------
date        | date
start_time  | time
end_time    | time

其中start_time和end_time是time类型而非timestamp类型。我希望创建一个EXCLUDE约束，防止表中出现同一日期下时间范围重叠的记录。

由于tsrange适用于timestamp类型，我参考PostgreSQL官方文档（文档示例：date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00），通过date + time拼接生成timestamp，编写了如下SQL：

ALTER TABLE bookings
ADD CONSTRAINT overlapping_times
EXCLUDE USING GIST (
  tsrange(date + start_time, date + end_time) WITH &&
)

执行该SQL时出现错误：

error: data type integer has no default operator class for access method "gist"

排查过程 #

经提示发现使用的PostgreSQL旧版本不支持date + time直接生成timestamp，于是尝试通过字符串拼接再转换的方式，同时创建了btree_gist扩展：

CREATE EXTENSION btree_gist;

ALTER TABLE bookings
ADD CONSTRAINT overlapping_times
EXCLUDE USING GIST (
    tsrange(
        CONCAT(date::date || ' ' || start_time::time)::timestamp,
        CONCAT(date::date || ' ' || end_time::time)::timestamp
    ) WITH &&
);

但执行后又出现新错误：

functions in index expression must be marked IMMUTABLE

最终解决方案 #

将PostgreSQL从v12升级到v14后，最初的SQL语句可以正常工作，约束成功创建并生效。

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