From: Nicholas I <nicholas.domnic.i@gmail.com>
To: Joshua Tolley <eggyknap@gmail.com>
Cc: Adam Ruth <adamruth@mac.com>
Cc: Pawel Socha <pawel.socha@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Comparing two tables of different database
Date: Thu, 30 Apr 2009 15:42:16 +0530
Message-ID: <e13d0d10904300312j5ec7baa1l3ae91724f6f5dbf8@mail.gmail.com> (raw)
In-Reply-To: <20090430033747.GB1126@eddie>
References: <e13d0d10904282339r1f853e1cpe3514e53ae47e5ef@mail.gmail.com>
<cc4f12900904290533x5e1871fdu8f8a27dc956ca616@mail.gmail.com>
<328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com>
<20090430033747.GB1126@eddie>
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@gmail.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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC
> rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh
> =LO6r
> -----END PGP SIGNATURE-----
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nicholas.domnic.i@gmail.com, eggyknap@gmail.com, adamruth@mac.com, pawel.socha@gmail.com
Subject: Re: Comparing two tables of different database
In-Reply-To: <e13d0d10904300312j5ec7baa1l3ae91724f6f5dbf8@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox