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:                            |  &lt;== 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;


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 (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