如何在PostgreSQL中使用array_agg包含NULL值？

Question

如果我查询这个：

SELECT DISTINCT class_low
FROM groups NATURAL JOIN species
WHERE type ~~ 'faune'
AND class_high ~~ 'Arachnides'
AND (class_middle ~~ 'Araignées' OR class_middle IS NULL)
AND (class_low ~~ '%' OR class_low IS NULL);

我理解为：

      class_low      
---------------------
 Dictynidés
 Linyphiidés

 Sparassidés
 Metidés
 Thomisidés
 Dolomedidés
 Pisauridés
 Araignées sauteuses
 Araneidés
 Lycosidés
 Atypidés
 Pholcidés
 Ségestriidés
 Tetragnathidés
 Miturgidés
 Agelenidés

注意 NULL 值（它不是空 varchar）。

现在如果我这样查询：

SELECT array_to_string(array_agg(DISTINCT class_low), ',')
FROM groups NATURAL JOIN species
WHERE type ~~ 'faune'
AND class_high ~~ 'Arachnides'
AND (class_middle ~~ 'Araignées' OR class_middle IS NULL)
AND (class_low ~~ '%' OR class_low IS NULL);

我得到：

      array_to_string                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Agelenidés,Araignées sauteuses,Araneidés,Atypidés,Dictynidés,Dolomedidés,Linyphiidés,Lycosidés,Metidés,Miturgidés,Pholcidés,Pisauridés,Ségestriidés,Sparassidés,Tetragnathidés,Thomisidés

NULL值不会被插入。

是否有办法包括它？我的意思是像这样有一个东西：

... :: ...（只是双冒号）

- vdegenne

Answer 1

我没有8.4版本的手头资料，但在更近期的版本中，array_to_string会忽略你的NULL值，所以问题不在于array_agg，而是在于array_to_string。

例如：

=> select distinct state from orders;
  state  
---------

 success
 failure

事实上，那个空行其实是一个NULL。接下来我们可以看看array_agg和array_to_string对这个东西做了什么：

=> select array_agg(distinct state) from orders;
       array_agg        
------------------------
 {failure,success,NULL}

=> select array_to_string(array_agg(distinct state), ',') from orders;
 array_to_string 
-----------------
 failure,success

在array_to_string函数调用中，NULL会被删除。虽然文档没有指定对NULL的特殊处理方式，但忽略它们似乎与其他任何方法一样合理。

在9.x版本中，您可以像往常一样使用COALESCE来解决这个问题:

=> select array_to_string(array_agg(distinct coalesce(state, '')), ',') from orders;
 array_to_string  
------------------
 ,failure,success

也许这对你有用：

所以也许这对你有用：

array_to_string(array_agg(DISTINCT coalesce(class_low, '')), ',')

当然，这将把NULL和空字符串合并为一个值，这可能是一个问题，也可能不是。

- mu is too short

Answer 2

您可以使用一个case语句来处理传递到array_agg之前的null值:

select
  array_to_string(array_agg(case xxx 
                              when null then 'whatever' 
                              when ''   then 'foo'
                            else xxx end), ', ')

这样，您可以将任意数量的“键”映射到所需的值。

- Beryllium

---

我更喜欢使用COALESCE方法，但是感谢您的建议，肯定会很有用。 - vdegenne

没问题，这是你的代码 :-) 一般来说，coalesce和case都非常有用。不幸的是，大多数程序员甚至不知道这些，因为他们只通过ORM层看到数据库。 - Beryllium

Answer 3

使用coalesce函数将NULL转换为空字符串。第一个示例变为

SELECT DISTINCT COALESCE(class_low, '')
FROM groups NATURAL JOIN species
WHERE type ~~ 'faune'
AND class_high ~~ 'Arachnides'
AND (class_middle ~~ 'Araignées' OR class_middle IS NULL)
AND (class_low ~~ '%' OR class_low IS NULL);

而对于第二个例子 -

SELECT array_to_string(array_agg(DISTINCT COALESCE(class_low, '')), ',')
FROM groups NATURAL JOIN species
WHERE type ~~ 'faune'
AND class_high ~~ 'Arachnides'
AND (class_middle ~~ 'Araignées' OR class_middle IS NULL)
AND (class_low ~~ '%' OR class_low IS NULL);

请注意 - 并非所有的关系型数据库管理系统都支持COALESCE函数。在Oracle中，使用NVL函数代替。

- Stefanov.sm