array_length if array is empty behaviour

Question

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

Initialization:

CREATE TABLE rises(
    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?

Answer 1

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

SELECT 
    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:

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

returns 0.

Tried this on PostgreSQL 9.4.