PostgreSQL按指定列排序为表新增有序唯一值列的实现方法

解决方案 #

步骤1：新增id列 #

首先给表添加整数类型的id列，暂不设置约束：

ALTER TABLE db1 ADD COLUMN id INT;

步骤2：按指定规则给id赋值 #

利用PostgreSQL内置的ctid行唯一标识，配合窗口函数生成符合排序要求的唯一值更新到id列：

UPDATE db1
SET id = t.row_num
FROM (
    SELECT 
        ctid,
        row_number() OVER (ORDER BY member ASC, service_date ASC) AS row_num
    FROM db1
) t
WHERE db1.ctid = t.ctid;

该语句无需重建表，直接在原表上更新，适配大体积业务表的需求。

步骤3（可选）：添加约束或设置自增 #

如果需要保证id列后续的唯一性、或者实现新插入数据自动生成id，可执行以下操作：

-- 设置id列为非空
ALTER TABLE db1 ALTER COLUMN id SET NOT NULL;
-- 可选：设为正式主键
ALTER TABLE db1 ADD PRIMARY KEY (id);
-- 可选：绑定自增序列，后续新数据自动从当前最大id开始递增
CREATE SEQUENCE db1_id_seq START WITH (SELECT MAX(id) + 1 FROM db1);
ALTER TABLE db1 ALTER COLUMN id SET DEFAULT nextval('db1_id_seq');

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