PostgreSQL时间序列表如何实现冲突时可选更新或插入新版本

保留现有表结构即可，无需新增全局唯一约束，通过两套独立的SQL逻辑分别适配两种操作场景即可，互不影响：

场景1：覆盖更新（相同variable_id+datetime仅保留最新值） #

通过CTE实现类UPSERT效果，先更新匹配记录，无匹配记录时再插入：

WITH updated AS (
    UPDATE public.time_series
    SET value = $新值, edit_datetime = now()
    WHERE variable_id = $目标变量ID 
      AND datetime = $目标时间
    RETURNING 1
)
INSERT INTO public.time_series (variable_id, datetime, value)
SELECT $目标变量ID, $目标时间, $新值
WHERE NOT EXISTS (SELECT 1 FROM updated);

上述逻辑和加唯一约束后的ON CONFLICT DO UPDATE效果完全一致，不会产生重复的同variable_id+datetime记录。

场景2：留存历史版本（相同variable_id+datetime新增行） #

直接沿用已有的逻辑即可，若需要避免插入完全重复的value可以加WHERE NOT EXISTS判断，不需要的话直接执行INSERT即可：

-- 需要去重完全相同值的版本时用这个
INSERT INTO public.time_series (variable_id, datetime, value)
SELECT $目标变量ID, $目标时间, $新值
WHERE NOT EXISTS (
    SELECT 1 FROM public.time_series 
    WHERE variable_id = $目标变量ID 
      AND datetime = $目标时间 
      AND value = $新值
);

-- 不需要去重直接插入即可
-- INSERT INTO public.time_series (variable_id, datetime, value)
-- VALUES ($目标变量ID, $目标时间, $新值);

可选优化：高并发场景下的增强方案 #

如果需要解决并发操作可能导致的冲突问题，可新增版本标记字段配合部分唯一约束实现更稳定的支持：

1. 修改表结构新增约束

-- 新增最新版本标记字段
ALTER TABLE public.time_series ADD COLUMN is_latest boolean NOT NULL DEFAULT true;
-- 新增部分唯一约束，保证同一个variable_id+datetime最多只有一条最新版本
ALTER TABLE public.time_series ADD CONSTRAINT unique_latest_version UNIQUE (variable_id, datetime, is_latest);

2. 对应操作逻辑

• 覆盖更新：直接使用原生UPSERT逻辑，性能更高

  INSERT INTO public.time_series (variable_id, datetime, value)
  VALUES ($目标变量ID, $目标时间, $新值)
  ON CONFLICT (variable_id, datetime, is_latest)
  DO UPDATE SET value = $新值, edit_datetime = now();
  

• 留存历史版本：先将原有最新版本标记为非最新，再插入新版本

  -- 标记旧版本为非最新
  UPDATE public.time_series
  SET is_latest = false
  WHERE variable_id = $目标变量ID 
    AND datetime = $目标时间 
    AND is_latest = true;
  -- 插入新版本
  INSERT INTO public.time_series (variable_id, datetime, value)
  VALUES ($目标变量ID, $目标时间, $新值);
  

后续查询最新版本时只需加WHERE is_latest = true过滤条件即可，历史版本可通过edit_datetime追溯。

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