PostgreSQL表已有数据时，如何为已删除非空约束的列重新添加NOT NULL约束

前提：直接对存在NULL值的列添加NOT NULL约束会触发PostgreSQL全表校验，报错中断操作，你需要先完成存量空值的处理，再执行约束添加操作。

步骤1：补全存量NULL值 #

根据业务需求填充该列的空值，示例为填充空字符串：

UPDATE 你的表名 SET columnname = '' WHERE columnname IS NULL;

如果不同行需要填充差异化的值，自行调整UPDATE的条件逻辑即可。

步骤2：确认空值已全部处理 #

执行以下查询，确认返回结果为0：

SELECT COUNT(*) FROM 你的表名 WHERE columnname IS NULL;

返回值为0代表该列已无NULL值，可进入下一步。

步骤3：添加NOT NULL约束 #

执行ALTER语句添加约束：

ALTER TABLE 你的表名 ALTER COLUMN columnname SET NOT NULL;

注意：该操作会获取表的ACCESS EXCLUSIVE锁，全表扫描校验数据期间会阻塞所有对该表的读写请求，数据量大的表请在业务低峰期操作，提前预估耗时。

大表低影响优化方案（避免长时间锁表） #

如果你的表数据量极大，无法接受长时间锁表，可以用以下分步操作降低对业务的影响：

• 先添加带NOT VALID标记的CHECK约束，该操作仅校验后续新增、修改的数据，不会扫描存量数据，不会长时间锁表：

ALTER TABLE 你的表名 ADD CONSTRAINT columnname_not_null CHECK (columnname IS NOT NULL) NOT VALID;

• 分批补全存量NULL值，直到所有行的该列都不为空
• 执行约束校验，该操作仅加SHARE UPDATE EXCLUSIVE锁，不会阻塞正常的表读写：

ALTER TABLE 你的表名 VALIDATE CONSTRAINT columnname_not_null;

• 正式添加NOT NULL约束，此时PostgreSQL会识别到已有生效的非空校验约束，无需再次全表扫描，瞬间完成操作：

ALTER TABLE 你的表名 ALTER COLUMN columnname SET NOT NULL;

• 清理临时创建的CHECK约束：

ALTER TABLE 你的表名 DROP CONSTRAINT columnname_not_null;

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