PostgreSQL 12.8父子表重复数据清理方案咨询

针对你的场景，我们可以通过**CTE（公共表表达式）**结合批量更新实现需求，全程无需删除数据，也能高效处理大表。以下是分步实现方案：

1. 标记重复行并生成新ID映射 #

首先给table_1的重复行分配唯一新ID，同时记录新旧ID的映射关系，用于后续同步table_2：

WITH duplicate_rows AS (
    -- 标记table_1中重复的行，生成唯一行标识、新ID和分组序号
    SELECT 
        ctid,  -- PostgreSQL内置行唯一标识符，无主键/索引时用来精准定位行
        id AS old_id,
        -- 生成自增新ID（按现有ID的数字部分延续，可根据实际规则调整）
        'id-' || (SELECT COALESCE(MAX(SUBSTRING(id FROM '(\d+)')::INT), 3) + ROW_NUMBER() OVER (PARTITION BY id ORDER BY ctid)) AS new_id,
        -- 给每个旧ID的重复行排序，用于后续拆分table_2的关联数据
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY ctid) AS row_num
    FROM table_1
    WHERE id IN (SELECT id FROM table_1 GROUP BY id HAVING COUNT(*) > 1)
),
-- 更新table_1的重复行，并返回映射关系
updated_table1 AS (
    UPDATE table_1
    SET id = dr.new_id
    FROM duplicate_rows dr
    WHERE table_1.ctid = dr.ctid
    RETURNING dr.old_id, dr.new_id, dr.row_num
)
-- 将映射关系存入临时表（大表场景避免内存溢出）
SELECT old_id, new_id, row_num INTO TEMP TABLE id_mapping FROM updated_table1;

2. 同步更新table_2的关联数据 #

将table_2中每个旧ID的关联数据拆分，一半保留原ID，一半更新为对应新ID：

WITH ranked_table2 AS (
    -- 给每个旧ID的关联行排序，标记总行数和行序号
    SELECT 
        ctid,
        id AS old_id,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY ctid) AS rn,
        COUNT(*) OVER (PARTITION BY id) AS total_rows
    FROM table_2
    WHERE id IN (SELECT old_id FROM id_mapping)
),
-- 匹配需要更新的行与目标新ID
mapping_match AS (
    SELECT 
        rt2.ctid,
        -- 拆分规则：后半部分行更新为新ID，前半部分保留原ID
        CASE 
            WHEN rt2.rn > rt2.total_rows / 2 THEN im.new_id
            ELSE rt2.old_id
        END AS target_id
    FROM ranked_table2 rt2
    JOIN id_mapping im ON rt2.old_id = im.old_id
    WHERE im.row_num = 2  -- 对应table_1中第二个重复行的新ID
)
-- 批量更新table_2
UPDATE table_2
SET id = mm.target_id
FROM mapping_match mm
WHERE table_2.ctid = mm.ctid;

3. 验证与清理 #

执行完成后验证数据正确性：

-- 检查table_1是否无重复ID
SELECT id, COUNT(*) FROM table_1 GROUP BY id HAVING COUNT(*) > 1;

-- 检查table_2的ID分布是否符合预期
SELECT id, COUNT(*) FROM table_2 GROUP BY id;

清理临时表：

DROP TABLE id_mapping;

大表优化提示 #

• 操作前可临时关闭autovacuum，避免自动清理拖慢性能，完成后再开启
• 超大规模表可按旧ID分批处理，避免单次更新锁表时间过长
• 所有操作完成后，重建所需的索引和约束

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