我处理过不少PostgreSQL的脏数据问题说句实话“重复数据”这四个字背后藏着的坑远比新手想象的多。很多人在第一步就搞错了不是不会写SQL而是没搞清楚“什么样的重复才算重复”结果要么漏删要么误删要么把整张表锁死生产环境直接告警。这篇东西我不打算给你讲那些教科书式的理论。我就结合我自己在项目里踩过的坑、补过的漏把“PostgreSQL如何查找重复数据”和“如何安全删除重复数据”这两件事拆开揉碎了讲清楚从判断逻辑到实操SQL再到生产环境的高危操作姿势一次说透。1. 动手之前先搞清楚“重复数据”到底指什么在写任何一条SQL之前我们得先达成一个共识什么样的数据算是重复是某一行完全一样还是某几个关键字段一样这个定义没定清楚后面所有操作都是盲人摸象。1.1 重复的两种最常见形态第一种是整行完全重复。这种通常是因为程序端重复提交、导入数据时脚本跑了两遍、或者两个接口同时写了一张表导致的。表结构有主键的话不太可能出现整行重复但如果你建的表没有主键别笑生产环境里真的很多这种表或者主键是自增ID那么除了ID以外所有字段都一样的行就会堂而皇之的存在。第二种是某个业务字段重复。这种更隐蔽也更危险。比如用户表里的email字段按理说一个邮箱只能注册一次但由于代码里没加唯一约束、或者历史数据迁移过程中出了问题同一邮箱对应了多个账号。这时候如果没有id字段两行就是完全一样的但如果有id它们只是业务逻辑上重复物理上并不相同。这两种情况的处理逻辑完全不同。整行重复可以直接删除最多就是留一行而业务字段重复你得先决定“保留哪一行”是按最早创建时间留还是按最新更新时间留或者按ID最小留这个规则必须提前定好不能等SQL跑完才发现留错了数据。提示哪怕现在只是临时清理数据也强烈建议先想清楚这两个问题再动手。1.2 为什么PostgreSQL里没有现成的“去重”按钮用过Excel的同学都知道删除重复项就是一个按钮的事情。但到了PostgreSQL里为什么没有类似的语法比如DELETE DUPLICATE这样的东西原因在于关系型数据库的核心理论——集合操作。SQL处理数据是基于集合的而集合本身是不允许重复元素的。理论上一张设计合理的表就应该通过主键或唯一约束来保证数据的唯一性重复数据本身就是一种“违反了设计约定”的异常产物。所以数据库不会提供一个“默认去重”功能因为它根本不知道你视作重复的依据是什么。它只能给你提供“分组、排序、窗口函数、行号”这些基础能力由你自己组合出符合业务逻辑的去重方案。说白了工具给你了怎么用是你的事。2. 查找重复数据从笨办法到高效方法既然要处理第一件事肯定是先查出来。我把常见的情况分成三类从单一字段查重、多字段联合查重、以及整行重复的查找每一类都有对应的标准SQL你可以直接拿去用改一下表名和字段名就行。2.1 单字段重复查找比如现在有一张用户表users里面有id, email, name三个字段我想找出email重复的所有记录。最简单粗暴的思路是GROUP BY ... HAVING COUNT(*) 1SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1;这一步可以快速定位哪些邮箱重复了但它只返回email和数量并没有把这些重复行的完整信息展示出来。比如你想看看重复的都是谁、什么时候创建的这个SQL就不够用。所以更符合排查需求的写法是用子查询查出重复的email然后反查到完整行记录。SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) 1 ) ORDER BY email;这个写法比较直观性能也还可以如果email字段建了索引子查询的效率会很高全表扫描的开销不会太大。如果表特别大几十万上百万行的级别建议加上索引再跑。注意子查询里的HAVING COUNT(*) 1就是“重复”的定义——出现次数大于1。要根据业务调整这个条件比如有些业务允许重复2次超过3次才算异常那就改成HAVING COUNT(*) 2。另一种更“高级”的查找方式是用窗口函数ROW_NUMBER()。我之所以说高级不只是因为写法不同而是它后续可以直接复用删除重复数据也是基于同一套逻辑查和删之间可以无缝衔接SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) t WHERE t.rn 1;这条SQL的思路是按email分组组内按id从小到大排序标上序号。序号为1的是每组里id最小的那条也就是你预期保留的那条rn大于1的都是重复的。它顺便就把“保留哪条”的规则一并实现了非常漂亮。2.2 多字段联合查重很多时候单一字段无法判定重复比如订单表里只有“用户ID 商品ID 下单时间”三个字段合在一起才能确定唯一性。这种就需要在GROUP BY中把所有判定字段都列出来或者在PARTITION BY中把所有判定字段都加上。举个例子订单明细表order_items有order_id, product_id, quantity字段一个订单里同一个商品理论上只应该出现一次但由于接口重复推送出现了同订单同商品多条记录SELECT order_id, product_id, COUNT(*) FROM order_items GROUP BY order_id, product_id HAVING COUNT(*) 1;多字段查重的核心注意点字段顺序不影响结果但所有参与判定的字段必须在GROUP BY中和SELECT的普通字段中一致否则会报错——这是SQL标准的规定PostgreSQL执行得尤其严格。2.3 整行完全重复的查找如果一张表连主键都没有而且你想要找的是完全一模一样的行那处理起来就很微妙。PostgreSQL里有个隐藏字段ctid它表示每一行在物理存储上的位置。不同行即使内容完全一样它们的ctid也一定不同。所以查找整行重复的方式就是按所有业务字段分组然后看组内行数SELECT * FROM my_table t WHERE t.ctid IN ( SELECT ctid FROM ( SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY ctid) AS rn FROM my_table ) s WHERE s.rn 1 );这里PARTITION BY后面要罗列这张表的全部字段。如果字段很多写起来会有点痛苦但这是唯一能准确判断“整行完全相同”的办法。因为只要有任何一个字段不一样就不算重复——这就回到了我对开头那个问题的解释重复的定义完全取决于你SELECT了哪些字段。3. 删除重复数据不同的策略不同的代价查出来只是开始删才是重头戏。很多初学者第一次删重复数据直接就写DELETE FROM table WHERE 重复条件结果发现把重复的行全删了连保留的那一条也没了。正确的做法有多种我把它们按适用场景排个序。3.1 方法一使用ctid保留一条无主键表的救星如果你处理的表没有主键也没有唯一约束那最理想的删除定位方式就是使用ctid。ctid是PostgreSQL里的物理行标识符只要记录在表里存在它的ctid就是唯一的。所以对没有主键的表来说ctid就是现成的“伪主键”。删除逻辑一句话总结找出每组重复数据中ctid最大的那条或者最小的那条只留下它剩下的全部删除。DELETE FROM my_table t USING my_table t2 WHERE t.ctid t2.ctid AND t.col1 t2.col1 AND t.col2 t2.col2;拆解一下这个SQL。DELETE ... USING是PostgreSQL特有的语法允许在DELETE语句中引入另一张表的别名参与条件判断。意思就是对于每一行t如果存在另一行t2它们的业务字段相同而t的ctid小于t2的ctid那就把t删掉。打个比方你有一堆重复的快递单每一单都有一个包裹码ctid你只需要保留码最大的那一单其他同地址同收件人的都扔掉。这样最终每组重复数据只会留下ctid最大的那条也就是最后写入磁盘的那条。这个方案的优势在于无需主键、无需窗口函数、执行计划通常比较高效因为ctid是物理定位的。但注意它保留的是ctid最大的那条如果业务上需要保留最早的那条就把改成。注意ctid不是一成不变的。执行VACUUM FULL之后表的物理存储会重组ctid会改变。所以如果你在程序里长期存储ctid作为定位依据那是不可靠的。但在清理任务这种一次性操作中完全不用担心这个问题。3.2 方法二使用聚合函数保留特定一行有时候业务上有明确要求比如重复数据要保留注册时间最早的那条或者保留订单金额最大的那条。这时候ctid这种物理位置的方法就不够语义化了因为“最早”是按某个时间字段判断的而不是按物理存储位置。这种需求推荐用窗口函数来解决分三步第一步为每一行标号SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users;PARTITION BY email表示按email分组ORDER BY created_at ASC表示同一个email下创建时间越早的排越前面。所以rn1的就是创建时间最早的。第二步查出所有rn大于1的idSELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users ) t WHERE t.rn 1;第三步也是最爽的一步——PostgreSQL允许直接在DELETE里引用这个子查询DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users ) t WHERE t.rn 1 );这个删除方案可以灵活调整ORDER BY的字段和方向比如改成ORDER BY created_at DESC就变成保留创建时间最新的那条删除更早的旧记录。又或者你想保留登录次数最高的那条改成ORDER BY login_count DESC就可以。3.3 方法三使用DISTINCT重建表只适合小表这个方法比较暴刀适合数据量不大几千到几万行且表结构简单的场景。思路就是把去重后的数据导出来删掉原表再插回去。CREATE TABLE tmp_table AS SELECT DISTINCT * FROM my_table; TRUNCATE my_table; INSERT INTO my_table SELECT * FROM tmp_table; DROP TABLE tmp_table;这样做确实能去重而且是整行级别的去重简单直接。但缺点也很致命第一它会锁表。TRUNCATE和INSERT期间应用对这张表的读写全部会被阻塞如果是生产环境的活跃表这会造成业务中断。第二如果表上有外键关联、触发器或者依赖这张表结构的视图重建表的操作很容易把这些依赖关系搞坏。第三如果表数据量大整个过程耗时很长期间占用的磁盘空间也可能翻倍。所以这个方法我只建议用在一张小规模的、无依赖的临时表上。正经业务表千万别这么玩否则数据库运维会找你算账的。3.4 生产环境删除要注意什么不管你用哪种方法在删除之前有四个点必须过一遍第一先备份。不要嫌麻烦。哪怕是在测试环境操作也建议先CREATE TABLE 表名_bak_日期 AS SELECT * FROM 原表;花费的时间通常几秒到几分钟但这是你的后悔药。第二先在SELECT里验证。删除之前先用同款的SELECT查一下看看你打算删掉的是多少行。如果这个数字跟你预估的偏差很大先停下来排查原因。第三评估锁的影响。DELETE操作会对涉及的行加锁如果表是热点表删除大量数据时要分批进行不要在高峰期一次性删除几十万行。第四清理Vacuum。PostgreSQL的DELETE并不会立即释放磁盘空间只是给数据打上了删除标记。删除大量数据后建议执行VACUUM ANALYZE 表名;回收空间并更新统计信息。如果删了很大体量的数据需要考虑VACUUM FULL但那会把表锁住只能用在维护窗口。4. 高频场景实战三个具体案例前面讲了方法论估计有些同学看完还是一头雾水。我干脆再给你放三个实战案例从易到难覆盖最常见的几个场景你可以直接对标你自己的表。4.1 场景一单字段重复保留ID最小的一条比如users表 email重复保留每个邮箱下ID最小的那条。这条SQL在生产环境里我用了很多次稳得很。DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn FROM users ) t WHERE t.rn 1 );执行完之后建议立刻检查一下SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1;如果这条查不出任何结果就说明去重成功。4.2 场景二多字段联合重复保留最新一条比如订单表orders中同一个用户下单同一个产品出现了多次需要保留最新一条删除旧的。DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY user_id, product_id ORDER BY created_at DESC ) AS rn FROM orders ) t WHERE t.rn 1 );这里的ORDER BY created_at DESC就是保留最新记录的语义。如果反过来要保留最早的下单记录改成ASC就行。4.3 场景三无主键表全靠ctid拯救我遇到过一个最头疼的情况一张日志表不仅没有主键连唯一字段都找不到整行完全一样重复得清清楚楚。这种表没有任何业务字段可以区分哪条是“最早的”也没法用ID因为压根没有ID。没办法最后用的就是ctid方案DELETE FROM log_table t USING log_table t2 WHERE t.ctid t2.ctid AND t.user_id t2.user_id AND t.action t2.action AND t.created_at t2.created_at;因为整行都一样所以要把所有字段都列在AND条件里。ctid t2.ctid表示把物理存储位置更靠前的删掉保留最后写入的那条。如果物理位置上越靠前的反而是你不想留的比如日志希望保留最早出现的那就要想清楚保留规则再改。注意这种无主键表虽然能用ctid删除临时救火但根因还是建表时没有遵守数据库设计规范。正常情况下每张业务表都应该有主键至少要有唯一约束。如果实在没有也要加一个自增字段作为主键。5. 常见问题与排查技巧实录写SQL过程中你会遇到各种奇奇怪怪的状况我挑几个出现频率最高的直接给你录成速查表。问题现象原因分析解决方案DELETE后的行数比预期多很多重复判断字段选择不当把不该视为重复的字段排除在外了回查SELECT语句确认PARTITION BY和WHERE条件是否覆盖了正确的业务字段执行到一半报死锁多事务并发操作同一组数据互相等待锁避免在业务高峰期批量删除或分批提交每批删除后COMMIT删除后磁盘空间没变小PostgreSQL的DELETE只是标记删除物理空间未回收执行VACUUM ANALYZE回收空间必要时用VACUUM FULL会锁表子查询里有窗口函数但报错说不能让窗口函数出现在WHERE中SQL语法限制必须在子查询里先算好RN字段将窗口函数放在FROM子句的子查询中外层条件筛选rn有外键约束导致删除失败其他表有外键引用这张表的记录先处理关联表的数据或临时禁用触发器或考虑级联删除谨慎删除时忘记备份误删了不该删的操作前没做备份养成习惯先备份再操作PostgreSQL支持基于时间点的恢复可救急5.1 为什么不建议直接写DELETE FROM t WHERE 重复字段 IN (...)?我见过不少新手写出这样的SQLDELETE FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) 1 );这个SQL执行完所有email重复的用户全部被删得一干二净一条都不剩。因为它没有“保留一条”的逻辑。如果你确实只想删除重复的多余数据而不是把所有重复的连根拔起这个方法就会翻车。我之所以强调这一点是因为我见过有人这么干过最后从备份恢复数据恢复了一下午。如果想要用这种写法保留一条就得手动加上“排除保留的那条”的条件比如DELETE FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) 1 ) AND id NOT IN ( SELECT MIN(id) FROM users GROUP BY email HAVING COUNT(*) 1 );逻辑上没问题但可读性和执行效率都不如窗口函数版本。而且在数据量大时两个IN子查询的代价会非常高。5.2 大批量删除时如何防止锁表和死锁如果你要删除的数据量超过几万行甚至几十万行不要试图一个DELETE搞定一切。长时间占用大量行锁业务查询会被阻塞严重时直接把数据库连接池耗尽。一个稳妥的做法是分批删除。比如每次只删5000行DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn FROM users ) t WHERE t.rn 1 LIMIT 5000 );然后循环执行直到没有行被删除为止。在生产环境中这个操作建议在维护窗口内使用事务包装执行每批提交一次避免长事务。5.3 删除后别忘了处理索引和统计信息大批量删除后表上的索引可能变得空洞统计信息也可能过期导致查询计划变得很糟糕。这时候执行两件事第一步更新统计信息ANALYZE users;第二步如果表的膨胀比较严重可以考虑重建索引REINDEX TABLE users;或者直接在维护窗口内VACUUM FULL ANALYZE users;这条命令会重写整张表回收所有碎片空间并更新统计信息是清完数据后的一剂良药。代价是执行期间会锁表所以只能安排在业务低峰期。6. 事后反思如何从根源上避免重复数据删数据删得再漂亮也治标不治本。只要源头不堵住过一段时间重复数据又会冒出来。在清理完现有数据之后至少要补上下面几道防线。第一道防线是数据库约束。如果email本来就该唯一那就直接加上唯一索引CREATE UNIQUE INDEX idx_users_email ON users (email);如果整张表基于多字段唯一就建联合唯一索引ALTER TABLE order_items ADD CONSTRAINT unique_order_product UNIQUE (order_id, product_id);不过要注意在已有重复数据的表上添加唯一约束会失败。所以必须先清理再建约束。第二道防线是应用层校验。程序端在插入数据之前先查询一下是否已存在虽然会有并发问题不能完全杜绝重复但至少能挡住大部分人为操作造成的重复。第三道防线是定期巡检。写几个查重SQL挂到定时任务里定期执行有异常数据就告警。天底下没有一劳永逸的办法保持警觉才是正道。第四道防线是规范导入流程。很多重复数据都是数据迁移和导入时产生的脚本跑了两次、导入文件重复读取都会造成重复。批量导入前记得给临时表或源文件加去重处理导入后立刻检查重复数量。我喜欢把数据库清理比喻成打扫房间。今天你辛辛苦苦把地上的垃圾捡干净了如果窗户不关好、垃圾不扔到桶里明天又会满地都是。约束、约束、再约束这才是治本。