PostgreSQL创建联合唯一约束前删除违规重复记录方案咨询

查询重复违规记录 #

直接分组统计即可快速定位所有违反联合唯一约束的组合：

SELECT user_id, doc_id, COUNT(*) AS 重复次数
FROM mytable
GROUP BY user_id, doc_id
HAVING COUNT(*) > 1;

如果需要查看所有重复行的完整信息，用窗口函数实现更灵活：

SELECT * FROM (
    SELECT 
        id, user_id, doc_id,
        ROW_NUMBER() OVER (PARTITION BY user_id, doc_id ORDER BY id) AS 行序号
    FROM mytable
) t
WHERE 行序号 > 1;

上面的语句默认将每个user_id + doc_id组合中id最小的行标记为保留行（行序号=1），其余为重复行，你可以修改ORDER BY规则自定义保留逻辑，比如要保留最新插入的行就改为ORDER BY id DESC。

删除重复记录 #

方法1：直接删除（适合中小数据量） #

确认保留规则后直接执行删除即可，以下示例保留每个组合id最小的行：

DELETE FROM mytable
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY user_id, doc_id ORDER BY id) AS rn
        FROM mytable
    ) t
    WHERE rn > 1
);

删除完成后再执行你原本的新增约束语句即可生效。

方法2：重建表去重（适合超大数据量） #

如果表数据量极大，删除操作效率低，可以用PostgreSQL特有的DISTINCT ON语法快速重建去重表：

-- 创建和原表结构完全一致的临时表
CREATE TABLE mytable_temp (LIKE mytable INCLUDING ALL);
-- 插入去重后的数据
INSERT INTO mytable_temp
SELECT DISTINCT ON (user_id, doc_id) id, user_id, doc_id
FROM mytable
ORDER BY user_id, doc_id, id;
-- 替换原表
DROP TABLE mytable;
ALTER TABLE mytable_temp RENAME TO mytable;

提示：所有删除操作前建议先备份全表数据，避免误操作导致数据丢失。

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