deep copy map, 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, 
  some_data integer
);

create table map_a2b
(
  a_id integer not null references table_a, 
  b_id integer not null references table_b,
  primary key (a_id, b_id)
);

insert into table_a (id, some_data) values (1, 'one'), (2, 'two');
insert into table_b (id, some_data) values (1, 10), (2, 20), (3, 30), (4, 40);
insert into map_a2b (a_id, b_id) values (1, 1), (1, 2), (1,3), (2,2), (2,4);

-- 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);

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 b.id as old_b_id, 
          a.new_a_id,
          nextval(pg_get_serial_sequence('table_b', 'id')) as new_b_id, 
          b.some_data -- other columns
   from table_b b
     join map_a2b m on m.b_id = b.id 
     join new_a_values a on a.old_a_id = m.a_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, some_data)
  select new_b_id, some_data
  from new_b_values
)
insert into map_a2b (a_id, b_id)
select new_a_id, new_b_id
from new_b_values;

select *
from table_a;

select *
from table_b;

select *
from map_a2b;


absolute service time: 0,47 sec