Question 115015 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql moving data from table to 3 other tables

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search