Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

arrays - A PostgreSQL query with 'ANY' is not working

SELECT "Ticket_id"  FROM "Tickets"
 WHERE "Status" = 1 AND ("Ticket_id" !=  ANY(array[1,2,3])) Limit 6

And the result is 1,2,3,4,5,6

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...