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 emptyNULL
.. array is NULLFALSE
.. any other case (array has elements - even if just NULL elements)
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
Below example accepts array values
, NULL
or empty string
as query parameter. Some frameworks result in an SQL error when you try to pass NULL
as query parameter and you send an empty string instead.
The query checks if array-parameter is empty using json mapping:
SELECT CAST(ARRAY_TO_JSON(ARRAY[:myArrayParameter]) AS VARCHAR) IN ('[null]', '[""]')