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;

http://www.postgresql.org/message-id/e08cc0400809142350l10ca00b1j4bf0063b3643c3bd@mail.gmail.com — Gopesh Sharma, Aug 06 '14 at 08:39

Erwin Brandstetter · Accepted Answer · 2023-06-12T01:49:25.970

153

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.

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:

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)

edited Jun 12 '23 at 01:49

answered Aug 06 '14 at 08:58

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks @Erwin Brandstetter, I'm really missed second parameter for `array_length`. – joni jones Aug 06 '14 at 09:03
1

It returns null for an empty array on postgres 9.5 – Marcin Król Jul 19 '16 at 11:59
@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`. – Erwin Brandstetter Jul 19 '16 at 13:18
@ErwinBrandstetter Yes, you are right though it would be nice if you included a version that answers the question title. – Marcin Król Jul 19 '16 at 13:29
@MarcinKról: The title is a bit misleading like that. I added another answer to address it. – Erwin Brandstetter Jul 19 '16 at 13:39

score 48 · Answer 2 · answered Apr 28 '16 at 19:54

48

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

answered Apr 28 '16 at 19:54

bronzenose

631
5
6

1

Does not exists in version 9.2 and prior versions as well.I guess it has been added in 9.4. – user3526905 Apr 06 '17 at 05:53

score 0 · Answer 3 · answered Jan 29 '20 at 07:51

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]', '[""]')

score 0 · Answer 4 · answered Apr 22 '20 at 16:08

One way to check if the array is empty would be with array_ndims which returns the number of dimensions of the array, if the array is empty it will return 0. I'm working with version 11 postgresql.

$$
DECLARE

    IDS_PATROCINADOR_RED_0 INTEGER[] := ARRAY []::INTEGER[];
    IDS                        INTEGER;
BEGIN

    IF array_ndims(IDS_PATROCINADOR_RED_0) > 0 THEN
        IDS = array_length(ARRAY []::INTEGER, 1);
    ELSE
        IDS = 0 ;
    end if;
    RAISE NOTICE '%', IDS;
END

$$;

output:

[2020-04-22 11:06:22] [00000] 0

[2020-04-22 11:06:22] completed in 143 ms

score 0 · Answer 5 · answered Oct 20 '21 at 17:23

I had this problem in spring boot project with postgresql.

I had an array list parameter which can be null or empty, and if it's empty you should ignore it.

This solved everything. In repository check if list is empty or not, and pass null is it's empty, otherwise you will have error:

private static final String IDs = "Ids";
private static final String HAS_IDs = "hasIds";

...
parameters.addValue(IDs, CollectionUtils.isEmpty(params.getIds()) ? null : params.getIds());

Make a boolean paramener which says if list is empty or not:

parameters.addValue(HAS_IDs, !CollectionUtils.isEmpty(params.getIds()));

In SQL use this:

select * from table_name where
:hasIds = false or id in (:Ids)

How to check if an array is empty in Postgres

5 Answers5

Linked