PostgreSQL：不删除重复项，按account_id重置重复identifier值

需求概述 #

需要修复books表中account_id和identifier组合的重复记录：

• 不删除任何条目
• 将重复的identifier更新为对应account_id的当前最大identifier值加1
• 循环处理所有重复项，确保最终(account_id, identifier)组合完全唯一，且不影响其他正常记录

表结构 #

books表的结构及索引定义：

#
# Table name: books
#
#  id                 :bigint           not null, primary key
#  account_id         :bigint           not null
#  identifier         :bigint           not null

# Indexes
#  unique_account_identifier             (account_id,identifier) UNIQUE

查询重复记录 #

使用以下SQL可以定位所有存在重复的account_id+identifier组合及重复次数：

select account_id, identifier, count(*)
from books                                           
group by account_id, identifier   
HAVING count(*) > 1;

查询结果示例 #

account_id | identifier | count 
------------+------------+-------
        111 |        155 |     2
        111 |        198 |     2
        111 |        178 |     2
        111 |        167 |     2
        111 |        196 |     2
        111 |        156 |     2
        111 |        150 |     2
        111 |        223 |     2

处理脚本（PostgreSQL） #

以下PL/pgSQL脚本会循环处理所有重复记录，确保最终(account_id, identifier)唯一：

DO $$
DECLARE
    rec record;
    max_id bigint;
BEGIN
    LOOP
        -- 获取当前存在的第一组重复记录
        SELECT account_id, identifier INTO rec
        FROM books
        GROUP BY account_id, identifier
        HAVING count(*) > 1
        LIMIT 1;
        
        -- 无重复则退出循环
        EXIT WHEN NOT FOUND;
        
        -- 获取该账户当前的最大identifier值
        SELECT MAX(identifier) INTO max_id FROM books WHERE account_id = rec.account_id;
        
        -- 为重复记录分配新的唯一identifier（保留一条原记录，其余依次递增）
        UPDATE books
        SET identifier = max_id + row_number() OVER (ORDER BY id)
        WHERE account_id = rec.account_id
          AND identifier = rec.identifier
          AND id NOT IN (
              SELECT id 
              FROM books 
              WHERE account_id = rec.account_id AND identifier = rec.identifier 
              LIMIT 1
          );
    END LOOP;
END $$;

脚本说明 #

1. 循环扫描重复记录，每次处理一组
2. 对每组重复，先获取对应账户的当前最大identifier
3. 保留该组中的一条记录（按主键id排序的第一条），其余记录的identifier从max_id+1开始依次递增分配
4. 直到所有重复组合都被修复，循环自动退出

处理效果示例 #

以account_id=111的重复记录identifier=155为例：

• 处理前该组合重复次数为2
• 该账户当前最大identifier为223
• 处理后两条记录的identifier分别为155和224，重复消除：

account_id | identifier | count 
------------+------------+-------
        111 |        155 |     1
        111 |        224 |     1

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