how can I include a NULL value using array_agg in postgresql?

Question

If I query this :

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);

I get :

      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

Notice the NULL value (it's not a empty varchar).

now if I query like that :

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);

I get :

      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

The NULL value is not inserted.

Is there any way to include it ? I mean having something like :

...,,... (just a double colon)

Answer 1

I don't have an 8.4 handy but in more recent versions, the array_to_string is ignoring your NULLs so the problem isn't array_agg, it is array_to_string.

For example:

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

 success
 failure

That blank line is in fact a NULL. Then we can see what array_agg and array_to_string do with this stuff:

=> 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

And the NULL disappears in the array_to_string call. The documentation doesn't specify any particular handling of NULLs but ignoring them seems as reasonable as anything else.

In version 9.x you can get around this using, as usual, COALESCE:

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

So perhaps this will work for you:

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

Of course that will fold NULLs and empty strings into one value, that may or may not be an issue.