agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Karen Goh <karenworld@yahoo.com>
To: Tumasgiu Rossini <rossini.t@gmail.com>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)
Date: Thu, 18 Jul 2019 08:54:28 +0000 (UTC)
Message-ID: <1663956557.1802027.1563440068968@mail.yahoo.com> (raw)
In-Reply-To: <CAJD9AWwLBw=2xOc7qYRgbQNi9w1B8C5o7tjTAvetvBtkPNd4Uw@mail.gmail.com>
References: <110414461.528890.1563075361778@mail.yahoo.com>
<40544440.741577.1563178850210@mail.yahoo.com>
<CAKFQuwYJsFw9p_qLyashvUmONFZre9OxgEWxDkqdY6-gWB9G7A@mail.gmail.com>
<768811852.1032981.1563242356010@mail.yahoo.com>
<CAKFQuwaC_Z24XxjvsSgYPvSNpu+K1Z9_60auVxEpjFQpE5zrFg@mail.gmail.com>
<774271584.1091322.1563257675315@mail.yahoo.com>
<1984680550.1098414.1563257845450@mail.yahoo.com>
<f11f7f05-e3fc-2794-e4ec-4b7144ae56be@stb-datenservice.de>
<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>
<CAJD9AWwLBw=2xOc7qYRgbQNi9w1B8C5o7tjTAvetvBtkPNd4Uw@mail.gmail.com>
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 iPhone
On 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
Le mar. 16 juil. 2019 à 18:01, Andrew Gierth <andrew@tao11.riddles.org.uk> a écrit :
>>>>> "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)
view thread (3+ messages)
Message-ID: <1663956557.1802027.1563440068968@mail.yahoo.com>
Permalink: ../1663956557.1802027.1563440068968@mail.yahoo.com/
Also on: postgresql.org/message-id/1663956557.1802027.1563440068968@mail.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: karenworld@yahoo.com, rossini.t@gmail.com, andrew@tao11.riddles.org.uk, pgsql-sql@lists.postgresql.org
Subject: Re: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)
In-Reply-To: <1663956557.1802027.1563440068968@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox