How can I merge two tables?

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