Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oC1fX-0007w1-QR for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Jul 2022 16:26:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oC1fV-0004Y1-63 for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Jul 2022 16:26:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oC1fU-0004Xs-Qi for pgsql-hackers@lists.postgresql.org; Thu, 14 Jul 2022 16:26:24 +0000 Received: from mail-qk1-x729.google.com ([2607:f8b0:4864:20::729]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oC1fR-0002XD-Id for pgsql-hackers@postgresql.org; Thu, 14 Jul 2022 16:26:23 +0000 Received: by mail-qk1-x729.google.com with SMTP id f14so1617444qkm.0 for ; Thu, 14 Jul 2022 09:26:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:mime-version:content-disposition :user-agent; bh=hkELo5OC0pnOJQeGPMj2Rdpicyo/RPPsL92LOZOeBKk=; b=IvZO8SHjwVXH4yqYCPaZa994/Q9dk+RGvHfSl/vTHQYiUaQiaHO1vlvD8ugUzFlnek amVE4RY77p1xdPbk91xVZu7grueRg3SX+WsYrtTuUUDTBPc+aWdvy01cwfcnDu+179kI mqHH6wovlXnHMsIdtUK5RDARh+WukFJBv/6Jp/0xymHk8NVOr+jHthiPCdbCQu6m6uLA ALED4LcpzZ7KqD8kQ+2Wry+0APjl1AP450PPhDSdcSbKP2+DOQIgGRBCWlE8Aw/4CH/B kHrSocP2EX/KRI+NlALoFrK9w7ir7PF84unEXKlp+g3dfIgLlMj0eC7vqI1fbLs2u0AD TWBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:mime-version :content-disposition:user-agent; bh=hkELo5OC0pnOJQeGPMj2Rdpicyo/RPPsL92LOZOeBKk=; b=KMUryr0JfC3mwRbdXOggdbFQFURNPno7X+tbwmXyJLOcNummWHMbhxm19BpFX34Duy o+XIGAA/c8aVPoc0kuHCQ+21bfeW3eTB3rMqlQTBtefhou11XZzpqTLXyu7QeihHW1LK IGI26oAjPojOxO7BW3XyOZ6JQr88mPIBdxUxOT3iSAT27HSQVvwWnkGpkbY4CcdCvLig a0eFbHT5Yqvha11kqzV/KixZFzYdGQMsngV1LOazEj+dDrhSb/Qt9ALMsVbaP399n6xL DI1xjuz6oj/jBoBYjVmhM4U1lh6aP1P4TyD05XVl1Mnyj1Jat+j6VvOJZcfQmblEhMAj xkVw== X-Gm-Message-State: AJIora9vHT2jIlPiqVI+uNk3cV8BEwep+4MhDWxsIMkrVgmIyqTQHDbp IR8YfuczVZ3/Wjq3sDo7BOhF1g== X-Google-Smtp-Source: AGRyM1u9UU3BgTu7Ac+vFoQi0e7Fdu648Iz7fVPUtsor0nl5Ke1LaOIu5mIaqTMoJEfiKApFuP6eOg== X-Received: by 2002:a05:620a:f0d:b0:6b5:473d:d4b with SMTP id v13-20020a05620a0f0d00b006b5473d0d4bmr6623811qkl.6.1657815980483; Thu, 14 Jul 2022 09:26:20 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id e25-20020ac86719000000b0031ec38da567sm1954792qtp.0.2022.07.14.09.26.19 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 14 Jul 2022 09:26:20 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id E8501800B38; Thu, 14 Jul 2022 11:26:18 -0500 (CDT) Date: Thu, 14 Jul 2022 11:26:18 -0500 From: Justin Pryzby To: Alvaro Herrera Cc: pgsql-hackers@postgresql.org Subject: MERGE and parsing with prepared statements Message-ID: <20220714162618.GH18011@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 ?