Postgresql – How to split one table into two in a single statement

postgresqlpostgresql-12

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?

Best Answer

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.

Related Question