Returning empty string when string_agg has no records

Question

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: ')?

Answer 1

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;

The null-safe 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.

Nested in a more efficient query (while querying all foos):

SELECT f.name AS foo
     , concat_ws(' ', 'Bazzes:', baz_agg) AS bazzes
FROM   foo f
LEFT   JOIN (
   SELECT fooid AS id
        , string_agg(b.baz, ', ') AS baz_agg
   FROM   bar b
   GROUP  BY 1
   ) b USING (id);

Why NULL?

Almost all aggregate functions return NULL if all source fields are NULL (no non-null values, to be precise) - count() being the exception for practical reasons. The manual:

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.

Related:

• How to concatenate columns in a Postgres SELECT?
• Combine two columns and add into one new column

Answer 2

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.