pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17631: Prepare + Merge fails to identify parameter types
2+ messages / 2 participants
[nested] [flat]
* BUG #17631: Prepare + Merge fails to identify parameter types
@ 2022-10-10 17:14 PG Bug reporting form <noreply@postgresql.org>
2022-10-10 20:53 ` Re: BUG #17631: Prepare + Merge fails to identify parameter types Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-10-10 17:14 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: arthur.mcgibbon@gmail.com
The following bug has been logged on the website:
Bug reference: 17631
Logged by: Arthur McGibbon
Email address: arthur.mcgibbon@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: Docker + Windows
Description:
PostgreSQL version: 15rc2.
The link on the 15c2 release page takes me here but I can't select 15rc2 as
the version.
I use the java JDBC driver to access Postgresql. To work out the parameters
of a prepared query it prepares the query without supplying the params...
e.g.
create table foo (id int, bar bit);
PREPARE foo_insert as
insert into foo
select $1, $2;
This works, and so do statements with "update" and
"insert-on-conflict-do-update" commands, but when I try it with the new
MERGE statement I get an error...
e.g.
create table foo (id int, bar bit);
PREPARE foo_merge as
merge into foo as target
using(values($1, $2))
as source (id, bar)
on target.id = source.id
when matched then
update set bar = source.bar
when not matched then
insert(id, bar)
values ($1, $2);
gives error...
ERROR: operator does not exist: integer = text
LINE 5: on target.id = source.id
^
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
SQL state: 42883
Character: 121
I can get round this by casting and changing...
using(values($1, $2))
to...
using(values(cast($1 as int), cast($2 as bit)))
Is this a bug or is it currently too hard for Postgresql to work out the
parameter types? It does have all the information needed in just the first
2 lines.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17631: Prepare + Merge fails to identify parameter types
2022-10-10 17:14 BUG #17631: Prepare + Merge fails to identify parameter types PG Bug reporting form <noreply@postgresql.org>
@ 2022-10-10 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-10-10 20:53 UTC (permalink / raw)
To: arthur.mcgibbon@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> create table foo (id int, bar bit);
> PREPARE foo_merge as
> merge into foo as target
> using(values($1, $2))
> as source (id, bar)
> on target.id = source.id
> when matched then
> update set bar = source.bar
> when not matched then
> insert(id, bar)
> values ($1, $2);
> gives error...
> ERROR: operator does not exist: integer = text
> LINE 5: on target.id = source.id
> ^
> HINT: No operator matches the given name and argument types. You might need
> to add explicit type casts.
I don't see any bug here. There's no reason to assume that the VALUES
clause in USING must yield the same columns that the merge target table
has, so those Params just default to text type. I realize that there
are some kluges that let "insert into foo values($1,$2)" act differently,
but the key word there is "kluge". VALUES in contexts other than INSERT
has never had such a behavior. For example, if you do this:
prepare foo as select * from foo join (values($1,$2)) v(f1,f2) on id = v.f1;
you'll get the very same failure:
ERROR: operator does not exist: integer = text
LINE 1: ...elect * from foo join (values($1,$2)) v(f1,f2) on id = v.f1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-10-10 20:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-10 17:14 BUG #17631: Prepare + Merge fails to identify parameter types PG Bug reporting form <noreply@postgresql.org>
2022-10-10 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox