Postgresql moving data from table to 3 other tables-CodePudding

I have a table:

TABLE_A

id_table_a
field1
field2
field3
field4
field5

I need to move the data to 3 tables, 1 parent with 2 children:

TABLE_B

id_table_b
id_table_a
field1
field2

TABLE_C

id_table_c
id_table_b
field3
field4

TABLE_D

id_table_d
id_table_b
field5

We're talking about millions of registers. What would be the correct and most effective way to do this?

I'm completely new to PostgreSQL and I've come up with this after reading the documentation:

INSERT INTO table_b (id_table_a, field1, field2) SELECT id_table_a FROM table_a, SELECT field1 FROM table_a, SELECT field2 FROM table_a;

INSERT INTO table_c (id_table_b, field3, field4) SELECT id_table_b FROM table_b, SELECT field3 FROM table_a WHERE table_b.id_table_a = table_a.id_table_a, SELECT field4 FROM table_a WHERE table_b.id_table_a = table_a.id_table_a;

INSERT INTO table_d (id_table_d, field5) SELECT id_table_c FROM table_c, SELECT field5 FROM table_a WHERE table_b.id_table_a = table_a.id_table_a;

Would this do what I need or am I missing something? Thank you.

CodePudding user response：

This will not work:

INSERT INTO table_b (id_table_a, field1, field2) SELECT id_table_a FROM table_a, SELECT field1 FROM table_a, SELECT field2 FROM table_a;

because the query SELECT id_table_a FROM table_a will (or can) return more than 1 value.

You need to write it like:

INSERT INTO table_b (id_table_a, field1, field2) 
   SELECT id_table_a, field1, field2 FROM table_a;

CodePudding user response：

Maybe sub-optimal but a straightforward PL/pgSQL do block will help. Pls. note the returning into clause. Assuming that id_table_b, id_table_c and id_table_d are autogenerated integers, then

DO language plpgsql
$$
declare
  r record;
  var_id_table_b integer;
begin
 for r in select * from table_a loop
   insert into table_b (id_table_a, field1, field2) 
     values (r.id_table_a, r.field1, r.field2) 
     RETURNING id_table_b INTO var_id_table_b;
   insert into table_c (id_table_b, field3, field4) 
     values (var_id_table_b, r.field3, r.field4);
   insert into table_d (id_table_b, field5) 
     values (var_id_table_b, r.field5);
 end loop;
end;
$$;