PostgreSQL不依赖序列为存量表新增唯一非空列的实现方法

实现可行性说明 #

你示例中直接将ROW_NUMBER()放在列默认值的写法无法直接生效：DEFAULT子句仅支持针对单行计算的表达式，不支持ROW_NUMBER()这类需要扫描全表的窗口函数逻辑。你可以通过分三步操作实现需求，无需创建序列：

具体操作步骤 #

• 第一步：先新增可空的目标列，暂时不添加非空、唯一约束

ALTER TABLE 你的表名 ADD COLUMN foo text;

• 第二步：批量更新存量数据，用ROW_NUMBER()生成符合要求的唯一临时值

UPDATE 你的表名
SET foo = 'ABC' || (ROW_NUMBER() OVER (ORDER BY 表内已有的唯一稳定列，例如主键ID))::TEXT;

你可以自行选择ORDER BY的排序列，只要能保证生成的行号唯一即可，最终得到的就是ABC123、ABC124这类格式的唯一值。

• 第三步：所有行填充完成后，再给列加上非空、唯一约束

-- 设置非空
ALTER TABLE 你的表名 ALTER COLUMN foo SET NOT NULL;
-- 添加唯一约束
ALTER TABLE 你的表名 ADD CONSTRAINT uk_表名_foo UNIQUE(foo);

后续替换说明 #

后续你需要替换为正式数据时，只要保证替换过程中值的唯一性、非空性，不会触发约束报错，也可以根据需求随时删除该约束后再做批量修改。

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