PostgreSQL迁移数据：UUID冲突时仅更新最新时间戳的方法

一、冲突时仅更新时间戳更新的数据 #

PostgreSQL 的 ON CONFLICT DO UPDATE 支持通过 WHERE 子句添加条件，仅在满足特定逻辑时执行更新。针对你的需求，只需在更新语句后增加时间戳判断，仅当待插入记录的时间戳比目标表中已存在的记录更新时，才执行更新操作：

INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT src."uuid", src."time_stamp", src."json_thing"
FROM info_tbl AS src
ON CONFLICT ("uuid")
DO UPDATE 
SET "time_stamp" = EXCLUDED."time_stamp", "json_thing" = EXCLUDED."json_thing"
WHERE EXCLUDED."time_stamp" > info_tbl_correct."time_stamp";

这里的 EXCLUDED 指代触发冲突的待插入记录，通过时间戳比较，精准实现「仅保留最新数据」的需求。

二、先过滤源数据，仅保留每个UUID的最新记录 #

如果是一次性全量迁移数据，更高效的方式是先从源表中筛选出每个 uuid 对应的最新记录，再插入目标表——这种方式可以直接避免冲突处理逻辑，插入的都是无重复的最新数据。

方法1：使用 DISTINCT ON #

PostgreSQL 特有的 DISTINCT ON 语法可按指定字段分组，保留每组排序后的第一条记录：

INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT DISTINCT ON (src."uuid") 
       src."uuid", src."time_stamp", src."json_thing"
FROM info_tbl AS src
ORDER BY src."uuid", src."time_stamp" DESC;

ORDER BY 先按 uuid 分组，再按 time_stamp 倒序排列，确保每个 uuid 只取时间戳最新的记录。

方法2：使用窗口函数 ROW_NUMBER() #

如果需要更灵活的筛选逻辑，可通过窗口函数标记每个分组内的最新记录：

WITH latest_records AS (
    SELECT 
        "uuid", "time_stamp", "json_thing",
        ROW_NUMBER() OVER (PARTITION BY "uuid" ORDER BY "time_stamp" DESC) AS rn
    FROM info_tbl
)
INSERT INTO info_tbl_correct
("uuid", "time_stamp", "json_thing")
SELECT "uuid", "time_stamp", "json_thing"
FROM latest_records
WHERE rn = 1;

PARTITION BY "uuid" 按UUID分组，ORDER BY "time_stamp" DESC 让最新记录的 rn 值为1，最后筛选出这些记录插入目标表即可。

问题补充解答 #

• PostgreSQL 支持在冲突处理中添加条件判断，并非只能执行无条件更新，上述第一种方案就是利用该特性实现需求。
• 完全可以在读取源数据时通过排序+筛选，提前保留每个UUID的最新记录，避免后续冲突处理，这种方式在一次性全量迁移时效率更高。

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