PostgreSQL如何实现根据非主键字段去重删除
用 ROW_NUMBER() + CTE 标序号再删，最稳
说到PostgreSQL的去重删除，窗口函数绝对是绕不开的核心工具，毕竟从8.4版本开始就支持了。其中，ROW_NUMBER() 尤其关键，它不依赖主键，完全由你定义的“重复判断字段”和排序逻辑说了算。比如，按 create_by 分组，再按 create_time DESC 排序，就能精准地为每组数据标上序号，最新那条自然就是第一名。
不过，这里真正的难点往往不在于“怎么写”，而在于“怎么写才安全、可控”。
- 务必用
WITH子句（CTE）把窗口函数包一层，因为PostgreSQL不允许在DELETE语句里直接嵌套窗口函数。 PARTITION BY后面跟的列，必须严格对应业务上定义“重复”的逻辑，少一列可能导致漏删，多一列则可能误删。ORDER BY决定了最终留下哪一条：想保留最新的就用DESC，保留最早的就用ASC。如果排序字段可能存在NULL值，记得用NULLS LAST或NULLS FIRST来显式控制其位置。
来看个具体例子，目标是保留每组相同 email 中 id 最小的那条记录：
WITH ranked AS (
SELECT id, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn
FROM people
)
DELETE FROM people
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
别直接 DELETE FROM ... WHERE ctid IN (...)，性能差还难读
有些朋友为了图省事，会想到用 ctid 这个系统列来标识物理行位置进行删除，比如下面这种写法：
DELETE FROM people WHERE ctid NOT IN ( SELECT MIN(ctid) FROM people GROUP BY email );
这种写法看似省略了排序逻辑，但问题其实相当明显：
ctid反映的是物理存储位置，不具备业务意义。你无法保证在同一组重复数据中，MIN(ctid)对应的就是最早插入或你真正想保留的那条，结果很可能是随机保留。- 使用
GROUP BY配合MIN(ctid)在大数据量下会触发全表扫描和哈希分组。实测在70万行的表上，这种操作耗时可能超过30秒。 - 语句的可读性极差。后续维护的人根本看不出来你的意图是保留“最早”还是“最新”的记录，也无从得知依据哪个字段来判断重复。
所以，除非业务场景明确要求“任意保留一条且完全不关心顺序”，否则最好避开 ctid 这种方案。
NOT IN 子查询在有 NULL 时会静默失效
这是一个非常隐蔽的坑。如果用来判断重复的字段（例如 email）允许为 NULL，那么下面这种常见的写法会导致所有包含 NULL 值的重复组都无法被删除：
DELETE FROM people WHERE email NOT IN ( SELECT MIN(email) FROM people GROUP BY email );
原因在于：NOT IN (..., NULL) 这个表达式的最终结果永远是 UNKNOWN，而PostgreSQL会将其当作 FALSE 处理，从而导致匹配不到任何行。
要绕过这个坑，通常有两个办法：
- 提前过滤掉
NULL值，在子查询和主查询中都加上WHERE email IS NOT NULL的条件。 - 改用对
NULL值安全的NOT EXISTS写法：
DELETE FROM people p1
WHERE EXISTS (
SELECT 1 FROM people p2
WHERE p2.email = p1.email
AND p2.id < p1.id
AND p2.email IS NOT NULL
);
大表删重前务必先建索引，不然删着删着就锁表
窗口函数本身不依赖索引，但 DELETE 语句最终执行时，需要精准定位并锁定待删除的行。如果 PARTITION BY 涉及的字段上没有索引，PostgreSQL就只能进行全表顺序扫描。对于一个70万行的表来说，这可能导致数分钟的表锁——在此期间，所有的 INSERT 和 UPDATE 操作都会被阻塞。
正确的做法应该是：
- 在用于去重的字段上建立索引，例如：
CREATE INDEX idx_people_email ON people(email); - 如果是多个字段组合判重（比如
name, email），则建立联合索引：CREATE INDEX idx_people_name_email ON people(name, email); - 索引创建完成后，记得执行
ANALYZE people;来更新统计信息，确保查询规划器能选择最优的执行路径。
没有索引的删重操作，表面上SQL能执行成功，实际上是以牺牲生产环境的稳定性为代价换来的“方便”，这才是最需要警惕的地方。
