In VS Any Operator in Postgresql

PostgreSQL - IN vs ANY

No, in these variants are same:

You can see - the execution plans are same too:

postgres=# explain select * from foo1 where id in (select id from foo2);
┌──────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                            │
╞══════════════════════════════════════════════════════════════════╡
│ Hash Semi Join  (cost=3.25..21.99 rows=100 width=4)              │
│   Hash Cond: (foo1.id = foo2.id)                                 │
│   ->  Seq Scan on foo1  (cost=0.00..15.00 rows=1000 width=4)     │
│   ->  Hash  (cost=2.00..2.00 rows=100 width=4)                   │
│         ->  Seq Scan on foo2  (cost=0.00..2.00 rows=100 width=4) │
└──────────────────────────────────────────────────────────────────┘
(5 rows)

postgres=# explain select * from foo1 where id = any (select id from foo2);
┌──────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                            │
╞══════════════════════════════════════════════════════════════════╡
│ Hash Semi Join  (cost=3.25..21.99 rows=100 width=4)              │
│   Hash Cond: (foo1.id = foo2.id)                                 │
│   ->  Seq Scan on foo1  (cost=0.00..15.00 rows=1000 width=4)     │
│   ->  Hash  (cost=2.00..2.00 rows=100 width=4)                   │
│         ->  Seq Scan on foo2  (cost=0.00..2.00 rows=100 width=4) │
└──────────────────────────────────────────────────────────────────┘
(5 rows)

PostgreSQL =ANY and IN

That's because IN (unlike ANY) does not accept an array as input. Only a set (from a subquery) or a list of values. Detailed explanation:

• How to use ANY instead of IN in a WHERE clause with Rails?

Difference between IN and ANY operators in SQL

SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *
  2  FROM employee
  3  WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
  2  FROM employee
  3  WHERE salary IN (2000, 3000, 4000);

But with the IN operator you cannot use =, <>, <, >, <=, or >=

SQL Using LIKE and ANY at the same time

The simple solution is to use the regular expression match operator ~ instead, which works with strings in arg as is (without concatenating wildcards):

SELECT *
FROM   tbl
WHERE  feature ~ ANY(args);

string ~ 'pattern' is mostly equivalent to string LIKE '%pattern%', but not exactly, as LIKE uses different (and fewer) special characters than ~. See:

• Escape function for regular expression or LIKE patterns

If that subtle difference is not acceptable, here is an exact implementation of what you are asking for:

SELECT *
FROM   tbl t
WHERE  t.feature LIKE ANY (SELECT '%' || a || '%' FROM unnest(t.args) a);

Unnest the array, pad each element with wildcards, and use LIKE ANY with the resulting set.

See:

• IN vs ANY operator in PostgreSQL

How do I use the Postgresql ANY operator in a NOT IN statement

When you do

select 2 != any(array[2,3,4]);
 ?column? 
----------
 t

2 will be compared to all array items and if there is any to which 2 is not equal it will evaluate to true.

Use not id = any(array[2,3,4])

select not 1 = any(array[2,3,4]);
 ?column? 
----------
 t

select not 2 = any(array[2,3,4]);
 ?column? 
----------
 f

Or != all

select 1 != all(array[2,3,4]);
 ?column? 
----------
 t

select 2 != all(array[2,3,4]);
 ?column? 
----------
 f

postgres can we use array operation to perform IN or ANY operator?

I encourage you to explore questions like these on your own using the EXPLAIN keyword to see what postgres is doing behind the scenes. Let's compare the EXPLAIN plans of two of these queries:

postgres=# explain select * from tag where ARRAY[name] && '{"tag1","tag2"}';
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on tag  (cost=0.00..11.88 rows=1 width=516)
   Filter: (ARRAY[name] && '{tag1,tag2}'::character varying[])
(2 rows)


postgres=# explain select * from tag where name = ANY('{"tag1","tag2"}');
                              QUERY PLAN
-----------------------------------------------------------------------
 Bitmap Heap Scan on tag  (cost=4.31..9.65 rows=2 width=516)
   Recheck Cond: ((name)::text = ANY ('{tag1,tag2}'::text[]))
   ->  Bitmap Index Scan on tag_pkey  (cost=0.00..4.30 rows=2 width=0)
         Index Cond: ((name)::text = ANY ('{tag1,tag2}'::text[]))
(4 rows)

As you can see, the queries with ARRAY[name] are not able to using the index on the name column, because postgres isn't acting on the column directly, but rather on an array created from the column. However, the IN and ANY queries have the same plan, and make use of your index, so you should use one of those.

---

Related Topics