On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby <pry...@telsasoft.com> wrote:
> > That appears to be copied from the INSERT page. > What does that mean, if not that data types will be resolved as needed ? > Yep, and the system needs to resolve the type at a point where there is no contextual information and so it chooses text. > Note that if I add casts to the "ON" condition, MERGE complains about the > INSERT VALUES. > > PREPARE p AS > MERGE INTO CustomerAccount CA > USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T > ON CA.CustomerId = T.CustomerId::int > WHEN NOT MATCHED THEN > INSERT (CustomerId, Balance) > VALUES (T.CustomerId, T.TransactionValue) > WHEN MATCHED THEN > UPDATE SET Balance = Balance + TransactionValue; > > ERROR: column "customerid" is of type integer but expression is of type > text > LINE 7: VALUES (T.CustomerId, T.TransactionValue) > > Noted. Not surprised. That error was always present, it's just that the join happens first. Since your fix narrowly targeted the join this error remained to be discovered. David J.
