PostgreSQL使用窗口函数按非主键字段删除重复数据

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能执行成功，实际上是以牺牲生产环境的稳定性为代价换来的“方便”，这才是最需要警惕的地方。