How to get 0 as array_length() result when there are no elements

Question

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);

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:

• postgresql return 0 if returned value is null

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

• Why do NULL values come first when ordering DESC in a PostgreSQL query?

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.