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

With postgresql-9.3 one can do this;

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

Update: with postgresql-9.4;

SELECT g.id,
   array_agg(g.users) FILTER (WHERE g.canonical="Y") canonical_users,
   array_agg(g.users) FILTER (WHERE g.canonical="N") non_canonical_users
FROM groups g 
GROUP BY g.id;

Update (2022-02-19): also with postgresql-9.4;

This results in an empty array when all values in an array are null instead of returning null;

SELECT g.id,
  coalesce( array_agg(g.users) FILTER (WHERE g.canonical="Y"), '{}' ) canonical_users,
  coalesce( array_agg(g.users) FILTER (WHERE g.canonical="N"), '{}' ) non_canonical_users
FROM groups g 
GROUP BY g.id;

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)