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;