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 1oDQfP-0000Zs-BH for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Jul 2022 13:20:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oDQfO-00086u-0K for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Jul 2022 13:20:06 +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 1oDQfN-00086k-Ni for pgsql-hackers@lists.postgresql.org; Mon, 18 Jul 2022 13:20:05 +0000 Received: from mail-qk1-x72e.google.com ([2607:f8b0:4864:20::72e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oDQfL-0008QE-CO for pgsql-hackers@postgresql.org; Mon, 18 Jul 2022 13:20:04 +0000 Received: by mail-qk1-x72e.google.com with SMTP id k7so5706582qkj.2 for ; Mon, 18 Jul 2022 06:20:03 -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:references:mime-version :content-disposition:in-reply-to:user-agent; bh=odHsq2GkvNL2EZX0c3B7G3evvu+plwhhxNlTDkO1yR8=; b=2KlGc4EEg71+NFWGtlrEjP5oKTCkmKKakCMSzSJNxOtu3Ld76Wv68z/1Lv8z2/xVlN DMh7/pECvtLO7c65U73fwBGRocVQNS21sgW5NaTCXsASKepczAZd6LXBWNLacxec9gYJ TeX1ENB6v4L/gVPa3zoBz9Q9AWU6LttDP3OSJUOJmFLDwIohYhsMbVCqt+SKgbxC0aic WDU63u/n7eOX83pA6dS9cvUWibXDIFO8/D+KQGYxzKqZ712kP9xhWbRWufZ0RJO8jqG+ WTFpyajA7Xh1T0K8lyBOPhcS+uY6i2FYpInTsq85UDujJa9axP8LIO7/fVnFpjEbbAsB QShw== 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:references :mime-version:content-disposition:in-reply-to:user-agent; bh=odHsq2GkvNL2EZX0c3B7G3evvu+plwhhxNlTDkO1yR8=; b=XrLBrM79kwvXk8s5tWYjfGkLUuf0eimJfZViiYqA8e4cNeXfgqjwNBbmpKevXmIK+Y DJyW1ZTvr0JwXsEjgMwSh/B9nQquGDZ1hVH5MG/SaPwwlxic2gxUQzfRaW7W1q73BMsq 0Jfzkne+0TFScDrMq+3a8vNL6e0rGeHpMLn1mDpjf+89zUtkd+EjrGdY0l74aWuayDSf 8DoaE/od5apbAcTKo6DQIEHisEesdV8Ot0ghh/+WMxPBf3Lon1MxyLxlhzkh30NIQa7l ttJYhrg6x3adqPl79qzeQoDBt94LnJtHJIIc0Z7SCtL5QqwyreCzLJyDhUM1gBOS7Z/u m1yA== X-Gm-Message-State: AJIora/R1HScMD5a1KvSyKhcrzHEs9sSqV4hcRv8dpFzTOTkDo9+7TPa qDwgZf97o7tbdY5i+ENlKpW8Rw== X-Google-Smtp-Source: AGRyM1v87mtviUh/Tujh5H8bwOCDZOirdO4CuIuOZQi9JMyIpyYrRb4DiCLlu7A0rsM5YHVVZSZiHw== X-Received: by 2002:a05:620a:2585:b0:6a7:9479:5fa8 with SMTP id x5-20020a05620a258500b006a794795fa8mr17196393qko.779.1658150402597; Mon, 18 Jul 2022 06:20:02 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id bt14-20020ac8690e000000b0031ef0081d77sm2126027qtb.79.2022.07.18.06.20.01 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 18 Jul 2022 06:20:02 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 19977800DDD; Mon, 18 Jul 2022 08:20:01 -0500 (CDT) Date: Mon, 18 Jul 2022 08:20:00 -0500 From: Justin Pryzby To: "David G. Johnston" Cc: Alvaro Herrera , Matthias van de Meent , pgsql-hackers@postgresql.org Subject: Re: MERGE and parsing with prepared statements Message-ID: <20220718132000.GB18011@telsasoft.com> References: <20220715175854.GM18011@telsasoft.com> <20220715184019.rour3mk33ngc76y6@alvherre.pgsql> <20220715194013.GN18011@telsasoft.com> <20220715204341.GO18011@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20220715204341.GO18011@telsasoft.com> 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 On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > Should that sentence be removed from MERGE ? Also, I think these examples should be more similar. doc/src/sgml/ref/merge.sgml > > MERGE INTO CustomerAccount CA > USING RecentTransactions T > ON T.CustomerId = CA.CustomerId > WHEN MATCHED THEN > UPDATE SET Balance = Balance + TransactionValue > WHEN NOT MATCHED THEN > INSERT (CustomerId, Balance) > VALUES (T.CustomerId, T.TransactionValue); > > > > > Notice that this would be exactly equivalent to the following > statement because the MATCHED result does not change > during execution. > > > MERGE INTO CustomerAccount CA > USING (Select CustomerId, TransactionValue From RecentTransactions) 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; > > The "ON" lines can be the same. The "MATCHED" can be in the same order. -- Justin