I have a query like this:

select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;

But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 for that case so that this would get sorted to the bottom.

I'm probably not understanding array_length() (ok, definitely) but:

embers_dev2=# select array_length(array[1,2], 1), array_length(ARRAY[]::integer[],1);

enter image description here

should return 0 rather than nothing (NULL), right?

Could I do an if statement like inline on it?


According to this related answer on SO, it looks like COALESCE might be what I want - but I am open to better ideas:


1 Answer 1


should return 0 rather than nothing (NULL), right?

It's up for debate what this should return. But the way Postgres defines it, the result for any dimension that does not exist is NULL.

COALESCE is the tool to fix your query, but the better solution is not to break it to begin with.

Alternative in Postgres 9.4

Postgres 9.4 or later provides a separate function cardinality() that per documentation:

returns the total number of elements in the array, or 0 if the array is empty

Bold emphasis mine.
Seems like you deal with 1-dimensional arrays exclusively and just want to use that.

test=# SELECT cardinality('{}'::int[]);

However, this is not the proper fix for your problem, yet. The whole array might be NULL, which returns NULL either way and still sorts on top.

Fix query with NULLS LAST

SELECT id, array_length(users_who_like_ids, 1) AS ct
FROM   queryables

This always deals with NULL values properly. You might still want to use cardinality() to sort empty arrays before NULL. But be aware of the difference when dealing with multi-dimensional arrays.

  • I updated my answer to your previous question accordingly: dba.stackexchange.com/a/122511/3684. Note that the first query there avoids a similar problem because the aggregate function count() never returns NULL. Dec 29, 2015 at 4:53
  • thx Erwin for excellent answer (again) - will definitely go with NULLS LAST and hopefully be doing multi-dimensional arrays and JSON soon; really enjoying Postgres
    – timpone
    Dec 29, 2015 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.