sql - 如何使用 postgres 像 string_agg 一样排除 array_agg 中的空值？

Question

如果我使用 array_agg 来收集名字，我的名字用逗号分隔，但是如果有一个 null 值，那个 null 也会被当作一个名字在聚集。例如:

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;

它返回 ,Larry,Phil 而不仅仅是 Larry,Phil(在我的 9.1.2 中，它显示 NULL,Larry,Phil ).

相反，如果我使用 string_agg()，它只会显示名称(没有空逗号或空值)。

问题是我在服务器上安装了 Postgres 8.4，而 string_agg() 在那里不起作用。 有没有办法让 array_agg 的工作方式类似于 string_agg() ？

Answer 1

使用 postgresql-9.3 可以做到这一点；

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;

更新:使用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;

更新 (2022-02-19):也与 postgresql-9.4 一起；

当数组中的所有值都为 null 而不是返回 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;