Postgres中如何比较timestamp与整数？授权码过期触发器报错如何解决

错误原因 #

两个timestamptz类型的时间做减法得到的是interval（时间间隔）类型，不能直接和整数600做比较，这是报错的直接原因。

修改方案 #

你只需要调整remove_expired函数里的删除条件，将整数转为对应的时间间隔类型即可，推荐直接使用可读性更强的时间间隔声明写法，修改后完整代码如下：

drop table if exists authorization_code;
create table if not exists authorization_code(
    id int generated always as identity,
    created_at timestamptz not null
);

drop function if exists remove_expired();
create function remove_expired() 
   returns trigger 
   language plpgsql
as $$
begin
    --NOTE 10 mins as recommended by the OAuth2 spec
    -- 写法1：直接用时间间隔字面量，可读性最高
    delete from authorization_code where created_at < now() - interval '10 minutes';
    -- 等价写法参考：
    -- delete from authorization_code where now() - created_at > interval '10 minutes';
    -- 偏好按秒计算也可以写：delete from authorization_code where now() - created_at > 600 * interval '1 second';
    return NEW;
end;
$$;

drop trigger if exists clean_up_expired_code on authorization_code;
create trigger clean_up_expired_code
  before insert
  on authorization_code
  for each row
  execute procedure remove_expired();

额外优化提示 #

上面示例中选择created_at < now() - interval '10 minutes'的写法，后续如果给created_at字段加索引的话，这条删除语句可以命中索引，执行效率会比做时间差值比较的写法更高。

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