Boolean comparison of null values in postgres arrays

Question

I have the following table:

   id    | row_2 
---------+---------------
       1 | {t,NULL}
       2 | {NULL}

I want to keep the rows that are either true or NULL and filter out the rows in which the array contains a false value. I did this:

SELECT
    *
FROM
    my_table t
WHERE
    not (false = ANY (t.row_2));

This doesn't work because a comparison with a null value results in null. That is, this...

SELECT not false = any (ARRAY[true, true]);
SELECT not false = any (ARRAY[false, false]);

...outputs true and false respectively (as expected). But this...

SELECT not false = any (ARRAY[true, null]);

...outputs NULL.

klin · Accepted Answer · 2020-01-10 20:30:52Z

1

Usually it is good to use coalesce() to correct null comparisons:

select *
from my_table
where not coalesce(false = any(row_2), false)

Note, that per the documentation of ANY/SOME(array) (emphasis mine):

Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of ANY will be null, not false

answered Jan 10, 2020 at 18:49

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1