PostgresQL: Find array length of output from ARRAY_AGG()

user_2205916 0
Viewed 8397

How do I count the number of distinct elements in an array object, created by ARRAY_AGG() in PostgresQL? Here's a toy example for discussion purposes:

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

I have tried ARRAY_LENGTH(), LENGTH(), etc., like so:

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

But I get an error:

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

So I tried (2):

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

but I get the error:

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

the function array_length(anyarray, int) require two elements, array and dimension for example:

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

Result: 3

user_83083810

If you are only dealing with a single dimension array, cardinality() is easier to use:

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