数组 – 具有“ANY”的PostgreSQL查询不起作用

9.21.3. ANY/SOME (array)

06000

[…] 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.

所以如果我们这样说：

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

那么我们会得到真实的，因为(1！= 1)或(1！= 2)是真的。任何本质上是一个OR运算符。例如：

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

如果我们看看ALL,we see：

9.21.4. ALL (array)

06003

[…] 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…

所以如果我们这样说：

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

那么我们会得到错误，因为(1！= 1)和(1！= 2)是假的，我们看到ALL本质上是一个AND运算符。例如：

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

如果要排除数组中的所有值，请使用ALL：

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