On Thu, 14 Jul 2022, 18:26 Justin Pryzby, <pry...@telsasoft.com> wrote: > > We've used INSERT ON CONFLICT for a few years (with partitions as the target). > That's also combined with prepared statements, for bulk loading. > > I was looking to see if we should use MERGE (probably not, but looking > anyway). > And came across this behavior. I'm not sure if it's any issue. > > CREATE TABLE CustomerAccount (CustomerId int, Balance float); > > PREPARE p AS > MERGE INTO CustomerAccount CA > USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T > ON CA.CustomerId = T.CustomerId > WHEN NOT MATCHED THEN > INSERT (CustomerId, Balance) > VALUES (T.CustomerId, T.TransactionValue) > WHEN MATCHED THEN > UPDATE SET Balance = Balance + TransactionValue; > > ERROR: operator does not exist: integer = text > LINE 3: ON CA.CustomerId = T.CustomerId > > Why is $1 construed to be of type text ?
The select statement that generates the row type of T `(select $1 CID, $2 TxV) AS T` does not put type bounds on the input parameters, so it remains `unknown` for the scope of that subselect. Once stored into the row type, the type of that column defaults to text, as a row type should not have 'unknown'-typed columns. You'll see the same issue with other subselects that select input parameters without casts, such as `select a from (select $1 a) A where A.a = 1;`. It's a pre-existing issue that has popped up earlier, and I think it's not something we've planned to fix in backbranches. Kind regards, Matthias van de Meent
