PostgresQL:查找 ARRAY_AGG() 输出的数组长度

Question

如何计算由 ARRAY_AGG() 在 PostgresQL 中创建的数组对象中不同元素的数量？这是一个用于讨论目的的玩具示例:

SELECT ARRAY_AGG (first_name || ' ' || last_name) actors
FROM film

我试过ARRAY_LENGTH()、LENGTH()等，像这样:

SELECT ARRAY_LENGTH(a.actors)
FROM (SELECT ARRAY_AGG (first_name || ' ' || last_name) actors
      FROM film) a;

但是我得到一个错误:

function array_length(integer[]) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 208

所以我尝试了(2):

SELECT ARRAY_LENGTH( CAST(COALESCE(a.actors, '0') AS integer) )
FROM (SELECT ARRAY_AGG (first_name || ' ' || last_name) actors
      FROM film) a;

但是我得到了错误:

malformed array literal: "0"
  Detail: Array value must start with "{" or dimension information.
  Position: 119

Answer 1

函数 array_length(anyarray, int) 需要两个元素，例如数组和维度:

Select array_length(array[1,2,3], 1);

结果: 3