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.