Thanks so much for your help.
It is working and running now except that I may need to change the query further to meet more requirements.
Out of curiosity, could you let me know the log is written in what programming language cos I remember there is $1 etc
Could you also know if this Any(?) way of query is only applicable to PostgreSQL?
Sent from Yahoo Mail for iPhoneOn Thursday, July 18, 2019, 4:43 PM, Tumasgiu Rossini <rossini.t@gmail.com> wrote:
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)
Why is the parameter for the 2nd case not text or varchar?
Apologies cos I haven’t copy down our conversation and my memory is failing me.
Am really amazed where you get that energy helping people like me. Wish you good health.
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)
