PostgreSQL如何查询重复行并仅保留最新更新记录实现表去重

以下操作默认以name字段作为重复判定维度（即name值相同即判定为重复数据），如果你的业务需要多字段组合判定重复，替换对应字段即可。

步骤1：查找所有重复行 #

先确认重复数据范围：

-- 查询所有存在重复的字段值及重复次数
SELECT name, COUNT(*) AS duplicate_count
FROM products
GROUP BY name
HAVING COUNT(*) > 1;

如果需要查看所有重复行的完整明细，执行以下语句：

SELECT *
FROM products
WHERE name IN (
    SELECT name FROM products GROUP BY name HAVING COUNT(*) > 1
)
ORDER BY name, updated_date DESC;

步骤2：查询每组重复项中更新日期最新的记录 #

使用PostgreSQL原生支持的窗口函数ROW_NUMBER()实现分组排序，每组按更新日期倒序，取排名第1的即为最新记录；增加id DESC作为兜底排序规则，避免多条记录更新日期完全一致时结果不确定：

WITH ranked_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY name
            ORDER BY updated_date DESC, id DESC
        ) AS row_rank
    FROM products
)
SELECT id, name, created_date, updated_date
FROM ranked_records
WHERE row_rank = 1;

步骤3：执行表去重，删除冗余重复数据 #

注意：执行删除操作前建议先备份表数据，或者通过事务执行，确认结果正确后再提交，避免误删。

-- 开启事务，操作异常可随时回滚
BEGIN;

WITH ranked_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY name
            ORDER BY updated_date DESC, id DESC
        ) AS row_rank
    FROM products
)
-- 删除每组中除最新记录外的所有冗余行
DELETE FROM products
WHERE id IN (
    SELECT id FROM ranked_records WHERE row_rank > 1
);

-- 执行完后可先查询表数据验证去重结果
-- 结果符合预期执行 COMMIT; 提交变更
-- 结果不符合预期执行 ROLLBACK; 回滚所有操作

补充说明 #

• 如果重复判定规则为多字段匹配（例如name+created_date同时相同才算重复），只需要修改PARTITION BY后的字段列表，改为PARTITION BY name, created_date即可
• 去重完成后可以给判定重复的业务字段加唯一约束，从根源避免后续再产生重复数据：

ALTER TABLE products ADD CONSTRAINT uk_products_name UNIQUE (name);

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