PostgreSQL中如何高效安全实现时序记录的插入/合并操作？

完全可以用PostgreSQL的CTE（公共表表达式）结合UPDATE和INSERT实现单查询的merge操作，不需要依赖(station_id,name)的唯一约束，核心思路是先锁定目标时序的最新记录，再根据新值判断执行更新或插入。

前提假设 #

假设你的气象数据表结构如下：

CREATE TABLE weather_data (
    station_id INT,
    name TEXT,
    value NUMERIC,
    first_time TIMESTAMP,
    last_time TIMESTAMP
);

单查询实现代码 #

WITH latest_entry AS (
    -- 获取对应站点、参数的最新一条记录，并加锁防止并发冲突
    SELECT *
    FROM weather_data
    WHERE station_id = :station_id_input
      AND name = :param_name_input
    ORDER BY last_time DESC
    LIMIT 1
    FOR UPDATE
),
update_operation AS (
    -- 如果新值和最新记录的值一致，更新last_time为当前时间
    UPDATE weather_data
    SET last_time = CURRENT_TIMESTAMP
    FROM latest_entry
    WHERE weather_data.station_id = latest_entry.station_id
      AND weather_data.name = latest_entry.name
      AND weather_data.last_time = latest_entry.last_time
      AND weather_data.value = :new_value_input
    RETURNING *
)
-- 如果更新操作没有生效（说明值不同或无历史记录），插入新记录
INSERT INTO weather_data (station_id, name, value, first_time, last_time)
SELECT :station_id_input, :param_name_input, :new_value_input, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT 1 FROM update_operation);

关键逻辑说明 #

• FOR UPDATE：对查询到的最新记录加排他锁，避免并发场景下多个请求同时操作同一条时序数据导致的冲突
• update_operation CTE：执行更新后返回被修改的记录，如果有返回结果，说明已经完成了更新操作；如果无返回，说明要么没有历史记录，要么新值和最新值不同
• 最后的INSERT通过NOT EXISTS判断是否需要执行，完美覆盖两种业务场景

边界情况处理 #

• 当某个(station_id,name)组合还没有任何记录时，latest_entry为空，update_operation也无返回，此时会直接插入新记录，符合业务要求
• 如果历史记录的last_time存在重复（极端场景），可以考虑增加主键或唯一键来进一步精准匹配，但当前逻辑已经能覆盖绝大多数正常场景

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