array_length if array is empty behaviour 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) &gt; 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?
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.