Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to return a text field in a PostgreSQL query that is of the form

'stringOne' || string_agg(field, ',') || 'stringTwo'

where for certain elements in the group clause, field is always null. I want, and expect, to end up with stringOnestringTwo in that case, but instead I get NULL.

Why is this, and how do I accomplish what I'm trying to do?

Example

Suppose I have the tables

foo                 bar
+----+--------+     +----+-------+--------------+
| id | name   |     | id | fooid | baz          |
+----+--------+     +----+-------+--------------+
|  1 | FooOne |     |  1 |     1 | FooOneBazOne |
|  2 | FooTwo |     |  2 |     1 | FooTwoBazTwo |
+----+--------+     +----+-------+--------------+

and I run the query

SELECT
  foo.name AS foo,
  'Bazzes: ' || string_agg(bar.baz, ', ') AS bazzes
FROM
  foo LEFT JOIN bar ON bar.fooid = foo.id
GROUP BY
  foo.name

Then I want (and expect) to get the resultset

+--------+------------------------------------+
| foo    | bazzes                             |
+--------+------------------------------------+
| FooOne | Bazzes: FooOneBazOne, FooOneBazTwo |
| FooTwo | Bazzes:                            |  <== NOT NULL
+--------+------------------------------------+

but instead, the second row is ('FooTwo', NULL). How can I modify this query so that the second row returns ('FooTwo', 'Bazzes: ')?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Use COALESCE to catch and replace NULL values:

SELECT f.name AS foo
     , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes
FROM   foo f
LEFT   JOIN bar b ON b.fooid = f.id
GROUP  BY 1;

concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with separator"), though, to avoid the trailing space.

concat_ws(' ', 'Bazzes:', string_agg(b.baz, ', ')) AS bazzes

Why NULL?

Almost all aggregate function return NULL if all source fields are NULL, count() being the exception for practical reasons. Per documentation:

It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. The coalesce function can be used to substitute zero or an empty array for null when necessary.

share|improve this answer
    
I agree concat is perhaps more useful when joining many values, but this most directly answers my original question of how... Do you know why, though, string_agg coerces all strings joined to it to NULL, instead of casting a null aggregate value to ''? –  Michael Underwood Apr 21 at 17:29
    
@MichaelUnderwood: I added a bit to address the why. –  Erwin Brandstetter Apr 22 at 0:57
    
Thank you, @ErwinBrandstetter. The fact that the result of the aggregation is null makes sense to me. It's the fact that ('not null string' || NULL) IS NULL instead of ('not null string' || NULL) == 'not null string' that confused me. As far as I can tell, that's generically true and not an artefact of the aggregate function, but it surprised me when I first saw the behaviour. –  Michael Underwood Apr 22 at 14:37
    
@MichaelUnderwood: That's general NULL behavior: (anything || NULL) IS NULL. –  Erwin Brandstetter Apr 22 at 14:47

The simplest method I have found to accomplish this is to swap out the string concatenation operator for the string concatenation function concat(). For some reason the former apparently coerces the entire result to NULL if one operand is null, as opposed to the latter which effectively casts any NULL arguments to ''.

So this query performs as desired:

SELECT
  foo.name,
  concat('Bazzes: ', string_agg(bar.baz, ', ')) AS bazzes
FROM
  foo LEFT JOIN bar ON bar.fooid = foo.id
GROUP BY
  foo.name

It returns the desired result set:

+--------+------------------------------------+
| foo    | bazzes                             |
+--------+------------------------------------+
| FooOne | Bazzes: FooOneBazOne, FooOneBazTwo |
| FooTwo | Bazzes:                            |
+--------+------------------------------------+

Another option

A second approach is slightly more involved, but I found it first and it may be relevant in other situations, so I'll document it here as well. A CASE statement can be used to count the number of elements before passing them to the aggregation function, and return an empty string instead of NULL when the answer is zero.

In the original query, replace

string_agg(bar.baz, ', ')

with

CASE count(bar.baz) WHEN 0 THEN '' ELSE string_agg(bar.baz, ', ') END

and the string_agg function will only be called when there is at least one string to aggregate. The empty string will be returned and combined correctly with the rest of the text otherwise, instead of coercing the whole result to NULL.

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.