3

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.

2 Answers 2

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:

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

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

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

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.