PostgreSQL自动更新列：实现状态24小时后自动变更的方法

我有如下结构的表：

当用户将卡片状态更新为approved后，希望在恰好24小时后自动将状态变更为activated。请问能否通过原生SQL查询实现，还是需要使用PostgreSQL扩展？

原生SQL查询本身无法实现定时自动执行的逻辑，因为SQL是用于数据查询和即时操作的语言，没有内置的任务调度能力。要实现这个需求，PostgreSQL有几种可行方案：

• 使用官方内置的pg_cron扩展：这是PostgreSQL官方维护的轻量级定时任务扩展，安装后可以创建定时任务，定期检查并更新符合条件的记录。示例操作：

  -- 安装pg_cron（若未安装）
  CREATE EXTENSION IF NOT EXISTS pg_cron;
  
  -- 创建每分钟执行一次的定时任务（可按需调整执行频率）
  SELECT cron.schedule(
      'update-card-status-to-activated',
      '* * * * *',
      $$UPDATE your_table_name SET status = 'activated', updated_at = NOW() WHERE status = 'approved' AND NOW() - updated_at > INTERVAL '24 hours'$$
  );
  

• 触发器+外部调度程序：当卡片状态被更新为approved时，通过触发器发送数据库通知，外部程序（如Python脚本、Shell脚本）监听该通知后，设置一个24小时后的延迟任务来执行状态更新。这种方式无需数据库扩展，但依赖外部进程。

• 外部定时任务工具：如果不想使用任何数据库扩展，可以直接用系统级定时任务（比如Linux的crontab、Windows任务计划），定期执行SQL更新语句，筛选出状态为approved且更新时间超过24小时的记录，将其状态改为activated。

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