如何将表中重复number值更新为下一个可用整数？

给定如下表结构：

CREATE TABLE "table" (
    "index" serial PRIMARY KEY,
    "number" integer
);

已插入的数据：

INSERT INTO "table" ("number") VALUES
(1), (2), (2), (3), (4), (4), (4), (5), (13), (13), (17);

需求是：按照行的插入顺序（由index字段决定），将重复或无法满足递增的数值（除首次出现的有效递增数值外）更新为下一个可用整数，最终得到如下结果：

(1), (2), (3), (4), (5), (6), (7), (8), (13), (14), (17)

解决方案 #

使用PostgreSQL的递归CTE实现依赖前序结果的递推更新逻辑，具体语句如下：

WITH RECURSIVE ranked_rows AS (
    -- 按index排序并为每行分配连续行号
    SELECT 
        "index",
        "number",
        ROW_NUMBER() OVER (ORDER BY "index") AS rn
    FROM "table"
),
updated_values AS (
    -- 初始化第一行的数值
    SELECT 
        rn,
        "index",
        "number" AS new_number
    FROM ranked_rows
    WHERE rn = 1
    UNION ALL
    -- 递归计算后续每行的新数值
    SELECT 
        rr.rn,
        rr."index",
        CASE 
            WHEN rr."number" > uv.new_number THEN rr."number"
            ELSE uv.new_number + 1
        END AS new_number
    FROM ranked_rows rr
    JOIN updated_values uv ON rr.rn = uv.rn + 1
)
-- 将计算出的新数值更新回原表
UPDATE "table" t
SET "number" = uv.new_number
FROM updated_values uv
WHERE t."index" = uv."index";

逻辑说明 #

1. ranked_rows CTE：按主键index对所有行排序，分配连续行号rn，确保处理顺序与插入顺序一致。
2. updated_values 递归CTE：
   • 初始分支：取第一行的原始数值作为初始新数值。
   • 递归分支：依次处理后续每行，若当前行原始数值大于前一行的新数值则保留，否则用前一行新数值加1作为当前行的新数值。
3. UPDATE语句：将递归计算得到的新数值批量更新回原表。

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