Postgresql – Moving data from one database to another database with auto incriment keys

auto-incrementpostgresqlprimary-key

I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id).

The questions is, I have 2 databases(db_a and db_b) that have the same schema (tables 1,2,3 with same relations). Now how can I insert data from db_b into db_a in a way that I can keep track of the correct relations between table3 and the table1 and table2.

EDIT

This is not a one time thing, it will reoccur aperiodically.

New data is inserted into db_a on daily basis, however data inserts on db_b can happen with different regularity. The issue is how to keep track of the auto generated keys for table1 and table2 when inserting table3 data into db_a

db schema

Best Answer

Just follow this... steps

These are the tables for database1

    create table if not exists table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

There are the tables for database2

    create table if not exists 2table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists 2table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists 2table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

Here we have temp data for database1

    insert into database1.table1 (data1)
        values('table_1_data_1'),
        ('table_1_data_2'),
        ('table_1_data_3');

    insert into database1.table2 (data2)
        values('table_2_data_1'),
        ('table_2_data_2'),
        ('table_2_data_3');

    insert into database1.table3 (data3,t1_id,t2_id)
        values('table_3_data_1',1,1),
        ('table_3_data_2',1,2),
        ('table_3_data_3',2,1),
        ('table_3_data_4',2,2),
        ('table_3_data_5',2,3);

temp data for database2

    insert into database2.2table1 (data1)
        values('2table_1_data_1'),
        ('2table_1_data_2'),
        ('2table_1_data_3');

    insert into database2.2table2 (data2)
        values('2table_2_data_1'),
        ('2table_2_data_2'),
        ('2table_2_data_3');

Now come to the point

Here we are copying data from database1s table1 to database2s 2table1

and

from database1s table2 to database2s 2table2

    insert into database2.2table1 (data1)
        select data1 from database1.table1;
    insert into database2.2table2 (data2)
        select data2 from database1.table2;

Now we are going to insert data into database2s 2table3

    insert into database2.2table3 (t1_id,t2_id,data3) select
      (select 2t1.id from database2.2table1 2t1 where 2t1.data1 =t1.data1 ),
      (select 2t2.id from database2.2table2 2t2 where 2t2.data2 =t2.data2 ),
      t3.data3
    from database1.table3 t3
    left join database1.table1 as t1 on t1.id = t3.t1_id
    left join database1.table2 as t2 on t2.id = t3.t2_id;

I hope it helps...

Related Question