PostgreSQL中为含重复值的列添加序列值的实现方法

假设你的表名为your_table，目标列名为target_column，我们可以通过窗口函数和字符串处理来批量修正重复值，以下是具体操作步骤：

1. 先验证修改结果（推荐优先执行） #

先执行查询查看待修改记录和对应的新值，确保逻辑符合预期：

SELECT 
    id, -- 假设表有主键id用于排序，保证处理顺序稳定
    target_column,
    CONCAT(
        -- 提取前缀：从开头到最后一个非数字字符
        SUBSTRING(target_column FROM '^.*[^0-9]'),
        -- 计算新后缀：原数字 + 组内序号-1，格式化为原后缀相同位数
        TO_CHAR(
            SUBSTRING(target_column FROM '[0-9]+$')::INT + (ROW_NUMBER() OVER (PARTITION BY target_column ORDER BY id) - 1),
            'FM' || REPEAT('0', LENGTH(SUBSTRING(target_column FROM '[0-9]+$')))
        )
    ) AS new_target_value
FROM your_table
WHERE target_column IN (
    -- 筛选出存在重复值的列值
    SELECT target_column
    FROM your_table
    GROUP BY target_column
    HAVING COUNT(*) > 1
)
ORDER BY target_column, id;

2. 执行批量更新 #

确认验证结果无误后，用CTE结合UPDATE语句修改重复值（仅修改每组中除第一条外的重复记录）：

WITH ranked_duplicates AS (
    SELECT 
        id,
        target_column,
        -- 给每组重复值分配序号，第一条为1，第二条为2，依此类推
        ROW_NUMBER() OVER (PARTITION BY target_column ORDER BY id) AS rn,
        SUBSTRING(target_column FROM '^.*[^0-9]') AS prefix,
        SUBSTRING(target_column FROM '[0-9]+$')::INT AS suffix_num,
        LENGTH(SUBSTRING(target_column FROM '[0-9]+$')) AS suffix_length
    FROM your_table
    WHERE target_column IN (
        SELECT target_column
        FROM your_table
        GROUP BY target_column
        HAVING COUNT(*) > 1
    )
)
UPDATE your_table t
SET target_column = CONCAT(
    rd.prefix,
    TO_CHAR(rd.suffix_num + rd.rn - 1, 'FM' || REPEAT('0', rd.suffix_length))
)
FROM ranked_duplicates rd
WHERE t.id = rd.id
AND rd.rn > 1; -- 保留每组第一条记录的原数值，仅修改后续重复项

关键说明 #

• 通用性：上述SQL支持任意长度的数字后缀（比如123P01或4567X0001格式），无需固定后缀位数。
• 稳定性：依赖主键id排序，确保每次执行时组内的序号顺序一致，避免修改结果混乱。
• 安全建议：执行UPDATE前务必备份表数据，或者开启事务执行，确认结果正确后再提交。

内容的提问来源于stack exchange，提问作者Diya Nair 11 5C