Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ho2BY-00012u-A5 for pgsql-sql@arkaria.postgresql.org; Thu, 18 Jul 2019 08:54:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ho2BW-0000VW-Od for pgsql-sql@arkaria.postgresql.org; Thu, 18 Jul 2019 08:54:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ho2BW-0000VO-E8 for pgsql-sql@lists.postgresql.org; Thu, 18 Jul 2019 08:54:42 +0000 Received: from sonic305-1.consmr.mail.bf2.yahoo.com ([74.6.133.40]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ho2BT-00043g-38 for pgsql-sql@lists.postgresql.org; Thu, 18 Jul 2019 08:54:42 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1563440076; bh=6PaSQIlRTzgBYhncUFKBorJsKtXpmueQzPNopn/DrCI=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=d+NvnX3R+Y3ZYUJN/GCnBWrsb8xdYYNlmAYazRqmNKY/nL2WySv32XS3n5dUKCQ1wOm3aMnyyHP4zqRBaS7h1cFHTKFROzxUzNzHvwgX+QdXfl1jv2ORIZoFgW7O1ipB3SK439vOOk8Bno7B7a+FgTUTMbqJdZ6+V4HTmerrin+ThUkLrSW4ym2ns/m+Yqgg2dJRBTFnXkNyNZGsTB43GUJLT8mSIFpYiTwzWylF1DbiuTa+tXwY9I3yHT66T/wmO66hA5QoiOOcc1KK1OrarU1RDvZFo5/fof+GpLzGPjqla5FIHV+7QHofSUHsw2is/kn6xguSIQnh/t0wk4RZAw== X-YMail-OSG: yuy6MEoVM1n.eKl7jMyoixP8wcyvjcPsHMbj01JcBFt8lsiykHCoPbzLgkYCFj3 KeQRsJDHHR7wPdWDaCmfNQL54Dj4UuS2H8el.aY5JCNQP.JDZff4HQlgzoxphy779EuleSdbACHJ plVw25sqOrgrQFP7U3E3uf55l0Gv3wxVVmFircs9ycvbMFd7qI5f8NJLLrC7Ve1dV3BfA0MIxBrI KZ.x.cXF7xIkSSpAAPCCQmCkIDsKh6XhfwPQzdRaZoAqh2.alhUI6wpavv8nAw3_cG.is_4M8vba sDjFpopofrQhUHJCCJ7mcLfJ1lPPkMuOWJ5smphWYNfHOF4qmfy4lqzmFCcPKLTftczEwiGQiU8n oKfTOasJN24HBtV2kSnEK3D_p8djbBEEj6e6vep8Aad26S1d1O0J2vfP0YpWI7Z7K2IkhsTiacES dA7Ioxif1idcKREtYhuvd5iclOhwRhCv6rM.W3BX9Aex9W.FD41_8hM3NnrhSVa.Aod2tBSFNBr6 ct7f3GxIyEnJ5NQi11ubNp2gpLBl0QlFd64MdyO3UvFhwJzhBAm6yvJ6pE0ap9ZtMwQYnINHGMF4 EiAAeQ4ufPOO0ZPeNWhko8pnDrNydepHE8IemcS6s6L8Feron66JWMDiTssMvmUR5Mf4U1nc1Nq7 aoxC4OvWNRNFKqPMs5bvDNAPoGSFDtoN6Qc3PHKoobswYD4YaqTsXATKCT9TCh9CwUrE6YmnljXD I0AQQgKj9OPbNOItx_yJ454W0zcUR.Gq5wHdL0Z9DZiiWmACJJjtyYAzYOr4jfsyAZGQ7O6yf6_Z 8vLVDSjBmk9.sfi.UsKusNeRv2cL4N0nELAKzfYG8gQOvVjv6Znw3yN0iXt.IogSk8YL5co00RNt DL5Z3SaQJbTZDxUZPVz1ZpXeM3T0ka7o8Lr8jhRmLRkc8MS.MbePy7U3tgKRZ8XEoBn7XmrKSXiO 5zXvg5OkRG6KiFeIeN60f38IjolaJsqG5Qxabou4E4.7S_A6Rr0Vwa6.juIJ5MRl9vCgS8AKuyfZ LARWejh3846egkjoRrFd8E5OJgiw0nHzhHgkLFhHzLznUffbfFssDv6kDnE.xPYWvd75y1g.hqep q5UZyD2nc8cjMxkrHToHwAoRi9Vkh1SfPXvkUyums340a4FrjTz2ImqBhv.LTU8OhpA1FhxjX9lY ATIsJnQGpsHLNARDVTBG23taip4nROF0MN17sUiFIv2TqwwkEsC22EpGi82n8m5PbTvXFw36I_BQ GHYOmIVcnB1oUNnKtXA10kcJdQVfD9Yybqrp8paDDSA0flXumxqY67cxDaHVa.KhyElBaIdx6LBz MpjjK4Da4wo_ssmL5LUgplxlwg3l4FrHMkHyfVJcKHOCoKTITNRVU6LnSR1myZ9DSbjGA09pN Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.bf2.yahoo.com with HTTP; Thu, 18 Jul 2019 08:54:36 +0000 Date: Thu, 18 Jul 2019 08:54:28 +0000 (UTC) From: Karen Goh To: Tumasgiu Rossini , Andrew Gierth Cc: Message-ID: <1663956557.1802027.1563440068968@mail.yahoo.com> In-Reply-To: References: <110414461.528890.1563075361778@mail.yahoo.com> <40544440.741577.1563178850210@mail.yahoo.com> <768811852.1032981.1563242356010@mail.yahoo.com> <774271584.1091322.1563257675315@mail.yahoo.com> <1984680550.1098414.1563257845450@mail.yahoo.com> <59735533.1068095.1563265280844@mail.yahoo.com> <87o91u1idu.fsf@news-spur.riddles.org.uk> <1919398337.1106688.1563269115584@mail.yahoo.com> <87k1ci1g9u.fsf@news-spur.riddles.org.uk> <2066139407.1119562.1563270535921@mail.yahoo.com> <87d0ia1bmt.fsf@news-spur.riddles.org.uk> <1787690642.1167081.1563281149734@mail.yahoo.com> <875zo213dq.fsf@news-spur.riddles.org.uk> <274777029.1154161.1563286780671@mail.yahoo.com> <871ryq12hp.fsf@news-spur.riddles.org.uk> <472848228.1169550.1563289333511@mail.yahoo.com> <87sgr6ypvp.fsf@news-spur.riddles.org.uk> <774827472.1178535.1563291843182@mail.yahoo.com> <87o91uyo3o.fsf@news-spur.riddles.org.uk> Subject: Re: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1802026_399577580.1563440068966" X-Mailer: WebService/1.1.13991 YahooMailIosMobile Yahoo%20Mail/45955 CFNetwork/978.0.7 Darwin/18.6.0 Content-Length: 9575 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1802026_399577580.1563440068966 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks so much for your help. It is working and running now except that I may need to change the query fu= rther to meet more requirements.=C2=A0 Out of curiosity, could you let me know the log is written in what programm= ing language cos I remember there is $1 etc=C2=A0 Could you also know if this Any(?) way of query is only applicable to Postg= reSQL? Sent from Yahoo Mail for iPhone On Thursday, July 18, 2019, 4:43 PM, Tumasgiu Rossini = 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 =C2=A0=C2=A0=C2=A0 SELECT *=20 =C2=A0=C2=A0=C2=A0 FROM baz=C2=A0=C2=A0=C2=A0 WHERE foo IN (SELECT unnest(A= RRAY[1,2,3]))=C2=A0=C2=A0=C2=A0 ; You can also combine operators with the ANY/ALL operator=20 to use it against arrays =C2=A0=C2=A0 SELECT *=C2=A0=C2=A0 FROM baz=C2=A0=C2=A0 WHERE foo =3D ANY (A= RRAY[1,2,3])=C2=A0=C2=A0 ; The latter query is postgres specific. Cheers Le=C2=A0mar. 16 juil. 2019 =C3=A0=C2=A018:01, Andrew Gierth a =C3=A9crit=C2=A0: >>>>> "Karen" =3D=3D Karen Goh writes: =C2=A0Karen> I have been told In clause in the way to do it. =C2=A0Karen> 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')=C2=A0 =C2=A0-- explicit literals col IN (?, ?, ?)=C2=A0 =C2=A0-- 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?=C2=A0 Apologies cos I haven=E2=80=99t 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'])=C2=A0 =C2=A0-- trying to compare text and text[= ] col IN (?)=C2=A0 -- 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. --=20 Andrew (irc:RhodiumToad) ------=_Part_1802026_399577580.1563440068966 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 require= ments. 

Out of curiosity, could you let me kn= ow the log is written in what programming language cos I remember there is = $1 etc 

Could you also know if this Any(?) wa= y of query is only applicable to PostgreSQL?


Sent from Yahoo Mail for iPhone

On Thursday, July 18, 2019, 4:43 PM, = Tumasgiu Rossini <rossini.t@gmail.com> wrote:

IN clause does n= ot require explicit listing,
but a set of values, which can be ex= pressed
as a subquery.

You can transform your array to a set using unnest
<= div>
    SELECT *
    FROM baz
    W= HERE foo IN (SELECT unnest(ARRAY[1,2,3]))
    ;

You can also combine operators with t= he ANY/ALL operator
to use it against arrays<= br clear=3D"none">

   SEL= ECT *
   FROM baz
   WHERE foo =3D = ANY (ARRAY[1,2,3])
   ;
Th= e latter query is postgres specific.

Cheers

Le&nb= sp;mar. 16 juil. 2019 =C3=A0 18:01, Andrew Gierth <andrew@tao11.riddles.o= rg.uk> a =C3=A9crit :
>>>>> "Karen"= =3D=3D 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? 

<= /blockquote>
Apologies cos I haven=E2=80=99t copy down our conversation and my memory i= s failing me.

Am really amazed where you get that energy helping peo= ple 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)


------=_Part_1802026_399577580.1563440068966--