Обсуждение: How can I merge two tables?
Hi, I have tables on my various computers that looks like: Table "public.registrations" Column | Type | Modifiers | Storage | Description ----------+------+-----------+----------+------------- software | text | | extended | id | text | | extended | value | text | | extended | location | text | | extended | Indexes: "registrations_software_idx" btree (software) CLUSTER Has OIDs: yes I keep registration numbers for software and login/passwords for various organizations, etc… As time goes by the tables on the various computers get out of sync. Is there an elegant way I can get all of the differences (uniquely) merged into a single table? Thanks Jerry
Mmm... maybe if you can dump them as inserts you'll be able to restore them in one DB only. Important: make the restore connection autocommit (i.e. don't put the --single-transaction flag). That way individual INSERTs will fail without affecting the other data if that INSERT violates the PK. This is slow! specially with large data sets. Once you've got the data in one DB, you can use some replication system if you want to have that DB in all your machines. One of that systems is Slony-I, though I haven't tried it. -- Diego Augusto Molina diegoaugustomolina@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html
Hi, On 2 September 2011 03:09, Jerry LeVan <jerry.levan@gmail.com> wrote: > I keep registration numbers for software and login/passwords for > various organizations, etc… > > As time goes by the tables on the various computers get out of > sync. > > Is there an elegant way I can get all of the differences (uniquely) I would copy data from the other machines to the "master" one: on the "master", under postgres user (data only dump; -a options): pg_dump -a -h <host1> -t registrations -U <user> <database> -F c | pg_restore -d <database> pg_dump -a -h <host2> -t registrations -U <user> <database> -F c | pg_restore -d <database> ... pg_dump -a -h <hostN> -t registrations -U <user> <database> -F c | pg_restore -d <database> and then do the following: begin; insert into tmp select distinct * from registrations; truncate registrations; insert into registrations select * from tmp; commit; -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
On Thu, 2011-09-01 at 13:09 -0400, Jerry LeVan wrote: > As time goes by the tables on the various computers get out of > sync. > > Is there an elegant way I can get all of the differences (uniquely) > merged into a single table? You can try a query involving NOT EXISTS, combined with dblink: http://www.postgresql.org/docs/current/static/dblink.html Effectively the query would be something like: INSERT INTO registrations SELECT * FROM -- fetch remote version of table dblink(..., "SELECT * FROM registrations") AS remote_reg(...) WHERE NOT EXISTS (SELECT 1 FROM registrations local_reg WHERE local_reg.id = remote_reg.id); (disclaimer: I didn't test this query out, it's just for illustrating the idea). Regards, Jeff Davis