5

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?

8

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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