CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-replication
AzizSM
AzizSM

Reputation: 6289

Two or more tables that are mostly synchronized in database

I'm using PostgreSQL and I need to have two or more tables that are mostly synchronized in my database, such that an Add/Delete/Update to a row in sub table is made to the master table.

I'm not sure if trigger can be used in this situation because in my situation the sub table may be truncated and load in with new set of data

Any idea how to implement that ?

Example: 

Employee_Sub1:
 id    name    version
+------+-------+--------+
 101    John     1
 102    David    1
 105    Azi      1

Employee_Sub2:
 id    name    version
+------+-------+--------+
 101    John     1
 102    DavidX   2       <- Sub2 having updated value for id 102
 105    Azi      1
 107    Marry    1

Employee_Sub3:
 id    name    version
+------+-------+--------+
 101    John    1
 105    Azi     1
 110    devil   1       <- Sub3 having new row of data

The Employee_Final table shall look like this :

Employee_Final:
 id    name    version
+------+-------+--------+
 101    John    1
 102    DavidX  2
 105    Azi     1
 107    Marry   1
 110    devil   1

Upvotes: 2

Views: 191

Answers (3)

KIM
KIM

Reputation: 1254

You might use table inheritance. At first, you create master table with no data and create child table from master table with inheritance option. When you select from master table, resultset includes dataset of child table. Visit http://www.postgresql.org/docs/9.2/static/ddl-inherit.html

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19225

Normally you create a trigger on the source table and it works out whether to update or insert into the target table.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324901

I think you're looking for triggers; see PL/PgSQL trigger procedures for how to implement them in PL/PgSQL. Use AFTER INSERT OR UPDATE OR DELETE triggers on each of Employee_Sub1, Employee_Sub2, and Employee_Sub3 to propagate their changes to Employee_Final.

There's also a bit of a table inheritance feel about this question, but I'm not sure inheritance is a good fit.

Usually when you're changing IDs you use ON UPDATE CASCADE foreign key references, but that won't work here because you need it reversed, where the PK gets updated when you change the FK. That's another job for a trigger; you have to compare the NEW and OLD record IDs and update the master table if the ID has changed.

Upvotes: 2

Related Questions