From: 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?)
In-Reply-To: <774827472.1178535.1563291843182@mail.yahoo.com> (Karen Goh's
	message of "Tue, 16 Jul 2019 15:44:03 +0000 (UTC)")
Message-ID: <87o91uyo3o.fsf@news-spur.riddles.org.uk>
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>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Tue, 16 Jul 2019 17:01:39 +0100
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "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)