PostgreSQL：使用array_append时自动创建不存在的数组

问题分析 #

当groups字段为NULL时，原UPDATE语句无法执行的原因有两点：

1. NOT (groups @> ARRAY[?]::INT[])在groups为NULL时结果为NULL，不满足WHERE条件，导致UPDATE不会触发；
2. 即使执行array_append(groups, ?)，传入NULL时函数仍返回NULL，无法创建新数组。

解决方案一：修改UPDATE语句 #

直接在语句中处理NULL情况，将NULL转换为空数组，并调整WHERE条件包含NULL场景：

UPDATE users
SET groups = array_append(COALESCE(groups, '{}'::INT[]), ?)
WHERE (groups IS NULL OR NOT (groups @> ARRAY[?]::INT[]))
  AND id = ?

• COALESCE(groups, '{}'::INT[])：将NULL替换为空整数数组，确保array_append能正常添加元素；
• groups IS NULL OR NOT (groups @> ARRAY[?]::INT[])：覆盖两种需要更新的场景——字段未初始化（NULL），或字段中不存在目标元素。

解决方案二：设置字段默认值（推荐） #

如果业务允许，可以给groups字段设置空数组作为默认值，从根源避免NULL问题：

1. 修改字段默认值：

ALTER TABLE users
ALTER COLUMN groups SET DEFAULT '{}'::INT[];

2. 批量更新已有的NULL值为默认空数组：

UPDATE users
SET groups = '{}'::INT[]
WHERE groups IS NULL;

完成后，原UPDATE语句即可正常处理所有用户，无需额外修改。

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