How to split one table into two in a single statement?
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&lt;>fiddle here

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