Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e7TJl-0007Ex-Bf for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 21:34:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e7TJk-0006VZ-3G for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 21:34:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e7TJj-0006Tj-ED for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 21:34:27 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e7TJg-0002sU-0a for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 21:34:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=gB9egneFlfRJr1k/6lpoavR8lHxVzIT4ZG23DtNQb2U=; b=femcSUTBCQZWP4Bto1NrbIrDzeeMaBimk1Ey/KlZ3wDgXyOB8O8jsDGIU6ZDYzeIZs/MPAwx9pPajRYaKo46cgOTbfbIkUaljj0mO32t2v3PgBpk1jGkzXMdCA4IdLZCJ8vuDFT41W8Ia68rlIzIkLGtt1Rb/cWyJiXa1Qh9hpg=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1e7TJd-0001E3-3e for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 23:34:23 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1e7TJN-0001rY-HO for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 23:34:05 +0200 Date: Wed, 25 Oct 2017 23:34:05 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: <15480.1508900684@sss.pgh.pa.us> Subject: Re: Unable to use INSERT ... RETURNING with column from other table MIME-Version: 1.0 X-Mailer: Visena Mail 2.1.0-SNAPSHOT X-Spam-Score: 0.3 X-Spam-Report: SpamAssasin (score=0.3, required 5.0 ALL_TRUSTED=-1,HTML_IMAGE_ONLY_24=1.282,HTML_MESSAGE=0.001) Content-Type: multipart/related; boundary="----=_Part_663_1559744029.1508967245386" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_662_380407271.1508967245386 Content-Type: multipart/related; boundary="----=_Part_663_1559744029.1508967245386" ------=_Part_663_1559744029.1508967245386 Content-Type: multipart/alternative; boundary="----=_Part_664_1666926731.1508967245403" ------=_Part_664_1666926731.1508967245403 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 onsdag 25. oktober 2017 kl. 05:04:44, skrev Tom Lane >: Peter Geoghegan writes: > As David says, you could use multiple CTEs for this. Yeah.=C2=A0 The SELECT portion of the query, so far as the outer INSERT is concerned, is just a black box that yields some column values to be inserted.=C2=A0 We could wish that the INSERT's RETURNING clause could examine additional column values that are available inside that subquery, but I'm afraid that there are insurmountable semantic problems. In particular, DISTINCT seems to break that entirely --- consider insert into foo(id, name) =C2=A0 select distinct 3, f.name =C2=A0 from foo f =C2=A0 where ... returning id, f.id; We can't just add "f.id" to the set of columns returned by the SELECT part without changing the semantics of the DISTINCT.=C2=A0 Or if we ignore that (acting like it was DISTINCT ON (3, f.name)) then we get an underdetermined value of f.id, which doesn't seem appetizing either. regards, tom lane =C2=A0 Thanks for the detailed explaination. =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_664_1666926731.1508967245403 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 onsdag 25. oktober 2017 kl. 05:04:44, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Pet= er Geoghegan <pg@bowt.ie> writes:
> As David says, you could use multiple CTEs for this.

Yeah.=C2=A0 The SELECT portion of the query, so far as the outer INSERT
is concerned, is just a black box that yields some column values to
be inserted.=C2=A0 We could wish that the INSERT's RETURNING clause
could examine additional column values that are available inside that
subquery, but I'm afraid that there are insurmountable semantic problems. In particular, DISTINCT seems to break that entirely --- consider

insert into foo(id, name)
=C2=A0 select distinct 3, f.name
=C2=A0 from foo f
=C2=A0 where ...
returning id, f.id;

We can't just add "f.id" to the set of columns returned by the SE= LECT
part without changing the semantics of the DISTINCT.=C2=A0 Or if we ignore<= br> that (acting like it was DISTINCT ON (3, f.name)) then we get an
underdetermined value of f.id, which doesn't seem appetizing either.

regards, tom lane
=C2=A0
Thanks for the detailed explaination.
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_664_1666926731.1508967245403-- ------=_Part_663_1559744029.1508967245386 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_663_1559744029.1508967245386-- ------=_Part_662_380407271.1508967245386--