Re: Unable to use INSERT ... RETURNING with column from other table

På onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>