PostgreSQL删除users表重复条目时如何跳过有外键关联的记录

实现思路 #

• 先筛选出所有被devices表关联的users.id，这部分记录直接排除在删除范围外，从根源避免外键约束报错
• 对users表按重复判定字段（name、email、role_id）分组，为每个重复组分配一条需要保留的记录
• 重复组内如果有被外键关联的记录则优先保留，无关联记录则默认保留ID最小的条目（可自行调整为保留ID最大的最新条目）

具体实现步骤 #

第一步：先查询确认待删除记录（避免误删） #

SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM devices) -- 排除被外键关联的用户
AND id NOT IN (
    -- 提取每个重复组需要保留的用户ID
    SELECT COALESCE(
        -- 优先取组内被外键关联的用户ID
        (SELECT u2.id FROM users u2 WHERE u2.name = u1.name AND u2.email = u1.email AND u2.role_id = u1.role_id AND u2.id IN (SELECT DISTINCT user_id FROM devices) LIMIT 1),
        MIN(u1.id) -- 无关联用户则取最小ID的条目保留
    ) AS keep_id
    FROM users u1
    GROUP BY u1.name, u1.email, u1.role_id
    HAVING COUNT(*) > 1
);

第二步：确认待删除数据无误后执行删除 #

DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM devices)
AND id NOT IN (
    SELECT COALESCE(
        (SELECT u2.id FROM users u2 WHERE u2.name = u1.name AND u2.email = u1.email AND u2.role_id = u1.role_id AND u2.id IN (SELECT DISTINCT user_id FROM devices) LIMIT 1),
        MIN(u1.id)
    ) AS keep_id
    FROM users u1
    GROUP BY u1.name, u1.email, u1.role_id
    HAVING COUNT(*) > 1
);

效果说明 #

以你提供的示例数据为例：

• 被devices表关联的用户ID为6，会被直接保留
• 同分组内的ID 5、2512未被关联，会被判定为待删除记录，执行删除后仅保留ID=6的条目，完全符合需求。

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