agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Karen Goh <karenworld@yahoo.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)
Date: Tue, 16 Jul 2019 17:01:39 +0100
Message-ID: <87o91uyo3o.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <774827472.1178535.1563291843182@mail.yahoo.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>
>>>>> "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)
view thread (3+ messages) latest in thread
Message-ID: <87o91uyo3o.fsf@news-spur.riddles.org.uk>
Permalink: ../87o91uyo3o.fsf@news-spur.riddles.org.uk/
Also on: postgresql.org/message-id/87o91uyo3o.fsf@news-spur.riddles.org.uk
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: andrew@tao11.riddles.org.uk, karenworld@yahoo.com, 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: <87o91uyo3o.fsf@news-spur.riddles.org.uk>
* 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