添加具有where约束的唯一索引，该约束包括日期时间

Question

我有一个表voucher_redemption，它有一个user_id列和一个inserted_at列，其类型是带时区的时间戳。

我正在尝试应用一个唯一的索引，每个用户每24小时只允许一次兑换，我正在尝试这个：

CREATE UNIQUE INDEX
one_redemption_per_user_per_day
ON
voucher_redemption (user_id)
WHERE
(inserted_at AT TIME ZONE 'UTC')
BETWEEN
((now() - INTERVAL '24 HOURS') AT TIME ZONE 'UTC')
AND
(now() AT TIME ZONE 'UTC')
;

当我运行此程序时，我会收到以下信息：

(Postgex.Error(错误42P17(invalid_object_definition(索引谓词中的函数必须标记为IMMUTABLE

根据我在网上看到的其他帖子，这是因为给出的一些时间戳不是UTC，但据我所知是UTC。

我如何才能实现我想要实现的目标？

提前感谢

Answer 1

您的结果是因为now()函数不是不可变的。它访问服务器时间，而不是"根据参数"。不能使用它创建唯一约束。您可以创建触发器来有效地实现这一点。(见演示(

create or replace function validate_voucher_redemption()
returns trigger 
language plpgsql
as $$
begin 
if exists (select null
from voucher_redemption 
where user_id = new.user_id
and inserted_at at time zone 'UTC'+ interval '24 hour' > now() at time zone 'UTC'
) 
then raise exception 'Cannot redeem a second voucher within 24 hours.';  
else return new; 
end if;
end; 
$$;
create trigger validate_voucher_redemption_bir
before insert on voucher_redemption
for each row 
execute function validate_voucher_redemption();

注意：所有内容都在"UTC"时区，但这不是必需的。