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 1ppB3Y-000178-V1 for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 16:53:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppB3X-0007A2-Jy for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 16:53:19 +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 1ppB3X-00079s-8x for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 16:53:19 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppB3U-000imA-VM for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 16:53:18 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 33JGrEvO2991190; Wed, 19 Apr 2023 12:53:15 -0400 From: Tom Lane To: Alex Bolenok cc: pgsql-general@lists.postgresql.org Subject: Re: Joins of data-modifying CTE with the target table In-reply-to: References: <2983382.1681919164@sss.pgh.pa.us> Comments: In-reply-to Alex Bolenok message dated "Wed, 19 Apr 2023 12:09:12 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2991188.1681923194.1@sss.pgh.pa.us> Date: Wed, 19 Apr 2023 12:53:14 -0400 Message-ID: <2991189.1681923194@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Alex Bolenok writes: > To answer your question, by "explicitly" I mean mentioning this very > pattern, where you join the returned id with the target table. I do not see why this existing example isn't sufficient: ... An example of this is that in WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products; the outer SELECT would return the original prices before the action of the UPDATE, ... Yeah, it lacks a JOIN and it's an UPDATE not an INSERT. But we can't provide an example to precisely match every possible mistake. As Adrian said, brevity in the docs does have value. regards, tom lane