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 ?


Reply via email to