PostgresQL: Find array length of output from ARRAY_AGG()

Question

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

You could just use a COUNT at the same level as the ARRAY_AGG. Something like: SELECT ARRAY_AGG (first_name || ' ' || last_name) actors, COUNT(*) FROM film — jmelesky
– jmelesky, Commented Jul 2, 2019 at 23:15
@jmelesky that doesn't work for my actual use case. There are too many other columns that must be grouped by if I use COUNT(*) so the result is just 1 even if there is more than 1 element in the array. — user2205916
– user2205916, Commented Jul 3, 2019 at 0:08
Unrelated, but: concat_ws(' ', first_name, last_name) is a bit easier to use and will properly deal with null values and empty strings — user330315
– user330315, Commented Jul 3, 2019 at 5:13

Anthony Sotolongo · Accepted Answer · 2019-07-03 00:16:37Z

17

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

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

Result: 3

answered Jul 3, 2019 at 0:16

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2019-07-03 05:11:56Z

8

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;

answered Jul 3, 2019 at 5:11

user330315

1 Comment

Jellicle Over a year ago

This looks great. Did you mean SELECT cardinality(a.actors) instead of SELECT cardinality(ARRAY_LENGTH(a.actors))? (The former worked for me and not the latter.)

Collectives™ on Stack Overflow

PostgresQL: Find array length of output from ARRAY_AGG()

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related