From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | MERGE and parsing with prepared statements |
Date: | 2022-07-14 16:26:18 |
Message-ID: | 20220714162618.GH18011@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 ?
From | Date | Subject | |
---|---|---|---|
Next Message | vignesh C | 2022-07-14 16:32:42 | Re: Refactor to make use of a common function for GetSubscriptionRelations and GetSubscriptionNotReadyRelations. |
Previous Message | Andrew Dunstan | 2022-07-14 15:45:52 | Re: SQL/JSON documentation JSON_TABLE |