Search code examples
sqlpostgresqlsql-in

PostgreSQL =ANY and IN

Recently I've read Quantified Comparison Predicates – Some of SQL’s Rarest Species:

In fact, the SQL standard defines the IN predicate as being just syntax sugar for the = ANY() quantified comparison predicate.

8.4 <in predicate>

Let RVC be the <row value predicand> and 
let IPV be the <in predicate value>.

The expression  RVC IN IPV
is equivalent to  RVC = ANY IPV

Fair enough, based on other answers like: What is exactly “SOME / ANY” and “IN” or Oracle: '= ANY()' vs. 'IN ()' I've assumed that I could use them interchangely.

Now here is my example:

select 'match'
where 1 = any( string_to_array('1,2,3', ',')::int[])
-- match

select 'match'
where 1 IN ( string_to_array('1,2,3', ',')::int[])
-- ERROR:  operator does not exist: integer = integer[]
-- HINT:  No operator matches the given name and argument type(s).
-- You might need to add explicit type casts.

DB Fiddle

The question is why the first query is working and the second returns error?

Solution

  • 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: