Обсуждение: Synchronize tables question....
Hi, I have a table with four text fields on two different machines that contain information about mail list passwords/software keys and similar information. Neither table has any keys or constraints. I do have dbi installed on each machine... 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)? Thanks, Jerry
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.