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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…