agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUnable to use INSERT ... RETURNING with column from other table
9+ messages / 4 participants
[nested] [flat]
* Unable to use INSERT ... RETURNING with column from other table
@ 2017-10-24 22:04 Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2017-10-24 22:04 UTC (permalink / raw)
To: pgsql-sql
Hi.
I'm having this schema:
create table foo( id integer PRIMARY KEY, name varchar not null ); insert into
foo(id,name) values(1, 'one'), (2, 'two'); insert into foo(id, name) select 3,
f.namefrom foo f where f.id = 1 returning id, f.id; ERROR: missing FROM-clause
entry for table "f" LINE 1: ...lect 3, f.name from foo f where f.id = 1
returning id, f.id;
I'd like to return f.id and the inserted id, is this possible?
I want this because I'm hoping to use this in a CTE where other selects from
this CTE will use this extra column.
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
@ 2017-10-24 22:06 ` Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Geoghegan @ 2017-10-24 22:06 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
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 = 1 returning id, f.id;
>
> ERROR: missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 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.
--
Peter Geoghegan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
@ 2017-10-24 22:49 ` Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 00:21 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 02:45 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
0 siblings, 2 replies; 9+ messages in thread
From: Andreas Joseph Krogh @ 2017-10-24 22:49 UTC (permalink / raw)
To: pgsql-sql
På onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan <pg@bowt.ie
<mailto: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 = 1
returning id, f.id;
>
> ERROR: missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 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.
I'm not sure how an alias for the 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.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
@ 2017-10-25 00:21 ` Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 01:56 ` Re: Unable to use INSERT ... RETURNING with column from other table David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2017-10-25 00:21 UTC (permalink / raw)
To: pgsql-sql
På onsdag 25. oktober 2017 kl. 00:49:05, skrev Andreas Joseph Krogh <
andreas@visena.com <mailto:andreas@visena.com>>:
På onsdag 25. oktober 2017 kl. 00:06:59, skrev Peter Geoghegan <pg@bowt.ie
<mailto: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 = 1
returning id, f.id;
>
> ERROR: missing FROM-clause entry for table "f"
> LINE 1: ...lect 3, f.name from foo f where f.id = 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.
I'm not sure how an alias for the 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.
What I want to accomplish is returning a value from INSERT which is part of
the SELECT-expression's FROM-clause, not part of the actual inserted columns:
My real-world use-case isn't quite this simple but this sample-case
illustrates the problem;
DROP TABLE IF EXISTS tbl_value; DROP TABLE IF EXISTS tbl_header; CREATE TABLE
tbl_header( idINTEGER PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE
tbl_value( idSERIAL PRIMARY KEY, header_id INTEGER NOT NULL REFERENCES
tbl_header(id),name VARCHAR NOT NULL ); INSERT INTO tbl_header(id, name) VALUES(
1, 'header_one'), (2, 'header_two'); INSERT INTO tbl_value(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);
WITHupd_h(new_header_id, header_name, old_header_id) AS ( INSERT INTO
tbl_header(id,name) SELECT 3, h.name FROM tbl_header h WHERE h.id = 1 RETURNING
id,name, 1 -- need h.id here ) INSERT INTO tbl_value(header_id, name) SELECT
f.new_header_id, hv.nameFROM tbl_value hv JOIN tbl_header h ON hv.header_id = h
.idJOIN upd_h AS f ON hv.header_id = f.old_header_id ; select h.*, v.* from
tbl_headerh JOIN tbl_value v ON v.header_id = h.id ORDER BY h.id, v.id;
id name id header_id name 1 header_one 1 1 value 1 1 header_one 2 1 value 2 1
header_one 3 1 value 3 2 header_two 4 2 value 1 2 header_two 5 2 value 2 2
header_two 6 2 value 3 3 header_one 7 3 value 1 3 header_one 8 3 value 2 3
header_one 9 3 value 3
I need to return the value for h.id in the first INSERT:
WITH upd_h(new_header_id, header_name, old_header_id) AS ( INSERT INTO
tbl_header(id,name) SELECT 3, h.name FROM tbl_header h WHERE h.id = 1 RETURNING
id,name, h.id ) INSERT INTO tbl_value(header_id, name) SELECT f.new_header_id,
hv.nameFROM tbl_value hv JOIN tbl_header h ON hv.header_id = h.id JOIN upd_h AS
fON hv.header_id = f.old_header_id ;
This fails with:
ERROR: missing FROM-clause entry for table "h"
LINE 5: RETURNING id, name, h.id
Is what I'm trying to do possible? I'd like to avoid having to use temp-tables
and/or PLPgSQL for this as I need to insert many such values in large batches...
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 00:21 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
@ 2017-10-25 01:56 ` David G. Johnston <david.g.johnston@gmail.com>
2017-10-25 21:15 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2017-10-25 01:56 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
On Tuesday, October 24, 2017, Andreas Joseph Krogh <andreas@visena.com>
wrote:
>
> WITH upd_h(new_header_id, header_name, old_header_id) AS (
> INSERT INTO tbl_header(id, name)
> SELECT 3, h.name
> FROM tbl_header h WHERE h.id = 1 RETURNING id, name, 1 -- need h.id here)
> INSERT INTO tbl_value(header_id, name)SELECT f.new_header_id, hv.nameFROM tbl_value hv
> JOIN tbl_header h ON hv.header_id = h.id
> JOIN upd_h AS f ON hv.header_id = f.old_header_id
> ;
>
>
> This fails with:
> ERROR: missing FROM-clause entry for table "h"
> LINE 5: RETURNING id, name, h.id
>
>
> Is what I'm trying to do possible? I'd like to avoid having to use
> temp-tables and/or PLPgSQL for this as I need to insert many such values in
> large batches...
>
>
Not directly that I know of. Options:
1. Updatable view.
2. In this particular example you can place the desired h.id in its own
CTE. Move the insert into a CTE. Then join the insert-returning CTE with
the input CTE.
WITH input AS ( 1 as hid ),
ins1 as ( insert select where h.id = (select hid from input) returning *)
Select * from ins1 cross join input
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 00:21 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 01:56 ` Re: Unable to use INSERT ... RETURNING with column from other table David G. Johnston <david.g.johnston@gmail.com>
@ 2017-10-25 21:15 ` Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Joseph Krogh @ 2017-10-25 21:15 UTC (permalink / raw)
To: pgsql-sql
På onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
[snip]
Not directly that I know of. Options:
1. Updatable view.
2. In this particular example you can place the desired h.id <http://h.id; in
its own CTE. Move the insert into a CTE. Then join the insert-returning CTE
with the input CTE.
WITH input AS ( 1 as hid ),
ins1 as ( insert select where h.id <http://h.id; = (select hid from
input) returning *)
Select * from ins1 cross join input
This gives too many duplicate rows in my use-case (at first, I think I solved
it at the end of this post).
Let me give a more detailed complete example:
I want to copy the set of headers with corresponding values for an
email-message to a new email-message. In this example I have message 1000 and
1001, and want to copy headers (with list of values) for message 1001 to
message 1002.
DROP TABLE IF EXISTS tbl_value; DROP TABLE IF EXISTS tbl_header; DROP TABLE IF
EXISTStbl_message; CREATE TABLE tbl_message( id INTEGER PRIMARY KEY, name
VARCHAR NOT NULL); CREATE TABLE tbl_header( id SERIAL PRIMARY KEY, message_id
INTEGER NOT NULL REFERENCEStbl_message(id), name VARCHAR NOT NULL, index
INTEGER NOT NULL, UNIQUE(message_id, name), UNIQUE(message_id, index) ); CREATE
TABLEtbl_value( id SERIAL PRIMARY KEY, header_id INTEGER NOT NULL REFERENCES
tbl_header(id),name VARCHAR NOT NULL, index INTEGER NOT NULL, UNIQUE(header_id,
name), UNIQUE(header_id, index) ); INSERT INTO tbl_message(id, name) VALUES(1000
,'Message'), (1001, 'Message 2'), (1002, 'Message 3'); INSERT INTO
tbl_header(id, message_id,name, index) VALUES(1, 1000, 'header_one msg1', 0), (2
,1000, 'header_two msg1', 1); INSERT INTO tbl_header(id, message_id, name, index
)VALUES(3, 1001, 'header_one msg2', 0), (4, 1001, 'header_two msg2', 1); INSERT
INTOtbl_value(id, header_id, name, index) VALUES(1, 1, 'msg1 h1 value 1', 0),(2,
1, 'msg1 h1 value 2', 1),(3, 1, 'msg1 h1 value 3', 2) , (4, 2, 'msg1 h2 value 1'
,0),(5, 2, 'msg1 h2 value 2', 1),(6, 2, 'msg1 h2 value 3', 2) , (7, 3, 'msg2 h1
value 1', 0),(8, 3, 'msg2 h1 value 2', 1),(9, 3, 'msg2 h1 value 3', 2) , (10, 4,
'msg2 h2 value 1', 0),(11, 4, 'msg2 h2 value 2', 1),(12, 4, 'msg2 h2 value 3', 2
) ;SELECT setval('tbl_header_id_seq', 4); SELECT setval('tbl_value_id_seq', 12);
-- headers and values for message 1000 SELECT h.message_id, v.header_id, h
.name,h.index, v.id AS value_id, v.name, v.index FROM tbl_header h JOIN
tbl_value vON h.id = v.header_id WHERE h.message_id = 1000 ORDER BY h.index ASC
, v.indexASC;
message_id header_id name index value_id name index 1000 1 header_one msg1 0 1
msg1 h1 value 1 0 1000 1 header_one msg1 0 2 msg1 h1 value 2 1 1000 1
header_one msg1 0 3 msg1 h1 value 3 2 1000 2 header_two msg1 1 4 msg1 h2 value 1
0 1000 2 header_two msg1 1 5 msg1 h2 value 2 1 1000 2 header_two msg1 1 6 msg1
h2 value 3 2
-- headers and values for message 1001 SELECT h.message_id, v.header_id, h
.name,h.index, v.id AS value_id, v.name, v.index FROM tbl_header h JOIN
tbl_value vON h.id = v.header_id WHERE h.message_id = 1001 ORDER BY h.index ASC
, v.indexASC;
message_id header_id name index value_id name index 1001 3 header_one msg2 0 7
msg2 h1 value 1 0 1001 3 header_one msg2 0 8 msg2 h1 value 2 1 1001 3
header_one msg2 0 9 msg2 h1 value 3 2 1001 4 header_two msg2 1 10 msg2 h2 value
1 0 1001 4 header_two msg2 1 11 msg2 h2 value 2 1 1001 4 header_two msg2 1 12
msg2 h2 value 3 2
Now, try to copy all headers, with values, for message 1001 to message 1002;
WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h
.message_id =1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, name,
index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM input h
RETURNINGid ) INSERT INTO tbl_value(header_id, name, index) SELECT f.id AS
new_header_id, hv.name, hv.indexFROM tbl_value hv JOIN input h ON hv.header_id =
h.id CROSS JOIN ins1 AS f;
This fails with:
ERROR: duplicate key value violates unique constraint
"tbl_value_header_id_index_key"
DETAIL: Key (header_id, index)=(5, 0) already exists.
The reason is that the CROSS JOIN with ins1 gives too many duplicate rows:
(has plain SELECT instead of INSERT from the CTEs)
WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h
.message_id =1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, name,
index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM input h
RETURNINGid ) SELECT f.id AS new_header_id, hv.id, hv.name, hv.index FROM
tbl_value hvJOIN input h ON hv.header_id = h.id CROSS JOIN ins1 AS f;
new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9
msg2 h1 value 3 2 5 10 msg2 h2 value 1 0 5 11 msg2 h2 value 2 1 5 12 msg2 h2
value 3 2 6 7 msg2 h1 value 1 0 6 8 msg2 h1 value 2 1 6 9 msg2 h1 value 3 2 6 10
msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h2 value 3 2
Instead, I want this:
new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9
msg2 h1 value 3 2 6 10 msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h2
value 3 2
I figured that if I also JOIN'ed on tbl_header.index then it works:
WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h
.message_id =1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, name,
index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM input h
RETURNINGid, index ) SELECT f.id AS new_header_id, hv.id, hv.name, hv.index FROM
tbl_value hvJOIN input h ON hv.header_id = h.id JOIN ins1 AS f ON f.index = h
.index;
new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9
msg2 h1 value 3 2 6 10 msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h2
value 3 2
Which then results in this query for also producing the header-values:
WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h
.message_id =1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, name,
index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM input h
RETURNINGid, index ) INSERT INTO tbl_value(header_id, name, index) SELECT f.id
ASnew_header_id, hv.name, hv.index FROM tbl_value hv JOIN input h ON
hv.header_id =h.id JOIN ins1 AS f ON f.index = h.index;
This seems to produce correct results:
-- headers and values for message 1002 SELECT h.message_id, v.header_id, h
.name, v.idAS value_id, v.name FROM tbl_header h JOIN tbl_value v ON h.id =
v.header_idWHERE h.message_id = 1002;
message_id header_id name value_id name 1002 5 header_one msg2 13 msg2 h1
value 1 1002 5 header_one msg2 14 msg2 h1 value 2 1002 5 header_one msg2 15
msg2 h1 value 3 1002 6 header_two msg2 16 msg2 h2 value 1 1002 6 header_two msg2
17 msg2 h2 value 2 1002 6 header_two msg2 18 msg2 h2 value 3
Do anybody see anything potentially wrong with this, or ways to simplify it?
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
@ 2017-10-25 02:45 ` Peter Geoghegan <pg@bowt.ie>
2017-10-25 03:04 ` Re: Unable to use INSERT ... RETURNING with column from other table Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Peter Geoghegan @ 2017-10-25 02:45 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
On Tue, Oct 24, 2017 at 3:49 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> I'm not sure how an alias for the 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.
I must have had my wires crossed.
As David says, you could use multiple CTEs for this.
--
Peter Geoghegan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 02:45 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
@ 2017-10-25 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-25 21:34 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2017-10-25 03:04 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
Peter Geoghegan <pg@bowt.ie> writes:
> As David says, you could use multiple CTEs for this.
Yeah. 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. 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)
select distinct 3, f.name
from foo f
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. 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Unable to use INSERT ... RETURNING with column from other table
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Re: Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 02:45 ` Re: Unable to use INSERT ... RETURNING with column from other table Peter Geoghegan <pg@bowt.ie>
2017-10-25 03:04 ` Re: Unable to use INSERT ... RETURNING with column from other table Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-10-25 21:34 ` Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Joseph Krogh @ 2017-10-25 21:34 UTC (permalink / raw)
To: pgsql-sql
På onsdag 25. oktober 2017 kl. 05:04:44, skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>>:
Peter Geoghegan <pg@bowt.ie> writes:
> As David says, you could use multiple CTEs for this.
Yeah. 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. 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)
select distinct 3, f.name
from foo f
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. 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
Thanks for the detailed explaination.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2017-10-25 21:34 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-24 22:04 Unable to use INSERT ... RETURNING with column from other table Andreas Joseph Krogh <andreas@visena.com>
2017-10-24 22:06 ` Peter Geoghegan <pg@bowt.ie>
2017-10-24 22:49 ` Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 00:21 ` Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 01:56 ` David G. Johnston <david.g.johnston@gmail.com>
2017-10-25 21:15 ` Andreas Joseph Krogh <andreas@visena.com>
2017-10-25 02:45 ` Peter Geoghegan <pg@bowt.ie>
2017-10-25 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-10-25 21:34 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox