Re: continuous copy/update one table to another

2010/2/28 John R Pierce <pierce(at)hogranch(dot)com>

> Terry wrote:
>
>> Hello,
>>
>> I am looking for a way to copy all the data from one table to another
>> on a regular basis, every 5 minutes let's say.
>>
>> INSERT INTO table2 SELECT * FROM table1;
>>
>> The above will copy all the data as is and insert it into the other
>> table. What happens if I rerun it again? Will it just append table1
>> again into table2? How can I have it only insert rows that are
>> different? Would that be a program to lookup the most last record in
>> table 1 and then a query to only select after that row for the insert
>> into table2?
>>
>>
>>
>
> both tables should have a serial 'id' (or bigserial if you expect over 2
> billion entries), and use something like ...
>
> insert into table2 select * from table1 as t1 where t1.id >
> (select max(t.id) from table2 as t);
>
>
> i haven't tested this but I think it should work. as long as id is indexed
> in both tables
>
>
Different doesn't mean that the id should be greater or lower, rather should
be different. I'd rather do something like:

insert into table2 select * from table1 as t1 where not exists (select 42
from table2 as t2 where t2.id = t1.id);

of course assuming that the primary key is id;

Another problem is that it wouldn't copy changed records (this should rather
be done using some triggers)

Szymon Guz