A PostgreSQL query with 'ANY' is not working

arrays, postgresql

Solution

You want to use `ALL`, not `ANY`. From the fine manual:

9.21.3. ANY/SOME (array)

expression operator ANY (array expression)

[...] The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of `ANY` is "true" if any true result is obtained.

So if we say this:

1 != any(array[1,2])

then we'll get true since `(1 != 1) or (1 != 2)` is true. `ANY` is essentially an `OR` operator. For example:

=> select id from (values (1),(2),(3)) as t(id) where id != any(array[1,2]);
 id 
----
  1
  2
  3
(3 rows)

If we look at `ALL`, we see:

9.21.4. ALL (array)

expression operator ALL (array expression)

[...] The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of `ALL` is "true" if all comparisons yield true...

so if we say this:

1 != all(array[1,2])

then we'll get false since `(1 != 1) and (1 != 2)` is false and we see that `ALL` is essentially an `AND` operator. For example:

=> select id from (values (1),(2),(3)) as t(id) where id != all(array[1,2]);
 id 
----
  3
(1 row)

If you want to exclude all values in an array, use `ALL`:

select "Ticket_id"
from "Tickets"
where "Status" = 1
  and "Ticket_id" != all(array[1,2,3])
limit 6

Problem

Original source