PostgreSQL空数组调用array_length、cardinality返回1的原因及解决方法

你遇到的问题通常由以下两种常见场景导致：

• 字段存储的不是真正的空数组{}，而是包含1个空字符串元素的数组{""}
  这种情况大多出现在写入数组的逻辑中，比如使用string_to_array('', ',')这类函数转换空字符串时，PostgreSQL会返回包含单个空字符串的数组，而非空数组。此时cardinality()返回值为1，available_region_code = '{}'条件自然匹配失败，是该问题的最高发原因。
• 数组维度不匹配，存储的是二维空数组{{}}
  如果写入逻辑错误将数组存为二维结构，空的二维数组第一维长度为1，第二维长度为0。若你调用array_length(available_region_code, 1)查询长度就会得到返回值1，和一维空数组的判断逻辑完全不兼容。

第一步：确认实际存储的数据结构 #

先执行以下查询确认异常行的真实数组内容，匹配对应场景：

SELECT 
  id,
  available_region_code,
  cardinality(available_region_code) as arr_total_length,
  array_length(available_region_code, 1) as arr_first_dim_length,
  array_length(available_region_code, 2) as arr_second_dim_length
FROM my_table
-- 可补充你认为应该是空数组的筛选条件
LIMIT 20;

第二步：对应场景处理 #

场景1：存储的是{""}（单空字符串元素数组） #

• 临时筛选方案：如果业务上认为此类数组等同于空数组，使用以下条件筛选：

  SELECT * FROM my_table WHERE array_remove(available_region_code, '') = '{}';
  

  如果需要同时匹配字段为NULL的情况，补充OR条件：

  SELECT * FROM my_table 
  WHERE array_remove(available_region_code, '') = '{}' OR available_region_code IS NULL;
  

• 永久修复方案：修正写入逻辑，写入前判断如果数组内容为空，直接写入'{}'::varchar[]而非通过字符串转换生成{""}，也可以对存量数据执行批量更新：

  UPDATE my_table 
  SET available_region_code = '{}'::varchar(255)[] 
  WHERE array_remove(available_region_code, '') = '{}';
  

场景2：存储的是二维空数组{{}} #

• 临时筛选方案：使用cardinality()判断数组总元素数，不受维度影响：

  SELECT * FROM my_table WHERE cardinality(available_region_code) = 0;
  

• 永久修复方案：修正写入逻辑保证所有数组为一维结构，同时修复存量数据：

  UPDATE my_table
  SET available_region_code = array_cat(available_region_code, '{}'::varchar(255)[])
  WHERE array_length(available_region_code, 2) IS NOT NULL;
  

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