PostgreSQL含外键多表重复记录删除方案及脚本咨询

一、自动识别全库外键关联关系 #

先通过以下SQL查询获取所有外键的父子表依赖关系，理清删除顺序（需先处理子表重复，再处理父表）：

SELECT
    tc.table_name AS child_table,
    kcu.column_name AS child_column,
    ccu.table_name AS parent_table,
    ccu.column_name AS parent_column,
    tc.constraint_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY parent_table, child_table;

该查询会返回所有子表、关联外键列、对应的父表及主键列，帮你明确数据依赖链。

二、批量识别各表重复记录 #

单表重复检测（自定义规则） #

针对单表，替换table_name和重复判断列，找出重复组：

SELECT col1, col2, ..., COUNT(*)
FROM table_name
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1;

全库自动生成检测脚本 #

如果需要批量检测所有表的重复，执行以下SQL生成对应检测语句（替换public为你的目标schema）：

SELECT 'SELECT ''' || table_name || ''' AS table_name, ' || 
       string_agg(column_name, ', ') || ', COUNT(*) FROM ' || table_name || 
       ' GROUP BY ' || string_agg(column_name, ', ') || 
       ' HAVING COUNT(*) > 1;'
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

执行生成的语句即可得到所有表的重复记录统计。

三、批量删除重复记录（兼容外键约束） #

单表删除逻辑（保留一条有效记录） #

对于单表，推荐用窗口函数高效删除重复，保留最新/最早的记录（替换table_name、重复列、主键列）：

WITH duplicates AS (
    SELECT 
        ctid,
        -- 按主键倒序，保留最新记录；改为ASC则保留最早
        ROW_NUMBER() OVER (PARTITION BY col1, col2, ... ORDER BY id DESC) AS rn
    FROM table_name
)
DELETE FROM table_name
WHERE ctid IN (SELECT ctid FROM duplicates WHERE rn > 1);

如果是超大数据量，可加LIMIT分批删除，避免长时间锁表。

按依赖顺序批量生成删除脚本 #

先通过递归查询获取表的拓扑删除顺序（子表优先，父表随后）：

WITH RECURSIVE fk_tree AS (
    SELECT
        child_table,
        parent_table,
        1 AS level
    FROM (
        SELECT DISTINCT tc.table_name AS child_table, ccu.table_name AS parent_table
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
    ) AS fks
    UNION ALL
    SELECT
        f.child_table,
        fp.parent_table,
        f.level + 1
    FROM fk_tree f
    JOIN (
        SELECT DISTINCT tc.table_name AS child_table, ccu.table_name AS parent_table
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
    ) AS fp ON f.parent_table = fp.child_table
)
SELECT child_table, MAX(level) AS dependency_level
FROM fk_tree
GROUP BY child_table
UNION ALL
-- 加入无外键依赖的表
SELECT table_name, 0 AS dependency_level
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name NOT IN (SELECT child_table FROM fk_tree)
ORDER BY dependency_level DESC;

根据返回的表顺序，结合单表删除逻辑，批量生成删除脚本，按顺序执行即可。

四、关键注意事项 #

1. 操作前必须备份：建议先在测试环境验证所有脚本，再在生产环境执行。
2. 大数据量分批处理：避免一次性删除大量数据导致锁表超时，可按主键范围或分批LIMIT删除。
3. 外键约束处理：除非万不得已，不要禁用外键约束；若必须禁用，操作完成后务必恢复：

   -- 禁用外键触发器
   ALTER TABLE table_name DISABLE TRIGGER ALL;
   -- 恢复外键触发器
   ALTER TABLE table_name ENABLE TRIGGER ALL;
   

4. pg_restore失败排查：之前的恢复方案出错，大概率是数据导入顺序不符合外键依赖（需先导入父表数据，再导入子表），可尝试拆分备份文件，按拓扑顺序分批导入。

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