PostgreSQL存量表新增not null唯一字段的数据库迁移方案

你设想的三步方案算不上好的实践，最大的问题是手动填充存量数据的步骤完全脱离了迁移工具的版本管控：不管是开发、测试环境重置，还是新生产环境搭建，Flyway只会跑你提交的版本脚本，不会执行你手动敲的更新语句，到第二步加非空约束的时候直接因为字段全是空值报错，环境一致性根本兜不住。另外手动改数没有事务保障、没有操作审计，漏更、错更的概率很高，出问题很难回溯。

为什么你最初的脚本跑不通 #

直接执行ALTER TABLE t1 ADD COLUMN code2 int NOT NULL时，PostgreSQL会检查全表现有行的新字段值，你没有配置默认值，存量行的新字段都是NULL，自然会触发非空约束报错。

推荐的全版本化迁移方案 #

所有逻辑全部写进Flyway的版本脚本里，不需要任何手动操作，小表可以直接跑，大表也能通过调整执行方式避免长时间锁表，步骤如下：

1. 第一步：新增允许为空的code2字段
   这个操作在PostgreSQL 11及以上版本是秒级完成的，不会重写全表数据，几乎不持有锁：

   ALTER TABLE t1 ADD COLUMN code2 int;
   

2. 第二步：回填存量数据，创建唯一索引
   不要手动填值，把code2的赋值逻辑直接写在SQL里：如果code2和现有字段有映射规则、或者需要从关联表取值，直接在UPDATE语句里实现即可。
   • 万级以下的小表：直接全量更新就行，不用搞复杂逻辑

     -- 替换成你实际的业务赋值逻辑
     UPDATE t1 SET code2 = <你的业务赋值规则，例如和code1做映射、从关联表查询等>;
     -- 直接创建唯一索引
     CREATE UNIQUE INDEX idx2 ON t1(code2, company_id);
     

   • 百万级以上的大表：建议分批更新避免长事务锁表，索引用CONCURRENTLY模式创建，不会阻塞正常读写：

     -- 分批更新，每批1000条，根据实际数据量调整批次大小
     DO $$
     DECLARE
         max_id bigint;
         batch_size int := 1000;
         current_id bigint := 0;
     BEGIN
         SELECT MAX(id) INTO max_id FROM t1;
         WHILE current_id < max_id LOOP
             UPDATE t1
             SET code2 = <你的业务赋值规则>
             WHERE id > current_id AND id <= current_id + batch_size;
             COMMIT;
             current_id := current_id + batch_size;
         END LOOP;
     END $$;
     
     -- CONCURRENTLY模式建索引不会锁表，注意不要放在事务块里执行
     CREATE UNIQUE INDEX CONCURRENTLY idx2 ON t1(code2, company_id);
     

   回填完成后可以先查一遍有没有code2 IS NULL的行、有没有(code2, company_id)重复的行，提前处理异常值，避免后续步骤报错。

3. 第三步：给字段加非空约束
   确认所有存量行的code2都已经赋值完成后，再给字段加NOT NULL属性：

   ALTER TABLE t1 ALTER COLUMN code2 SET NOT NULL;
   

   如果是超大表担心加约束时的扫描锁表，可以先用NOT VALID模式加约束，再单独做校验，把锁影响降到最低：

   ALTER TABLE t1 ALTER COLUMN code2 SET NOT NULL NOT VALID;
   ALTER TABLE t1 VALIDATE CONSTRAINT t1_code2_not_null;
   

关键提醒 #

• 绝对不要在迁移脚本之外手动修改库结构或者数据，所有变更必须进版本控制，这是数据库迁移最基本的原则，不然哪天环境重建、灾备切换的时候，漏了步骤就是生产事故。
• 迁移上线后，要确认业务代码写入t1表的时候一定会给code2赋值，避免新写入的数据触发非空报错。如果后续确定有通用默认值，再加DEFAULT属性即可，PostgreSQL 11+版本加字段默认值也是秒级操作，不会锁表。

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