Re: smallint out of range EXECUTEing prepared statement

>>>>> "Justin" == Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:

Justin> Is this expected behavior ?

Justin> ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1;
Justin> (0 rows)

Justin> ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1;
Justin> PREPARE
Justin> ts=# EXECUTE x(32768);
Justin> ERROR: smallint out of range

If column "site_id" is of type smallint, then parse analysis will deduce
a type of smallint for $1, which is otherwise of unknown type. So the
prepared statement "x" then has one parameter of type smallint.

Passing 32768 for that parameter therefore fails with the expected error.

Justin> ts=# PREPARE y AS SELECT * FROM t WHERE site_id::int=$1 LIMIT 1;
Justin> PREPARE

Now $1 is of type integer, not smallint, because parse analysis sees
(integer = unknown) and deduces the type from that.

(a better way would be WHERE site_id = $1::integer, which would allow
index usage on site_id, unlike your example)

--
Andrew (irc:RhodiumToad)