How to check if an array is empty in Postgres

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)