PostgreSQL中移除ID重复记录：保留单条重复数据的方法

针对你遇到的primary_location表中存在ID（及其他字段）完全重复的记录，且暂时无法设置唯一约束的场景，以下是两种可靠的解决方案：

方法一：窗口函数标记删除重复项 #

通过ROW_NUMBER()窗口函数为每组重复数据添加序号，删除序号大于1的记录即可保留单条：

WITH ranked_duplicates AS (
    SELECT 
        id, 
        name, 
        point, 
        closest_community_id,
        -- 按重复字段分组，为每条记录分配序号
        ROW_NUMBER() OVER (
            PARTITION BY id, name, point, closest_community_id 
            ORDER BY (SELECT NULL) -- 无特定排序需求时用此，随机保留一条
        ) AS rn
    FROM primary_location
)
DELETE FROM primary_location
USING ranked_duplicates
WHERE 
    primary_location.id = ranked_duplicates.id
    AND primary_location.name = ranked_duplicates.name
    AND primary_location.point = ranked_duplicates.point
    AND primary_location.closest_community_id = ranked_duplicates.closest_community_id
    AND ranked_duplicates.rn > 1;

提示：如果需要按特定规则保留记录（比如保留最新创建的），将ORDER BY (SELECT NULL)替换为实际排序字段，例如ORDER BY created_at DESC。

方法二：通过主键筛选保留（表含主键时适用） #

如果表有自增主键或唯一标识字段（假设为location_id），可以直接筛选每组重复中要保留的记录，删除其余：

DELETE FROM primary_location
WHERE location_id NOT IN (
    -- 保留每组中主键最大的记录，也可替换为MIN()取最小
    SELECT MAX(location_id)
    FROM primary_location
    GROUP BY id, name, point, closest_community_id
);

重要提示 #

1. 操作前务必验证：先执行查询确认要删除的记录，避免误删：

   -- 验证方法一的待删记录
   SELECT * FROM ranked_duplicates WHERE rn > 1;
   -- 验证方法二的待删记录
   SELECT * FROM primary_location WHERE location_id NOT IN (SELECT MAX(location_id) FROM primary_location GROUP BY id, name, point, closest_community_id);
   

2. 仅查询去重结果的方案：如果不需要修改原表，直接用DISTINCT获取去重数据：

   SELECT DISTINCT id, name, point, closest_community_id FROM primary_location;
   

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