PostgreSQL实战：一键清理重复记录，只留最新那条

最近在维护招聘系统数据库时遇到个常见问题：recruitment_candidate（候选人表）里出现了不少重复记录——同一个创建人（create_by）录入了多次相同候选人信息，但我们只需要保留每条重复记录中创建时间（create_time）最新的那一条。今天就把整个解决过程拆解开，从思路到代码一步步讲清楚，即使是数据库新手也能看明白。

一、先把问题说透：我们要删什么？留什么？

假设recruitment_candidate表有这样几条数据：

id（主键）	candidate_name	create_by	create_time
1	张三	user001	2025-11-18 09:00:00
2	张三	user001	2025-11-19 14:30:00
3	李四	user002	2025-11-20 10:15:00

可以看到create_by='user001'有两条重复记录，我们需要删除id=1的旧记录，保留id=2的最新记录；而create_by='user002'只有一条，无需处理。核心需求就是：按create_by分组，组内按create_time倒序排，只留第一个（最新），删剩下的。

二、核心思路：给重复记录“排号”，删“小号”留“大号”？

这里的关键是给每条记录加个“组内序号”：

• 分组：用PARTITION BY create_by把相同创建人的记录归为一组；

• 排序：用ORDER BY create_time DESC让每组内最新的记录排在最前面；

• 标序号：用ROW_NUMBER()函数给每组的记录按顺序标上1、2、3……

这样一来，序号=1的就是我们要保留的最新记录，序号>1的都是要删除的重复记录。这个逻辑就像给每个“创建人小组”的记录发号码牌，只留拿到1号的那条。

三、实战代码：先验证，再删除（安全第一！）

重要提醒：删除数据前一定要先验证！先查清楚要删哪些，确认无误再执行删除操作，避免误删数据。

步骤1：查询要删除的重复记录

用CTE（公共表表达式）给记录标序号，然后筛选出序号>1的记录：

WITH ranked_candidates AS (
    SELECT 
        *,
        -- 核心：分组标序号
        ROW_NUMBER() OVER (
            PARTITION BY create_by 
            ORDER BY create_time DESC
        ) AS rn
    FROM recruitment_candidate
)
-- 查看要删除的记录（序号>1）
SELECT id, candidate_name, create_by, create_time 
FROM ranked_candidates 
WHERE rn > 1;

执行后会显示所有待删除的重复记录，对比业务数据确认没问题，再进行下一步。

步骤2：执行删除操作（有主键的情况）

如果表有主键（如id），直接用主键定位删除更精准（推荐）：

WITH ranked_candidates AS (
    SELECT 
        id,  -- 用主键定位行
        ROW_NUMBER() OVER (
            PARTITION BY create_by 
            ORDER BY create_time DESC
        ) AS rn
    FROM recruitment_candidate
)
DELETE FROM recruitment_candidate
WHERE id IN (SELECT id FROM ranked_candidates WHERE rn > 1);

特殊情况：表没有主键怎么办？

PostgreSQL有个内置字段ctid，它代表行在表中的物理位置，可以临时用来定位行（注意：ctid可能在VACUUM FULL后变化，仅适合临时删除场景）：

-- 验证待删除记录
WITH ranked_candidates AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (PARTITION BY create_by ORDER BY create_time DESC) AS rn
    FROM recruitment_candidate
)
SELECT * FROM ranked_candidates WHERE rn > 1;

-- 执行删除
WITH ranked_candidates AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (PARTITION BY create_by ORDER BY create_time DESC) AS rn
    FROM recruitment_candidate
)
DELETE FROM recruitment_candidate
WHERE ctid IN (SELECT ctid FROM ranked_candidates WHERE rn > 1);

四、避坑细节：这些情况要注意

1. create_time有NULL值怎么办？

如果create_time可能为NULL（NULL会被视为“最早”记录），可以用NULLS LAST确保NULL排在最后：

ROW_NUMBER() OVER (
    PARTITION BY create_by 
    ORDER BY create_time DESC NULLS LAST
) AS rn

2. 数据量大时如何提速？

如果表有几十万甚至上百万条数据，给create_by和create_time建个联合索引能大幅提升分组排序效率：

CREATE INDEX idx_create_by_time ON recruitment_candidate(create_by, create_time DESC);

五、总结：核心逻辑再梳理

整个过程其实就三步：分组→排序→标序号→删序号>1的记录。用ROW_NUMBER()窗口函数给重复记录“贴标签”，再通过主键或ctid精准删除，既简单又安全。

下次遇到类似“保留最新/最早记录，删除重复项”的需求，这套思路完全可以复用——把create_by换成你的分组字段，create_time换成排序字段就行。