IM_AG IM_AG - 22 days ago 5
SQL Question

How do I do a deep copy with a single PostgreSQL query?

I have three tables:

CREATE TABLE offers
(
id serial NOT NULL PRIMARY KEY,
title character varying(1000) NOT NULL DEFAULT ''::character varying
);

CREATE TABLE items
(
id serial NOT NULL PRIMARY KEY,
offer_id integer NOT NULL,
title character varying(1000) NOT NULL DEFAULT ''::character varying,
CONSTRAINT items_offer_id_fkey FOREIGN KEY (offer_id)
REFERENCES offers (id)
);

CREATE TABLE sizes
(
id serial NOT NULL PRIMARY KEY,
item_id integer NOT NULL,
title character varying(1000) NOT NULL DEFAULT ''::character varying,
CONSTRAINT sizes_item_id_fkey FOREIGN KEY (item_id)
REFERENCES items (id)
);


I have 1 offer that has 2 items. Each item has 2 sizes:

INSERT INTO offers (title) VALUES ('My Offer');
INSERT INTO items (offer_id, title) VALUES (1, 'First Item');
INSERT INTO items (offer_id, title) VALUES (1, 'Second Item');
INSERT INTO sizes (item_id, title) VALUES (1, 'First Size of Item #1');
INSERT INTO sizes (item_id, title) VALUES (1, 'Second Size of Item #1');
INSERT INTO sizes (item_id, title) VALUES (2, 'First Size of Item #2');
INSERT INTO sizes (item_id, title) VALUES (2, 'Second Size of Item #2');


Is there a way to clone an offer with all its items and sizes with a single query?

I tried to solve it with CTE, here is my SQL:

WITH tmp_offers AS (
INSERT INTO offers (title)
SELECT title FROM offers WHERE id = 1
RETURNING id
), tmp_items AS (
INSERT INTO items (offer_id, title)
(SELECT (SELECT id FROM tmp_offers), title FROM items WHERE offer_id = 1)
RETURNING id
)
INSERT INTO sizes (item_id, title)
(SELECT (SELECT id FROM tmp_items), title FROM sizes WHERE id IN (
SELECT sizes.id FROM sizes
JOIN items ON items.id = sizes.item_id
WHERE items.offer_id = 1
));


But this SQL results to an error, that I can't resolve:

ERROR: more than one row returned by a subquery used as an expression

Your help is greatly appreciated.

P.S. I use PostgreSQL 9.5

Answer

This should work:

WITH tmp_offers AS (
    INSERT INTO offers (title)
    SELECT title 
    FROM offers 
    WHERE id = 1
    RETURNING id
), tmp_items AS (
    INSERT INTO items (offer_id, title)
    SELECT t.id, i.title 
    FROM items i
      cross join tmp_offers t
    WHERE i.offer_id = 1
    RETURNING items.id
)
INSERT INTO sizes (item_id, title)
SELECT i.id, s.title
FROM sizes s
  cross join tmp_items i
where s.id in (select i2.id from items i2 where i2.offer_id = 1);

Online example: http://rextester.com/RYQUS11008