Postgres Pro
Downloads
Home   >   mailing lists

Thread: How can I merge two tables?

How can I merge two tables?

From
Jerry LeVan
Date:

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

Re: How can I merge two tables?

From
Diego Augusto Molina
Date:
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

Re: How can I merge two tables?

From
Ondrej Ivanič
Date:
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)

Re: How can I merge two tables?

From
Jeff Davis
Date:
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