For each row create a linked row in another table with auto-generated ID

Question

In Postgres, I have an existing table:

things

thing_id	thing_name	destination_id
10	thing 10	null
15	thing 15	null

For each row in that table, I want to add rows to two new related tables one of which has an auto-generated ID. I then want to update rows in the old table to point to related new rows.

Desired result:

things

thing_id	thing_name	destination_id
10	thing 10	1
15	thing 15	2

parent_destination

destination_id
1
2

child_destination

destination_id	destination_name
1	[destination] thing 10
2	[destination] thing 15

I have tried to do it here: https://www.db-fiddle.com/f/6iyvCT7BYXPHPi2N2HvNor/1 but I can't work out how to return the correct data from result1.

Answer 1

Start with the UPDATE and fetch serial IDs for parent_destination from the underlying sequence with nextval() and pg_get_serial_sequence(). Then you have the needed connection to the things table, and the rest is simple:

WITH upd AS (
   UPDATE things t
   SET    destination_id = nextval(pg_get_serial_sequence('things', 'thing_id'))
   RETURNING thing_id, thing_name, destination_id
   )
, ins_parent AS (   
   INSERT INTO parent_destination(destination_id)
   SELECT destination_id
   FROM   upd
   )
INSERT INTO child_destination(destination_id, destination_name)
SELECT destination_id, concat('[thing]', thing_name)
FROM   upd;

fiddle

Related:

• INSERT rows in two tables preserving connection to a third table
• Insert data in 3 tables at a time using Postgres

The FK constraint is not violated while we do all of it in a single command. See:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Answer 2

I worked out a way:

with result1 as (
    insert into parent_destination select from things returning destination_id
), result2 as (
    select destination_id, thing_id, thing_name
    from
    (select row_number() over (order by destination_id) as join_column, destination_id from result1) did
    join
    (select row_number() over (order by thing_id) as join_column, thing_id, thing_name from things) ts
    on did.join_column = ts.join_column
), result3 as (
    insert into child_destination(destination_id, destination_name) select destination_id, thing_name from result2
)
update things t
set destination_id = t2.destination_id
from result2 t2
where t1.thing_id = t2.thing_id;

https://www.db-fiddle.com/f/6iyvCT7BYXPHPi2N2HvNor/3