From: Tom Lane <tgl@sss.pgh.pa.us>
To: arthur.mcgibbon@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17631: Prepare + Merge fails to identify parameter types
Date: Mon, 10 Oct 2022 16:53:28 -0400
Message-ID: <151679.1665435208@sss.pgh.pa.us> (raw)
In-Reply-To: <17631-7f892d95b3264da3@postgresql.org>
References: <17631-7f892d95b3264da3@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
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: tgl@sss.pgh.pa.us, arthur.mcgibbon@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17631: Prepare + Merge fails to identify parameter types
In-Reply-To: <151679.1665435208@sss.pgh.pa.us>
* 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