deep copy, PostgreSQL

Language: Editor: Layout:

drop table if exists table_a, table_b, table_c cascade;

create table table_a
(
  id serial primary key,
  some_data text
);

create table table_b
(
  id serial primary key, 
  a_id integer not null references table_a,
  some_data integer
);

create table table_c
(
  id serial primary key,
  b_id integer not null references table_b,
  some_data integer
);

insert into table_a (id, some_data) values (1, 'one');
insert into table_b (id, a_id, some_data) values (1, 1, 10), (2,1,20);
insert into table_c (id, b_id, some_data) values (1, 1, 100), (2, 1, 200), (3, 2, 300), (4, 2, 400);

-- the above used hardcoded PK values, we need to adjust the sequence
-- to avoid duplicate values. It also makes it easier to spot (and validate) the 
-- newly created rows as all copied IDs start with 100
select setval('table_a_id_seq', 100);
select setval('table_b_id_seq', 100);
select setval('table_c_id_seq', 100);

with new_a_values as (
   select a.id as old_a_id, 
          nextval(pg_get_serial_sequence('table_a', 'id')) as new_a_id, 
          'Copy of '||a.some_data as some_data -- other columns
   from table_a a
   where a.id = 1
), new_b_values as (
   select a.old_a_id, 
          a.new_a_id, 
          b.id as old_b_id, 
          nextval(pg_get_serial_sequence('table_b', 'id')) as new_b_id, 
          b.some_data -- other columns
   from new_a_values a
     join table_b b on a.old_a_id = b.a_id
), new_c_values as (
   select b.old_b_id, 
          b.new_b_id,
          c.id as old_c_id,
          nextval(pg_get_serial_sequence('table_c', 'id')) as new_c_id,
          c.some_data -- other columns
   from new_b_values b
      join table_c c on c.b_id = b.old_b_id
), new_a as (
  insert into table_a (id, some_data)
  select new_a_id, some_data
  from new_a_values
), new_b as (
  insert into table_b (id, a_id, some_data)
  select new_b_id, new_a_id, some_data
  from new_b_values
)
insert into table_c (id, b_id, some_data)
select new_c_id, new_b_id, some_data
from new_c_values;

select *
from table_a;

select *
from table_b;

select *
from table_c;


absolute service time: 0,58 sec