Sample Header Ad - 728x90

Returning empty string when string_agg has no records

9 votes
2 answers
26237 views
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: ')?
Asked by Michael Underwood (385 rep)
Apr 18, 2014, 07:37 PM
Last activity: Feb 25, 2023, 08:41 AM