current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 2 down vote favorite

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)

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
up vote 1 down vote

You could use a case statement to handle the null value before it gets passed into array_agg:

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

This way you can map any number of "keys" to the values you like

share|improve this answer
    
I prefer the method using COALESCE but surely thank, that will be useful for sure. – 발렌탕 May 25 '13 at 19:39
1  
No problem, it's your code :-) Generally speaking, both coalesce and case prove to be very useful. Unfortunately, most programmers do not even know these, because they see the database only through an ORM layer. – Beryllium May 25 '13 at 22:32
up vote 0 down vote

Use coalesce function to convert NULL to empty string. The first example becomes

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 it's NVL.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.