Skip site navigation (1) Skip section navigation (2)

Re: Comparing two tables of different database

From: "Edward W(dot) Rouse" <erouse(at)comsquared(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Comparing two tables of different database
Date: 2009-04-30 22:07:41
Message-ID: 006501c9c9e0$14d5c120$3e814360$@com (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Can't you use this?


select name from database2.sr_1 where name not in (select name from


My test database VM isn't running so I can't test it, but I seem to remember
that that's how I did it for a few queries of that type. This is assuming
the 2 databases are running on the same machine, like the way there is
template0 as the default and you add addition databases to the same
'instance'. If you are talking about 2 different database servers, then I
have no idea.


Edward W. Rouse


From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Nicholas I
Sent: Thursday, April 30, 2009 6:12 AM
To: Joshua Tolley
Cc: Adam Ruth; Pawel Socha; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Comparing two tables of different database


Hi All, 

  For example, 

    There are two database.   database1 and database 2;
     database1 has a table called pr_1 with the columns, id,name and time.
     database2 has a table called sr_1 with the_columns id,name and time.
     i would like to find out the differences that is, find the names that
are not in sr_1 but in pr_1.
     we can achieve this by the query, 
         select name from sr_1 where name not in (select name from pr_1);
    the above query will work in case of two tables in the same database.

     But the problem is, these two tables are in different database. i did
not understand about the dblink.

    is there any exaples on dblink. can we do it without using dblink.

-Nicholas I

On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <eggyknap(at)gmail(dot)com> wrote:

On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:
>    The simple answer is to pg_dump both tables and compare the output with
>    diff.
>    Other than that, I think you'll need a custom program.

For all but the strictest definition of "identical", that won't work.
Tables may easily contain the same information, in different on-disk
order, and pg_dump will most likely give the data to you in an order
similar to its ordering on disk.

Something like a COPY (<query>) TO <file>, where <query> includes an
ORDER BY clause, might give you a suitable result from both tables, on
which you could then take a checksum.

- Josh / eggyknap

Version: GnuPG v1.4.9 (GNU/Linux)



In response to


pgsql-sql by date

Next:From: John ZhangDate: 2009-05-01 15:55:24
Subject: Re: Comparing two tables of different database
Previous:From: Jasen BettsDate: 2009-04-30 12:08:26
Subject: Re: Comparing two tables of different database

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group