PostgreSQL：添加日期时间约束的唯一索引报错，如何实现单用户每日限兑？

错误原因分析 #

你之前的SQL报错是因为**now()是STABLE函数而非IMMUTABLE函数**，PostgreSQL要求索引的WHERE谓词必须使用IMMUTABLE函数（即输入相同则输出永远固定的函数）。now()会随时间动态变化，无法作为索引的静态判定条件，这和时区处理无关。

解决方案分两种场景 #

场景1：按UTC自然日限制（每天00:00-23:59:59 UTC） #

如果需求是每个用户在UTC的自然日内只能兑换一次，可以创建表达式唯一索引，将inserted_at转换为UTC日期后，和user_id组合作为唯一键：

CREATE UNIQUE INDEX one_redemption_per_user_per_utc_day
ON voucher_redemption (user_id, (DATE(inserted_at AT TIME ZONE 'UTC')));

说明：DATE(inserted_at AT TIME ZONE 'UTC')是IMMUTABLE表达式——带时区的时间戳转换为UTC日期后，结果固定不变，完全符合索引的要求。

场景2：滚动24小时窗口限制（上次兑换后24小时内不可再兑换） #

如果是严格的“从上次兑换时间起24小时内不能再兑换”（非自然日规则），无法直接用唯一索引实现，需要用触发器+检查函数来处理：

1. 创建检查函数：

CREATE OR REPLACE FUNCTION check_24h_redemption()
RETURNS TRIGGER AS $$
BEGIN
  -- 检查该用户最近24小时是否已有兑换记录，加锁避免并发插入绕过检查
  IF EXISTS (
    SELECT 1 FROM voucher_redemption
    WHERE user_id = NEW.user_id
      AND inserted_at >= NEW.inserted_at - INTERVAL '24 hours'
    FOR UPDATE
  ) THEN
    RAISE EXCEPTION '用户每24小时仅能兑换一次';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器：

CREATE TRIGGER trigger_24h_redemption_check
BEFORE INSERT ON voucher_redemption
FOR EACH ROW EXECUTE FUNCTION check_24h_redemption();

说明：触发器会在每次插入前执行检查函数，若用户24小时内已有兑换记录则抛出错误；FOR UPDATE用于锁定已有记录，避免并发插入时的竞态问题。

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