how to exclude null values in array_agg like in string_agg using postgres?

postgresql, postgresql-8.4, postgresql-9.1, sql

Solution

select
    id,
    (select array_agg(a) from unnest(canonical_users) a where a is not null) canonical_users,
    (select array_agg(a) from unnest(non_canonical_users) a where a is not null) non_canonical_users
from (
    SELECT g.id,
           array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
           array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
    FROM groups g
    GROUP BY g.id
) s

Or, simpler and may be cheaper, using `array_to_string` which eliminates nulls:

SELECT
    g.id,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END)
        , ','
    ) canonical_users,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END)
        , ','
    ) non_canonical_users
FROM groups g
GROUP BY g.id

Problem

If I use `array_agg` to collect names, I get my names separated by commas, but in case there is a `null` value, that null is also taken as a name in the aggregate. For example : ``` SELECT g.id, array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users, array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users FROM groups g GROUP BY g.id; ``` it returns `,Larry,Phil` instead of just `Larry,Phil` (in my 9.1.2, it shows `NULL,Larry,Phil`). Instead, if I use `string_agg()`, it shows me only the names (without empty commas or nulls). The problem is that I have `Postgres 8.4` installed on the server, and `string_agg()` doesn't work there. Is there any way to make array_agg work similar to string_agg() ?

Original source