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
>>>>> "Karen" == Karen Goh <karenworld@yahoo.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)
