PostgreSQL添加非空列后更新触发NOT NULL约束错误排查

错误核心原因 #

当你给my_table添加带NOT NULL约束的my_col1时，PostgreSQL会立刻对表中所有现有行执行约束校验——此时新列还未被任何数据填充，所有行的my_col1都是NULL，直接触发约束报错，后续的UPDATE语句根本没有执行的机会。

解决方案 #

方案1：先加无约束列，填充后再加约束

这是最稳妥的方式，步骤如下：

1. 先添加不带NOT NULL约束的列：

   ALTER TABLE my_table 
   ADD COLUMN my_col1 [你的数据类型],
   ADD COLUMN my_col2 [你的数据类型];
   

2. 执行UPDATE从JSON字段提取值填充新列：

   UPDATE my_table
   SET my_col1 = my_json_col->>'目标键名',
       my_col2 = my_json_col->>'另一键名';
   

3. 确认数据填充完成后，给my_col1添加NOT NULL约束：

   ALTER TABLE my_table ALTER COLUMN my_col1 SET NOT NULL;
   

方案2：临时用默认值过渡（仅当JSON字段对应值确定非空时适用）

如果能确保my_json_col中提取的my_col1值永远不为NULL，可以用临时默认值绕过添加列时的约束检查：

1. 添加带临时默认值和NOT NULL约束的列：

   -- 示例：如果是文本类型，用空字符串当临时默认值，需匹配实际数据类型
   ALTER TABLE my_table 
   ADD COLUMN my_col1 TEXT NOT NULL DEFAULT '',
   ADD COLUMN my_col2 [你的数据类型];
   

2. 填充真实数据：

   UPDATE my_table SET my_col1 = my_json_col->>'目标键名';
   

3. （可选）如果不需要默认值，移除临时默认值：

   ALTER TABLE my_table ALTER COLUMN my_col1 DROP DEFAULT;
   

关于子查询单独执行无问题的说明 #

单独执行UPDATE的子查询时，你查询的是my_json_col中的现有值，此时要么还没添加带约束的my_col1，要么是在假设数据填充后的状态，和添加列时PostgreSQL即时校验约束的时机完全不同，所以不会出现NULL报错。

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