From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | arthur(dot)mcgibbon(at)gmail(dot)com |
Subject: | BUG #17631: Prepare + Merge fails to identify parameter types |
Date: | 2022-10-10 17:14:34 |
Message-ID: | 17631-7f892d95b3264da3@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17631
Logged by: Arthur McGibbon
Email address: arthur(dot)mcgibbon(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-10-10 20:53:28 | Re: BUG #17631: Prepare + Merge fails to identify parameter types |
Previous Message | Simon Riggs | 2022-10-10 12:24:34 | Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records |