基于时间戳变更实现PostgreSQL单语句INSERT或UPDATE

完全可以实现，借助PostgreSQL的公共表表达式（CTE），我们能把「尝试更新+无匹配则插入」的逻辑封装成单条原子语句，完美贴合你的业务需求。

实现方案 #

WITH updated AS (
  UPDATE events
  SET updated_at = now()
  WHERE user_id = :user_id
    AND updated_at >= now() - interval '90 seconds'
  RETURNING 1
)
INSERT INTO events (user_id, created_at, updated_at /* 替换为你的其他事件字段 */)
SELECT :user_id, now(), now() /* 对应字段的取值 */
WHERE NOT EXISTS (SELECT 1 FROM updated);

逻辑说明 #

1. CTE更新阶段：先尝试更新指定用户最近90秒内的事件记录，将updated_at刷新为当前时间。RETURNING 1用于标记是否有记录被成功更新。
2. 插入判断阶段：如果CTE没有返回任何结果（说明该用户没有符合90秒间隔条件的记录），则执行插入操作，创建一条新的事件记录。

性能保障 #

你已创建的索引idx_events_user_id_updated_at（user_id + updated_at desc）能直接适配这个逻辑：

• 更新时，PostgreSQL可通过索引快速定位到用户的最新记录，判断是否在90秒窗口内
• 整个语句是原子性操作，不会出现并发场景下的竞态问题（比如重复插入）

为什么不适用常规ON CONFLICT #

正如你判断的，常规UPSERT依赖唯一约束，但这里的updated_at是动态变化的时间戳，无法提前设置固定的唯一键。而上述CTE方案不需要额外添加约束，完全匹配你的业务规则。

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