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,
  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?

  • 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
    Commented Jan 27, 2020 at 2: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
    Commented Jan 27, 2020 at 2: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
    Commented Jan 27, 2020 at 3: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
    Commented Jan 27, 2020 at 3:06

1 Answer 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_table2 (id_table1 INT, val2 INT);
WITH cte AS 
INSERT INTO new_table1 (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.

  • 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
    Commented Jan 27, 2020 at 6: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
    Commented Jan 27, 2020 at 6:36
  • @l0b0 Example edited, new_table1.id is changed to identity.
    – Akina
    Commented Jan 27, 2020 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.