Please explain to me this behavior.

This SQL finds elements where ids (type is array::bigint) is empty.

SELECT * FROM rises WHERE ids = '{}'
-- finds, e.g., 9 rows

This SQL finds no rows:

SELECT * FROM rises WHERE array_length(ids, 1) = 0
--finds always 0 rows

But this SQL can find non-empty arrays

SELECT * FROM rises WHERE array_length(ids, 1) > 0
--finds, e.g., 15 rows


    id bigserial, 
    d1 bigint DEFAULT 0, 
    ids bigint[] DEFAULT '{}', 
    PRIMARY KEY (id));

Why array_length can find non-empty arrays but it doesn't work for empty arrays?

1 Answer 1


It looks like PostgreSQL returns NULL for empty arrays. Try:

    array_length(ARRAY[]::bigint[], 1),
    array_length(ARRAY[]::bigint[], 1) IS NULL,
    array_lower(ARRAY[]::bigint[], 1),
    array_upper(ARRAY[]::bigint[], 1)

You get: null|true|null|null

Looks weird, but that's just the way it is. The workaround is to use COALESCE:

    COALESCE(array_length(ARRAY[]::bigint[], 1), 0)

returns 0.

Tried this on PostgreSQL 9.4.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.