如何用PostgreSQL或Prisma让序号始终保持连续？

问题背景 #

初始数据：

删除number=2的行后数据：

需要将number列恢复为连续的1、2，达到预期效果：

方案一：PostgreSQL原生SQL实现 #

利用窗口函数ROW_NUMBER()重新生成连续序号，结合CTE（公共表表达式）完成批量更新：

假设表名为your_table，且表存在唯一主键id（用于精准定位每行数据），执行以下SQL：

WITH ranked_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY code ORDER BY number) AS new_number
    FROM your_table
    WHERE code = 'A123'
)
UPDATE your_table t
SET number = r.new_number
FROM ranked_records r
WHERE t.id = r.id;

说明： #

• PARTITION BY code：确保仅对同一code分组内的序号重新计算
• ORDER BY number：保留原有记录的排序顺序（若需按其他字段排序，替换为对应字段即可，比如created_at）
• 如果需要对所有code的序号都进行连续化，去掉WHERE code = 'A123'条件即可

方案二：Prisma实现 #

通过Prisma Client查询指定code的记录，按原序号排序后逐个更新：

// 假设Prisma模型定义为YourTable，包含id、code、number、description字段
async function resetContinuousNumber(code) {
    // 查询指定code的记录，按原number升序排列
    const targetRecords = await prisma.yourTable.findMany({
        where: { code },
        orderBy: { number: 'asc' }
    });

    // 批量更新序号，索引+1得到连续的1、2、3...
    await Promise.all(
        targetRecords.map((record, index) => 
            prisma.yourTable.update({
                where: { id: record.id },
                data: { number: index + 1 }
            })
        )
    );
}

// 调用函数处理code=A123的记录
resetContinuousNumber('A123');

说明： #

• 如果表没有id主键，可使用复合唯一标识作为where条件，比如{ code, description }（需确保组合唯一）
• 若需批量处理所有code，可先查询所有distinct code，再循环调用该函数

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