Re: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)

IN clause does not require explicit listing,
but a set of values, which can be expressed
as a subquery.

You can transform your array to a set using unnest

SELECT *
FROM baz
WHERE foo IN (SELECT unnest(ARRAY[1,2,3]))
;

You can also combine operators with the ANY/ALL operator
to use it against arrays

SELECT *
FROM baz
WHERE foo = ANY (ARRAY[1,2,3])
;
The latter query is postgres specific.

Cheers

Le mar. 16 juil. 2019 à 18:01, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
a écrit :

> >>>>> "Karen" == Karen Goh <karenworld(at)yahoo(dot)com> writes:
>
> Karen> I have been told In clause in the way to do it.
> Karen> So, not sure why am I getting that error....
>
> Because the IN clause requires a list (an explicitly written out list,
> not an array) of values of the same type (or at least a comparable type)
> of the predicand.
>
> i.e. if "col" is a text column, these are legal syntax:
>
> col IN ('foo', 'bar', 'baz') -- explicit literals
>
> col IN (?, ?, ?) -- some fixed number of placeholder parameters
>
> (in that second case, the parameters should be of type text or varchar)
>
> but these are not legal and will give a type mismatch error:
>
> col IN (array['foo','bar']) -- trying to compare text and text[]
>
> col IN (?) -- where the parameter type is given as text[] or varchar[]
>
> There is no way in either standard SQL or PostgreSQL to use IN to
> specify a variable-length parameter array of values to compare against.
>
> Some people (including, alas, some authors of database drivers, looking
> at you psycopg2) try and work around this by dynamically interpolating
> values or parameter specifications into the query. This is BAD PRACTICE
> and you should never do it; keep your parameter values AWAY from your
> query strings, for security.
>
> --
> Andrew (irc:RhodiumToad)
>
>
>