Postgresql code to copy one table records to another table by comparing each table

Databases
1

Have any one knows how to copy one table records to another table by comparing each tables postgresql code.

Here is the best explanation : I’ve got 2 tables Table_A and Table_B. Both tables are same. That means Table_B is clone of Table_A and i want to use Table_B as a backup table. Now i want to copy Table_A data periodically to Table_B. Here i do not want to drop Table_B and reinsert all data from Table_A.
Simply it should work as like this. The postgresql syntax code that i need is compare Table_A and Table_B. Now copy Table_A data to into Table_B which are not in Table_B.

If any one knows postgresql code for the problem plz note the whole code here. I’ll hope any one will help me.:slight_smile:

2

You should use triggers.

3

that’s a fairly terse off-the-cuff comment

would you please elaborate on exactly how to apply the use of triggers to this problem

thank you

:slight_smile:

4

He can create an “AFTER INSERT OR UPDATE OR DELETE” trigger on TABLE_A. When TABLE_A is modified, the trigger will apply the modification to TABLE_B. In this case and in the case of table copy, TABLE_B is NOT a backup table of TABLE_A (for me, a backup table does not make any sense).

The PostgreSQL manual page about triggers has many examples. That was the reason I had posted the link without further explanation.

5

trigger still seems somehow wrong to me

the requirement is “Now copy Table_A data to into Table_B which are not in Table_B.”

this is something to be done periodically, whereas a trigger takes place all the time, adding overhead to whatever processes are updating table A

6

The overhead depends on updates’ frequency. If the TABLE_A is not often modified, trigger’s overhead is small.
IMO, OP should post more details about tables’ schema, number of records and frequency of modifications. With these information, we would provide him a better answer.

ps. For tables with small number of records, a “INSERT INTO TABLE_B (SELECT * FROM TABLE_A WHERE …)” query may be a better solution.