In Postgres, I have an existing table:


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:


thing_id thing_name destination_id
10 thing 10 1
15 thing 15 2




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.

2 Answers 2


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;



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

  • That's much nicer than my solution, thanks. Sep 26, 2022 at 22:14

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
    (select row_number() over (order by destination_id) as join_column, destination_id from result1) did
    (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;


Your Answer

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

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