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 <[email protected]>
a écrit :

> >>>>> "Karen" == Karen Goh <[email protected]> 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)
>
>
>