8

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?

Edit

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

9

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[]);
 cardinality
-------------
           0

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
ORDER  BY 2 DESC NULLS LAST;

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.

| improve this answer | |
  • 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. – Erwin Brandstetter Dec 29 '15 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 '15 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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