PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – NULL elements are ignored for "column = ANY(array)"

I have a query

select * from table1 where column1=ANY(@some_array)

@some_array – parameter that has array of values. It could contain null, which is correct according to my business logic.

Unfortunately, query returns nothing if @some_array=[1, NULL]

Documentation says:

expression operator ANY (array expression)

if the right-hand array contains any null elements and no true comparison result is obtained, the result of ANY will be null, […]

But in my case the table contains values for 1 and for NULL, so the result must appear.

Does anybody understand why select * from table1 where column1=ANY([1,NULL]) doesn’t return data?

Tags:

2

Answers


  1. 0

    Use array_position() instead. Internally, it uses is not distinct from comparison predicate instead of regular equality.
    Demo at db<>fiddle:

    id column1
    1 1
    2 2
    3 3
    4 4
    null null 
    select * from table1 where column1=ANY(array[2,3,null]);
    id column1
    2 2
    3 3 
    select * from table1 where array_position(array[2,3,null],column1) is not null;
    id column1
    2 2
    3 3
    null null
    Login or Signup to reply.
  2. 0

    Unfortunately, IS NOT DISTINCT FROM syntax (the usual one used to do a null-aware comparison) cannot be used with ANY in Postgres.

    You can always transform an [NOT] IN or [=/<>] ANY query into a [NOT] EXISTS.

    So combine that with IS [NOT] DISTINCT FROM.

    select *
from table1 t1
where exists (select 1
    from (values(2),(3),(null)) a(value)
    where a.value is not distinct from t1.column1
);

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search