PostgreSQL为表id列填充1-130递增序列时全为1的解决方法

错误本质 #

• 你的SQL在FROM子句引入了生成序列的派生表t，但完全没有建立原表bar和派生表t之间的行级匹配关联。
• 这种无关联条件的写法会触发交叉匹配：bar的每一行都会和t的全部130个序列值形成匹配对。PostgreSQL在处理这类多匹配场景时，不会自动把序列值逐行分配给不同的表记录，而是会从匹配结果集中取固定的单个值（本例中恰好取到了序列起始值1）赋值给所有待更新行，最终造成全表id均为1的结果。

正确实现代码 #

核心思路是给bar的每一条原有记录打上唯一的连续行号作为关联锚点，让序列值和行号一一对应匹配，避免交叉匹配的问题。

UPDATE bar
SET id = t.num
FROM (
    SELECT
        row_number() OVER () AS match_rn,
        generate_series(1, 130) AS num
) t
JOIN (
    -- 为原表的每一行分配独立行号，作为关联匹配的主键
    SELECT row_number() OVER () AS match_rn FROM bar
) bar_rows
ON t.match_rn = bar_rows.match_rn

补充说明 #

• 如果你需要按特定顺序填充id（比如按某个业务列的排序依次赋值），可以在两个子查询的窗口函数中指定排序规则，例如将row_number() OVER ()替换为row_number() OVER (ORDER BY 你的排序列名)，即可实现按指定顺序生成连续id。
• 执行前建议先运行SELECT count(*) FROM bar确认表的总行数和generate_series(1,130)的范围完全一致，避免出现部分行未被更新、id重复或跳号的问题。

不建议使用表的物理存储标识（如ctid）做关联，这类标识的顺序会受VACUUM、表碎片等因素影响，可靠性较差。

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