Home > mailing lists

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table - Mailing list pgsql-sql

From	Andreas Joseph Krogh
Subject	Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table
Date	October 26, 2017 03:15:31
Msg-id	VisenaEmail.93.20a85b79182e70a1.15f553c19f1@tc7-visena Whole thread Raw
In response to	Re: [SQL] Unable to use INSERT ... RETURNING with column from other table ("David G. Johnston" <david.g.johnston@gmail.com>)
List	pgsql-sql

Tree view

På onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston <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 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

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 EXISTS tbl_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 REFERENCES tbl_message(id),   name VARCHAR NOT NULL,   index INTEGER NOT NULL,   UNIQUE(message_id, name),   UNIQUE(message_id, index)
);

CREATE TABLE tbl_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 INTO tbl_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 v ON h.id = v.header_id
WHERE h.message_id = 1000
ORDER BY h.index ASC, v.index ASC;

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 v ON h.id = v.header_id
WHERE h.message_id = 1001
ORDER BY h.index ASC, v.index ASC;

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
    RETURNING id
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_header_id, hv.name, hv.index
FROM 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
    RETURNING id
)
SELECT f.id AS new_header_id, hv.id, hv.name, hv.index
FROM tbl_value hv   JOIN 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
    RETURNING id, index
)
SELECT f.id AS new_header_id, hv.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;

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
    RETURNING id, index
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_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.id AS value_id, v.name
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE 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

www.visena.com

pgsql-sql by date:

From: Tom Lane
Date: 25 October 2017, 09:04:44
Subject: Re: [SQL] Unable to use INSERT ... RETURNING with column from other table

From: Andreas Joseph Krogh
Date: 26 October 2017, 03:34:05
Subject: Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table - Mailing list pgsql-sql

Previous

Next