PostgreSQL：使用CTE更新重复记录以添加复合唯一索引

没问题，这场景我处理过很多次，咱们用窗口函数+CTE就能优雅地解决重复name的更新问题，让它们满足organization_id和name的复合唯一约束。

核心思路 #

对于每个重复的(organization_id, name)组合，我们保留其中一条记录的原name，其他记录在name后拼接自增序号（比如-2、-3），这样就能保证每个组合的name唯一。

具体实现代码 #

先通过CTE给每条重复记录分配组内序号，再根据序号更新name：

WITH numbered_duplicates AS (
    SELECT 
        id,
        name,
        organization_id,
        -- 按组织ID和名称分组，给组内记录分配序号
        -- ORDER BY id 可以换成你需要的排序字段（比如created_at），决定保留哪条记录的原名称
        ROW_NUMBER() OVER (
            PARTITION BY organization_id, name 
            ORDER BY id
        ) AS row_num
    FROM locations
    -- 只筛选存在重复的记录组
    WHERE (organization_id, name) IN (
        SELECT organization_id, name 
        FROM locations 
        GROUP BY organization_id, name 
        HAVING COUNT(*) > 1
    )
)
UPDATE locations
SET name = CONCAT(name, '-', row_num)
FROM numbered_duplicates
WHERE locations.id = numbered_duplicates.id
AND numbered_duplicates.row_num > 1; -- 只更新组内序号>1的记录，保留第一条原名称

关键细节说明 #

• 序号分配：ROW_NUMBER()会给每个(organization_id, name)组内的记录从1开始编号，ORDER BY id确保排序稳定（你可以根据业务需求换成其他字段，比如创建时间，优先保留最早创建的记录的原名称）。
• 安全验证：执行更新前，建议先跑下面的查询确认要更新的内容是否符合预期：

SELECT id, name, CONCAT(name, '-', row_num) AS new_name
FROM numbered_duplicates
WHERE row_num > 1;

• 验证更新结果：更新完成后，检查是否还有重复记录：

SELECT name, organization_id, COUNT(*) AS duplicate_count
FROM locations
GROUP BY name, organization_id
HAVING COUNT(*) > 1;

• 创建复合唯一索引：确认无重复后，就可以创建索引了：

CREATE UNIQUE INDEX idx_locations_org_name ON locations(organization_id, name);

可选优化 #

如果你觉得序号后缀不够友好，也可以用UUID_SHORT()生成短唯一标识，或者拼接其他业务相关的字段，比如：

SET name = CONCAT(name, '-', UUID_SHORT())

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