Postgres如何通过单条UPDATE查询自动填充Job表的order或created_at排序字段

实现方案 #

你完全可以通过单条UPDATE语句完成order字段的批量填充，不需要额外编写脚本，具体操作如下：

1. 首先给job表新增order字段，注意order是SQL保留关键字，需要用双引号包裹：

ALTER TABLE job ADD COLUMN "order" INTEGER;

2. 执行UPDATE语句完成字段填充，核心借助Postgres窗口函数和内置的ctid物理行标识实现按写入顺序、按Person分组排序赋值：

UPDATE job
SET "order" = row_num
FROM (
    SELECT
        id,
        -- 按person_id分组，每组内按行物理位置ctid升序排序，生成序列
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY ctid) AS row_num
    FROM job
) AS job_order_mapping
WHERE job.id = job_order_mapping.id;

补充说明 #

• 这里用ctid作为排序依据是因为Postgres中ctid代表行的物理存储位置，在没有做过VACUUM FULL、表重构、批量删除回填的历史表中，ctid的顺序和数据写入顺序完全一致，比依赖无ORDER BY的查询返回顺序更稳定可靠。
• 如果你的job表有自增主键ID，直接把ORDER BY ctid换成ORDER BY id即可，得到的顺序会更准确。
• 如果你确认表有过上述改动导致ctid顺序不可靠，可以替换ORDER BY后的规则为其他能佐证写入顺序的字段。
• 填充完成后建议给后续新增的Job配置order字段自动赋值规则，或者直接改用created_at字段并设置默认值为NOW()，后续查询直接ORDER BY created_at即可得到稳定的顺序。

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