sql - 如何在 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 值。

有什么方法可以包含它吗？我的意思是有类似的东西:

...,,...(只是一个双冒号)

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

并且 NULL 在 array_to_string 调用中消失了。 documentation没有指定对 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 和空字符串折叠成一个值，这可能是也可能不是问题。