0

I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table you're inserting into:

WITH comment_insert AS (
    INSERT INTO comments (content)
        SELECT content
          FROM old_schema.comments
          JOIN area ON area.id = old_schema.comments.area_id
        RETURNING id, area.id AS area_id
)
INSERT INTO area_comment (parent_id, area_id)
SELECT comment_insert.id,
       comment_insert.area_id
  FROM transitional_schema.mon_area_comment

Is it possible to do something like this, putting some columns in the "parent" table and the parent ID plus some other columns into another table, using a single query?

l0b0
  • 403
  • 4
  • 14
  • I don't think it's possible in any way that's worth doing. invollving any proececureal language is likely to be slower than doing the two inserts. – Jasen Jan 27 '20 at 02:49
  • @Jasen I've not said anything about procedural languages. I'm asking how to do this *in SQL.* If it's not possible using a single query I will just use two, with the associated overhead and duplication. – l0b0 Jan 27 '20 at 02:50
  • if you don'r need to keep the source table you could drop columns and rename the table for the second copy. proceural language was an example of a way that not worth doing. – Jasen Jan 27 '20 at 03:05
  • Good idea, but I don't think that's doable. I'm accessing it via a foreign data wrapper, so it's not even on the same cluster. Copying the table schema and data across before starting the migration is going to be more work than two inserts. – l0b0 Jan 27 '20 at 03:06

1 Answers1

1

Modelling scheme:

CREATE TABLE old_table (id INT, val1 INT, val2 INT);
INSERT INTO old_table VALUES (11,111,1111), (22,222,2222);

CREATE TABLE new_table1 (id INT GENERATED ALWAYS AS IDENTITY, val1 INT);
CREATE TABLE new_table2 (id_table1 INT, val2 INT);

WITH cte AS 
(
INSERT INTO new_table1 (val1)
SELECT val1
FROM old_table
RETURNING id, val1
)
INSERT INTO new_table2 (id_table1, val2)
SELECT cte.id, old_table.val2
-- use both old table
FROM old_table
-- and CTE table
JOIN cte 
    ON old_table.val1 = cte.val1; -- the relation is set 
                                  -- by the value saved into new table
                                  -- not by old primary index

SELECT * FROM new_table1;
SELECT * FROM new_table2;

id | val1
-: | ---:
 1 |  111
 2 |  222

id_table1 | val2
--------: | ---:
        1 | 1111
        2 | 2222

db<>fiddle here

Pay attention - in last INSERT both CTE and source table are used.

Akina
  • 19,866
  • 2
  • 17
  • 21
  • I have to check whether this will work, because if I understand correctly this solution relies on the primary key columns from the old table all being inserted into the parent table. – l0b0 Jan 27 '20 at 06:30
  • @l0b0 No... new_table.id may be new-valued identity field - this changes nothing. Anycase the last query takes referential value from NEW table (cte.id), not from the old one (old_table.id). The example edited. – Akina Jan 27 '20 at 06:36
  • @l0b0 Example edited, new_table1.id is changed to identity. – Akina Jan 27 '20 at 06:47