PostgreSQL中如何对特定范围以上的行的port列逐行递增1

问题分析 #

你当前的查询使用全局row_number() over ()生成行号，导致需要更新的行是基于全表行号来累加，而非仅针对目标行从1开始计数。比如示例中id=3的行，全表行号是3，所以得到201+3=204，但预期是201+1=202。

解决方案 #

核心思路是仅对需要更新的行生成从1开始的序号，而非全表行号。通过CTE先筛选目标行并生成专属序号，再执行更新：

WITH base_value AS (
    -- 替换为你获取有效范围上限的查询，示例中为201
    SELECT 201 AS max_valid_port
),
target_updates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS increment
    FROM criml.model_master
    CROSS JOIN base_value
    WHERE port NOT IN (9000, 9001) 
      AND port > max_valid_port
)
UPDATE criml.model_master mm
SET port = b.max_valid_port + tu.increment
FROM base_value b
JOIN target_updates tu ON mm.id = tu.id;

代码说明 #

1. base_value：存储有效port范围的上限值，你可以把这里的SELECT 201替换为原来获取该值的查询逻辑。
2. target_updates：筛选出所有需要调整的行，仅对这些行按id排序生成从1开始的递增序号increment。
3. UPDATE语句：将目标行的port设置为有效上限 + 对应序号，确保第一行更新为上限+1，第二行上限+2，以此类推。

示例验证 #

针对你的示例数据：

• 有效上限是201，需要更新的只有id=3的行，其increment为1，所以port更新为201+1=202，完全符合预期。

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