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

Question

If I request 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 

Note the NULL value (this is not an empty varchar).

now if i ask like this:

 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 

NULL value not inserted.

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

..., ... (double colon only)

Answer 1

I don't have 8.4, but in later versions array_to_string ignores your NULLs, so the problem is not array_agg , it's array_to_string .

For instance:

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

This empty string is actually 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 NULL disappears in the call to array_to_string . The documentation does not indicate any specific NULL processing, but ignoring them seems reasonable, like everything else.

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

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

So maybe this will work for you:

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

Of course, this resets NULL and empty strings to the same value, which may or may not be a problem.

Answer 2

You can use the case statement to process a null value before passing it to array_agg:

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

This way you can match any number of "keys" with the values ​​you like

Answer 3

Use the coalesce function to convert NULL to an empty string. The first example is

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

and for the second example -

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

Please note: not all RDBMS support COALESCE. In Oracle, this is NVL.