PostgreSQL添加唯一索引报错23505 如何删除重复数据完成约束设置

前置准备（建议测试环境执行前先操作） #

先备份测试表避免误删问题：
CREATE TABLE test_user_bak AS TABLE test_user;

步骤1：查询重复的personid #

先确认重复数据规模：

SELECT personid, count(*) 
FROM test_user 
GROUP BY personid 
HAVING count(*) > 1;

步骤2：删除重复行，仅保留每个personid的任意一行 #

这里用PostgreSQL内置的ctid系统字段标识行数据，每组重复的personid只保留ctid最小的那一行（也可修改为保留ctid最大的行，不影响最终唯一约束效果）：

DELETE FROM test_user
WHERE ctid NOT IN (
    SELECT min(ctid)
    FROM test_user
    GROUP BY personid
);

如果表数据量较大，可以使用下面的写法获得更高执行效率：

DELETE FROM test_user a
USING test_user b
WHERE a.ctid < b.ctid 
AND a.personid = b.personid;

步骤3：重新执行添加唯一约束操作 #

ALTER TABLE test_user ADD CONSTRAINT test_constraint UNIQUE (personid);

注意事项 #

• 以上操作先完全在测试表验证通过，确认剩余数据符合预期后再到正式表执行
• 正式表操作前一定要做全量备份，避免误删数据造成损失

内容的提问来源于stack exchange，提问作者Shaurya Chaudhuri