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