From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: arthur.mcgibbon@gmail.com
Subject: BUG #17631: Prepare + Merge fails to identify parameter types
Date: Mon, 10 Oct 2022 17:14:34 +0000
Message-ID: <17631-7f892d95b3264da3@postgresql.org> (raw)
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.
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, arthur.mcgibbon@gmail.com
Subject: Re: BUG #17631: Prepare + Merge fails to identify parameter types
In-Reply-To: <17631-7f892d95b3264da3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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