BUG #17631: Prepare + Merge fails to identify parameter types

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17631: Prepare + Merge fails to identify parameter types
Дата
Msg-id 17631-7f892d95b3264da3@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17631: Prepare + Merge fails to identify parameter types
Список pgsql-bugs
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Default framing option RANGE adds cost for no gain to some window functions
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17631: Prepare + Merge fails to identify parameter types