PostgreSQL中将NULL数组转为空数组并执行数组追加的问题

现有一张licence表，结构及数据如下：

需求说明：

• 需要将competition_ids字段为NULL的记录更新为空数组{}，因为以下合并去重脚本仅对空数组有效，对NULL值无效：

  ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))
  

• 目标是要么先将NULL转为空数组再执行上述脚本，要么修改脚本直接支持NULL值。但当前使用的UPDATE语句无法完成NULL转空数组操作，需排查修复。

原错误UPDATE语句：

UPDATE licence 
SET competition_ids =
  (CASE
    WHEN competition_ids is NULL 
    THEN ARRAY['{}'] THEN ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
    ELSE ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
  END)
WHERE NOT competition_ids  @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';

问题排查 #

原语句存在两个关键错误：

1. CASE语法错误：分支结构混乱，WHEN后连续出现两个THEN，不符合PostgreSQL的CASE语法规则。
2. 空数组定义错误：ARRAY['{}']会创建一个包含字符串{}的单元素数组，而非真正的空数组。正确的空数组写法应为'{}'::text[]或array[]::text[]。

解决方案 #

方案一：分步更新（先转NULL为空数组，再合并） #

1. 将NULL值转为空数组

UPDATE licence
SET competition_ids = '{}'::text[]
WHERE competition_ids IS NULL
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';

2. 执行数组合并去重更新

UPDATE licence
SET competition_ids = ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))
WHERE NOT competition_ids @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';

方案二：单条语句直接处理NULL值 #

使用coalesce函数将NULL替换为空数组，一步完成更新：

UPDATE licence
SET competition_ids = ARRAY(
    SELECT DISTINCT UNNEST(array_cat(coalesce(competition_ids, '{}'::text[]), ARRAY['hijk-23lm']))
)
WHERE NOT (coalesce(competition_ids, '{}'::text[]) @> ARRAY['hijk-23lm'])
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';

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