agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Unable to use INSERT ... RETURNING with column from other table
Date: Wed, 25 Oct 2017 23:15:31 +0200 (CEST)
Message-ID: <VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena> (raw)
In-Reply-To: <CAKFQuwZ8O8QVU8YvF6BnDD4yFxK_EgvBEuAGyoyCOyQO+N+O0g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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;
view thread (9+ messages) latest in thread
Message-ID: <VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena>
Permalink: ../VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Unable to use INSERT ... RETURNING with column from other table
In-Reply-To: <VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox