Synchronize tables question....

Don't forget to CC: the list

Jerry LeVan wrote:

Is there an elegant way I can merge/update the two tables so that
they will contain the same information ( with no duplicates or
omissions)?

It sounds like you'll want some form of replication, but whether
single-master or multi-master no-one can say without more information.

I meant to say I had dblink installed :)

The following sequence of steps seems to work ok, the tables are very
small...

insert into tmpRegistrations select *
from dblink('select * from registrations ')
as (software text,
id text,
value text,
location text
)

select dblink_disconnect()

create temp table newregistrations as
(select * from registrations union select * from tmpregistrations )

truncate table registrations

insert into registrations select * from newregistrations

A couple of points:
1. What if a registration is updated?
2. What happens if you get a new registration between CREATE TEMP TABLE
and TRUNCATE?

--
Richard Huxton
Archonet Ltd

On Feb 19, 2007, at 9:05 AM, Richard Huxton wrote:

Don't forget to CC: the list

Jerry LeVan wrote:

Is there an elegant way I can merge/update the two tables so that
they will contain the same information ( with no duplicates or
omissions)?

It sounds like you'll want some form of replication, but whether
single-master or multi-master no-one can say without more
information.

I meant to say I had dblink installed :)
The following sequence of steps seems to work ok, the tables are
very small...

insert into tmpRegistrations select *
from dblink('select * from registrations ')
as (software text,
id text,
value text,
location text
)
select dblink_disconnect()
create temp table newregistrations as
(select * from registrations union select * from
tmpregistrations )
truncate table registrations
insert into registrations select * from newregistrations

A couple of points:
1. What if a registration is updated?
2. What happens if you get a new registration between CREATE TEMP
TABLE and TRUNCATE?

--
Richard Huxton
Archonet Ltd

While a "generic" solution would be nice, I am the only one using the
DB :)
(so point 2 is unlikely).

Point 1 will require a bit of thought..., currently I would have to do
a manual scan of the (small) table after the merge.

Thanks

Jerry

Jerry LeVan wrote:

Is there an elegant way I can merge/update the two tables so that
they will contain the same information ( with no duplicates or
omissions)?

Well comparing algorithm is quite complex and depend on data.
You can use third patry software like
http://www.sqlmanager.net/en/products/postgresql/datacomparer
--
View this message in context: http://www.nabble.com/Synchronize-tables-question....-tf3242977.html#a9055565
Sent from the PostgreSQL - general mailing list archive at Nabble.com.