Returning empty string when string_agg has no records

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

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

StackExchange Database Administrators Q#63482, answer score: 9