How to check if an array is empty in Postgres

Question

I have a Postgres function:

CREATE OR REPLACE FUNCTION get_stats(
    _start_date timestamp with time zone,
    _stop_date timestamp with time zone,
    id_clients integer[],
    OUT date timestamp with time zone,
    OUT profit,
    OUT cost
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    query varchar := '';
BEGIN
... -- lot of code
IF id_clients IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
... -- other code
END;
$$;

So if I run query something like this:

SELECT * FROM get_stats('2014-07-01 00:00:00Etc/GMT-3'
                      , '2014-08-06 23:59:59Etc/GMT-3', '{}');

Generated query has this condition:

"... AND id = ANY('{}')..."

But if an array is empty this condition should not be represented in query.
How can I check if the array of clients is not empty?

I've also tried two variants:

IF ARRAY_UPPER(id_clients) IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

And:

IF ARRAY_LENGTH(id_clients) THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

In both cases I got this error: ARRAY_UPPER(ARRAY_LENGTH) doesn't exists;

Solution 1

array_length() requires two parameters, the second being the dimension of the array:

array_length(id_clients, 1) > 0

So:

IF array_length(id_clients, 1) > 0 THEN
    query := query || format(' AND id = ANY(%L))', id_clients);
END IF;

This excludes both empty array and NULL.

Or use cardinality() in Postgres 9.4 or later. See added answer by @bronzenose.

---

But if you're concatenating a query to run with EXECUTE, it would be smarter to pass values with a USING clause. Examples:

• Multirow subselect as parameter to `execute using`
• How to use EXECUTE FORMAT ... USING in postgres function

---

BTW, to explicitly check whether an array is empty (like your title says - but that's not what you need here) just compare it to an empty array:

id_clients = '{}'

That's all. You get:

TRUE .. array is empty
NULL .. array is NULL
FALSE .. any other case (array has elements - even if just NULL elements)

Solution 2

if for some reason you don't want to supply the dimension of the array, cardinality will return 0 for an empty array:

From the docs:

cardinality(anyarray) returns the total number of elements in the array, or 0 if the array is empty

Comments

[Gopesh Sharma]

postgresql.org/message-id/…

[joni jones]

Thanks @Erwin Brandstetter, I'm really missed second parameter for array_length.

[Marcin Król]

It returns null for an empty array on postgres 9.5

[Erwin Brandstetter]

@MarcinKról: Which is by design and not a problem for the use case: The IF branch is entered when the expression evaluates to TRUE and not for FALSE or NULL.

[Marcin Król]

@ErwinBrandstetter Yes, you are right though it would be nice if you included a version that answers the question title.

[Erwin Brandstetter]

@MarcinKról: The title is a bit misleading like that. I added another answer to address it.

[user3526905]

Does not exists in version 9.2 and prior versions as well.I guess it has been added in 9.4.