Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby <pryzby(at)telsasoft(dot)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.