Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e79T6-0003Ce-13 for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 00:22:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e79T5-00023a-Bv for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 00:22:47 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e79S4-00008l-5Z for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 00:21:44 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e79S0-0003yM-MX for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 00:21:43 +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=aC/TS0EksrCbsFWMIuXmzX+AXiTfCKv/7sLSdXSpHww=; b=sI5/q3z4VDq87nXrCBXddZDjJVi9W5jjB8W3WFVjCqxsLAEWKXk/EdmWR42ZcXb15F/aTfFK0iBBDSMnex0i2kYjLnY5cQV1Yqzw1ZrtGG4fgEQxy8cFF98NSXZpGHspz3C6TJ1QPrPr+Zw7xEd2XeEju/Op+sMWPAyQmu46/lI=; 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 1e79Rv-0007Xm-1s for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 02:21:37 +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 1e79Rf-0002vI-Me for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 02:21:19 +0200 Date: Wed, 25 Oct 2017 02:21:19 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: 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: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) Content-Type: multipart/related; boundary="----=_Part_421_314105840.1508890879514" 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_420_2048614977.1508890879514 Content-Type: multipart/related; boundary="----=_Part_421_314105840.1508890879514" ------=_Part_421_314105840.1508890879514 Content-Type: multipart/alternative; boundary="----=_Part_422_1486421103.1508890879542" ------=_Part_422_1486421103.1508890879542 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 onsdag 25. oktober 2017 kl. 00:49:05, skrev Andreas Joseph Krogh < andreas@visena.com >: P=C3=A5 onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan >: On Tue, Oct 24, 2017 at 3:04 PM, Andreas Joseph Krogh wrote: > insert into foo(id, name) values(1, 'one'), (2, 'two'); > > insert into foo(id, name) select 3, f.name from foo f where f.id =3D 1= =20 returning id, f.id; > > ERROR:=C2=A0 missing FROM-clause entry for table "f" > LINE 1: ...lect 3, f.name from foo f where f.id =3D 1 returning id, f.id= ; > > I'd like to return f.id and the inserted id, is this possible? It's possible on 9.5+. You need to assign the target table an alias using AS -- AS in not a noise word for INSERT (the grammar requires it). See the INSERT documentation. =C2=A0 I'm not sure how an alias for=C2=A0the target_table will help me here as I'= m trying=20 to return a value not being inserted? f.id is not inserted, only columns matching f.id. =C2=A0 =C2=A0 =C2=A0 What I want to accomplish is returning a value from INSERT which is part of= =20 the SELECT-expression's FROM-clause, not part of the actual inserted column= s: =C2=A0 My real-world use-case isn't quite this simple but this sample-case=20 illustrates the problem; =C2=A0 DROP TABLE IF EXISTS tbl_value; DROP TABLE IF EXISTS tbl_header; CREATE TAB= LE=20 tbl_header( idINTEGER PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE=20 tbl_value( idSERIAL PRIMARY KEY, header_id INTEGER NOT NULL REFERENCES=20 tbl_header(id),name VARCHAR NOT NULL ); INSERT INTO tbl_header(id, name) VA= LUES( 1, 'header_one'), (2, 'header_two'); INSERT INTO tbl_value(id, header_id, n= ame)=20 VALUES(1, 1, 'value 1'),(2, 1, 'value 2'),(3, 1, 'value 3') , (4, 2, 'value= 1' ),(5, 2, 'value 2'),(6, 2, 'value 3'); SELECT setval('tbl_value_id_seq', 6)= ;=20 WITHupd_h(new_header_id, header_name, old_header_id) AS ( INSERT INTO=20 tbl_header(id,name) SELECT 3, h.name FROM tbl_header h WHERE h.id =3D 1 RET= URNING=20 id,name, 1 -- need h.id here ) INSERT INTO tbl_value(header_id, name) SELEC= T=20 f.new_header_id, hv.nameFROM tbl_value hv JOIN tbl_header h ON hv.header_id= =3D h .idJOIN upd_h AS f ON hv.header_id =3D f.old_header_id ; select h.*, v.* fr= om=20 tbl_headerh JOIN tbl_value v ON v.header_id =3D h.id ORDER BY h.id, v.id; = =C2=A0 id name id header_id name 1 header_one 1 1 value 1 1 header_one 2 1 value 2= 1=20 header_one 3 1 value 3 2 header_two 4 2 value 1 2 header_two 5 2 value 2 2= =20 header_two 6 2 value 3 3 header_one 7 3 value 1 3 header_one 8 3 value 2 3= =20 header_one 9 3 value 3=20 =C2=A0 =C2=A0 =C2=A0 I need to return the value for h.id in the first INSERT: =C2=A0 WITH upd_h(new_header_id, header_name, old_header_id) AS ( INSERT INTO=20 tbl_header(id,name) SELECT 3, h.name FROM tbl_header h WHERE h.id =3D 1 RET= URNING=20 id,name, h.id ) INSERT INTO tbl_value(header_id, name) SELECT f.new_header_= id,=20 hv.nameFROM tbl_value hv JOIN tbl_header h ON hv.header_id =3D h.id JOIN up= d_h AS=20 fON hv.header_id =3D f.old_header_id ; =C2=A0 This fails with: ERROR: =C2=A0missing FROM-clause entry for table "h"=20 LINE 5: =C2=A0=C2=A0=C2=A0=C2=A0RETURNING id, name, h.id =C2=A0 Is what I'm trying to do possible? I'd like to avoid having to use temp-tab= les=20 and/or PLPgSQL for this as I need to insert many such values in large batch= es... =C2=A0 Thanks. =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_422_1486421103.1508890879542 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 onsdag 25. oktober 2017 kl. 00:49:05, skrev Andreas Joseph Kro= gh <andreas@visena.com>:
P=C3=A5 onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan &l= t;pg@bowt.ie>:
On = Tue, Oct 24, 2017 at 3:04 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> insert into foo(id, name) values(1, 'one'), (2, 'two');
>
> insert into foo(id, name) select 3, f.name from foo f where f.id =3D 1= returning id, f.id;
>
> ERROR:=C2=A0 missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id =3D 1 returning id, f.= id;
>
> I'd like to return f.id and the inserted id, is this possible?

It's possible on 9.5+. You need to assign the target table an alias
using AS -- AS in not a noise word for INSERT (the grammar requires
it).

See the INSERT documentation.
=C2=A0
I'm not sure how an alias for=C2=A0the target_table will help me here = as I'm trying to return a value not being inserted?
f.id is not inserted, only columns matching f.id.
=C2=A0
=C2=A0
=C2=A0
What I want to accomplish is returning a value from INSERT which is pa= rt of the SELECT-expression's FROM-clause, not part of the actual inserted = columns:
=C2=A0
My real-world use-case isn't quite this simple but this sample-case il= lustrates the problem;
=C2=A0
DROP TABLE IF EXISTS tbl_value;
DROP TABLE IF EXISTS tbl_header;

CREATE TABLE tbl_hea=
der(
    id INTEGER PRIMARY KEY<=
/span>,
    name VARCHAR NOT NULL
);

CREATE TABLE tbl_val=
ue(
    id SERIAL PRIMARY KEY,
    header_id INTEGER NOT N=
ULL REFERENCES tbl_header(id),
    name VARCHAR NOT NULL
);

INSERT INTO tbl_head=
er(id, name) VALUES(1, 'heade=
r_one'), (2, 'header_two');

INSERT INTO tbl_valu=
e(id, header_id, name)
VALUES(1, 1, 'value 1'),(2, 1, 'value 2'),(3, 1, 'value 3')
    , (4, 2, 'value 1'),(5, 2, 'value 2'),(6, 2, 'value 3');

SELECT setval('tbl_value_id_seq', 6);

WITH upd_h(new_heade=
r_id, header_name, old_header_id) AS (
    INSERT INTO tbl_=
header(id, name)
        SELECT 3, h.name
        FROM tbl_hea=
der h WHERE h.id =3D=
 1
    RETURNING id, name, 1 -- need h.id here
)
    INSERT INTO tbl_=
value(header_id, name)
SELECT f.new_header_=
id, hv.name
FROM tbl_value hv
    JOIN tbl_header =
h ON hv.header_id =
=3D h.id
    JOIN upd_h AS f ON hv.header_id =3D f.old_header_id
;

select h.*, v.* from tbl_header h JOIN tbl_value v ON v.header_id =3D h.id ORDER BY h.id, v.id;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
idnameidheader_idname
1header_one11value 1
1header_one21value 2
1header_one31value 3
2header_two42value 1
2header_two52value 2
2header_two62value 3
3header_one73value 1
3header_one83value 2
3header_one93value 3
=C2=A0
=C2=A0
=C2=A0
I need to return the value for h.id in the first INSERT:
=C2=A0
WITH upd_h=
(new_header_id, header_name, old_header_id) AS (
    INSERT INTO <=
/span>tbl_header(id, name)
        SELECT 3, h.name
        FROM tbl_header h W=
HERE h.id =3D 1
    RETURNING id, name, h.id<=
span style=3D"color: rgb(0, 0, 255);">
)
    INSERT INTO <=
/span>tbl_value(header_id, name)
SELECT f.n=
ew_header_id, hv.name
FROM tbl_v=
alue hv
    JOIN t=
bl_header h ON hv.header_id =3D h.id
    JOIN u=
pd_h AS f =
ON hv.head=
er_id =3D f.old_header_id
;
=C2=A0
This fails with:
ERROR: =C2=A0missing FROM-clause entry for table "h" =
LINE 5: =C2=A0=C2=A0=C2=A0=C2=A0RETURNING id, name, h.id

=C2=A0
Is what I'm trying to do possible? I'd like to avoid having to use tem= p-tables and/or PLPgSQL for this as I need to insert many such values in la= rge batches...
=C2=A0
Thanks.
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_422_1486421103.1508890879542-- ------=_Part_421_314105840.1508890879514 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_421_314105840.1508890879514-- ------=_Part_420_2048614977.1508890879514--