MERGE and parsing with prepared statements

Поиск
Список
Период
Сортировка
От Justin Pryzby
Тема MERGE and parsing with prepared statements
Дата
Msg-id 20220714162618.GH18011@telsasoft.com
обсуждение исходный текст
Ответы Re: MERGE and parsing with prepared statements  (Matthias van de Meent <boekewurm+postgres@gmail.com>)
Список pgsql-hackers
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

        postgres: pryzbyj postgres [local] PREPARE(+0x2337be) [0x56108322e7be]
        postgres: pryzbyj postgres [local] PREPARE(oper+0x198) [0x56108322f1fb]
        postgres: pryzbyj postgres [local] PREPARE(make_op+0x7e) [0x56108322f55a]
        postgres: pryzbyj postgres [local] PREPARE(+0x228f2b) [0x561083223f2b]
        postgres: pryzbyj postgres [local] PREPARE(+0x227aa9) [0x561083222aa9]
        postgres: pryzbyj postgres [local] PREPARE(transformExpr+0x1c) [0x5610832227f9]
        postgres: pryzbyj postgres [local] PREPARE(transformMergeStmt+0x339) [0x56108322d988]
        postgres: pryzbyj postgres [local] PREPARE(transformStmt+0x70) [0x5610831f4071]
        postgres: pryzbyj postgres [local] PREPARE(+0x1fa350) [0x5610831f5350]
        postgres: pryzbyj postgres [local] PREPARE(transformTopLevelStmt+0x11) [0x5610831f5385]
        postgres: pryzbyj postgres [local] PREPARE(parse_analyze_varparams+0x5b) [0x5610831f54f4]
        postgres: pryzbyj postgres [local] PREPARE(pg_analyze_and_rewrite_varparams+0x38) [0x5610834bcdfe]
        postgres: pryzbyj postgres [local] PREPARE(PrepareQuery+0xcc) [0x561083292155]
        postgres: pryzbyj postgres [local] PREPARE(standard_ProcessUtility+0x4ea) [0x5610834c31a0]

Why is $1 construed to be of type text ?



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: SQL/JSON documentation JSON_TABLE
Следующее
От: vignesh C
Дата:
Сообщение: Re: Refactor to make use of a common function for GetSubscriptionRelations and GetSubscriptionNotReadyRelations.