postgresql from Postgres returns[null] instead of for array_agg of join table
postgres coalesce (4)
I found out that this will do it:
tags.tag is a text type.
Not sure if maybe this would not work in older Postgres versions, but I am using it in ver. 9.6 and it seems to be working and less cumbersome than the
CASE WHEN x IS NULL... GROUP BY... solution provided earlier.
I'm selecting some objects and their tags in Postgres. The schema is fairly simple, three tables:
id | object_id | tag_id
id | tag
I'm joining the tables like this, using
array_agg to aggregate the tags into one field:
SELECT objects.*, array_agg(tags.tag) AS tags, FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id
However, if the object has no tags, Postgres returns this:
[ null ]
instead of an an empty array. How can I return an empty array when there are no tags? I have double checked that I don't have a null tag being returned.
The aggregate docs say "The coalesce function can be used to substitute zero or an empty array for null when necessary". I tried
COALESCE(ARRAY_AGG(tags.tag)) as tags but it still returns an array with null. I have tried making the second parameter numerous things (such as
COALESCE(ARRAY_AGG(tags.tag), ARRAY()), but they all result in syntax errors.
Since 9.4 one can restrict an aggregate function call to proceed only rows that match a certain criterion:
array_agg(tags.tag) filter (where tags.tag is not null)
Another option might be
array_remove(..., NULL) (introduced in 9.3) if
NOT NULL (otherwise you might want to keep
NULL values in the array, but in that case, you can't distinguish between a single existing
NULL tag and a
NULL tag due to the
SELECT objects.*, array_remove(array_agg(tags.tag), NULL) AS tags, FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id
If no tags are found, an empty array is returned.
The documentation says that an array containing
NULL is returned. If you want to convert that to an empty array, then you need to do some minor magic:
SELECT objects.id, CASE WHEN length((array_agg(tags.tag))) > 0 THEN array_agg(tags.tag) ELSE ARRAY::text END AS tags FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id GROUP BY 1;
This assumes that the tags are of
text type (or any of its variants); modify the cast as required.
The trick here is that the first (and only) element in a
[NULL] array has a length of 0, so if any data is returned from
tags you return the aggregate, otherwise construct an empty array of the right type.
Incidentally, the statement in the documentation about using
coalesce() is a bit crummy: what is meant is that if you do not want
NULL as a result, you can use
coalesce() to turn that into a
0 or some other output of your choosing. But you need to apply that to the array elements instead of the array, which, in your case, would not provide a solution.