Menu
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
417
PostgreSQL has an ANY operator that is used to compare a scalar value with a set of values returned by a subquery. The below rules must be followed while using PostgreSQL ANY operator: The subquery must return exactly one column.
With clause: This is defined as a clause that was used to execute the Subquery and large Subquery in PostgreSQL. Name of CTE: This is defined name of the common table expression which was we have used with clause. AS: This is defined as we have used alias name of a common table expression in with clause.
The PostgreSQL NOT condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.
Wildcards in PostgreSQL is used to find matching rows values from tables; it is also used to find matching patterns rows from tables, Wildcards is also used to find matching rows, column and tables names; the output of the wildcard operator will return matching name, which was table name, column name or rows, In ...
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
ANYis "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
ALLis "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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
