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?
2
Answers
Use
array_position()
instead. Internally, it usesis not distinct from
comparison predicate instead of regular equality.Demo at db<>fiddle:
Unfortunately,
IS NOT DISTINCT FROM
syntax (the usual one used to do a null-aware comparison) cannot be used withANY
in Postgres.You can always transform an
[NOT] IN
or[=/<>] ANY
query into a[NOT] EXISTS
.So combine that with
IS [NOT] DISTINCT FROM
.db<>fiddle