Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E36876408C5
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 30 Apr 2009 18:11:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 74564-01-7 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 30 Apr 2009 18:11:12 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.234])
	by mail.postgresql.org (Postfix) with ESMTP id 519C66417E9
	for <pgsql-sql@postgresql.org>; Thu, 30 Apr 2009 07:12:17 -0300 (ADT)
Received: by rv-out-0506.google.com with SMTP id k40so1422054rvb.43
	for <pgsql-sql@postgresql.org>; Thu, 30 Apr 2009 03:12:16 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=Mr9sd5yns5aAoIq3uf2EK6pRUQ52JojvhHuXjWb2wkc=;
	b=A8nSxwbjXtR0apSAlbZMlp+n8pxM1VywcpNMNkGgcHTGAGCLnFsDBgbsJHdCr7UNJq
	Ij8ziEGWvQoSFkkS5cETPZ3kJUFE/kt2HuqKVx6pRC1VqIC9b15fAFgyhwcCp5eE9I0J
	LlxbyEeiaPJGsPToS2qCe7KfdHCX7QNNAH/KQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=ost9fYARqhdP4D5ms4rKC6FroFK0qAaJTXhckHOXlaTiA7knYKlcuMac7FRB9btyPU
	Ud8fKmNpNgvX9z1EBAIWdil688qBQbxMYXvCyTdNqukzDLeStjWqFHrRTVIAzTR5GXnV
	P3pvyy1SSRFF+AGj/7XcxEQopq0mrRcVhg/NU=
MIME-Version: 1.0
Received: by 10.141.41.12 with SMTP id t12mr457584rvj.289.1241086336212; Thu,
	30 Apr 2009 03:12:16 -0700 (PDT)
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>
Date: Thu, 30 Apr 2009 15:42:16 +0530
Message-ID: <e13d0d10904300312j5ec7baa1l3ae91724f6f5dbf8@mail.gmail.com>
Subject: Re: Comparing two tables of different database
From: Nicholas I <nicholas.domnic.i@gmail.com>
To: Joshua Tolley <eggyknap@gmail.com>
Cc: Adam Ruth <adamruth@mac.com>, Pawel Socha <pawel.socha@gmail.com>,
	pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0cd151fa34a1ef0468c2ee29
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200904/141
X-Sequence-Number: 32487

--000e0cd151fa34a1ef0468c2ee29
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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-----
>
>

--000e0cd151fa34a1ef0468c2ee29
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi All, <br><br>=A0 For example, <br><br>=A0=A0=A0 There are two database.=
=A0=A0 database1 and database 2;<br>=A0=A0=A0 <br>=A0=A0=A0=A0 database1 ha=
s a table called pr_1 with the columns, id,name and time.<br>=A0=A0=A0=A0 d=
atabase2 has a table called sr_1 with the_columns id,name and time.<br>
=A0=A0 <br>=A0=A0=A0=A0 i would like to find out the differences that is, f=
ind the names that are not in sr_1 but in pr_1.<br>=A0=A0=A0=A0 we can achi=
eve this by the query, <br>=A0=A0 <br>=A0=A0=A0=A0=A0=A0=A0=A0 select name =
from sr_1 where name not in (select name from pr_1);<br>
=A0=A0=A0 the above query will work in case of two tables in the same datab=
ase.<br><br><br>=A0=A0=A0=A0 But the problem is, these two tables are in di=
fferent database. i did not understand about the dblink.<br><br>=A0=A0=A0 i=
s there any exaples on dblink. can we do it without using dblink.<br>
<br>-Nicholas I<br>=A0=A0=A0 <br>=A0=A0=A0=A0=A0 <br><br><div class=3D"gmai=
l_quote">On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <span dir=3D"ltr">&=
lt;<a href=3D"mailto:eggyknap@gmail.com">eggyknap@gmail.com</a>&gt;</span> =
wrote:<br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
 rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class=3D"im">On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote=
:<br>
&gt; =A0 =A0The simple answer is to pg_dump both tables and compare the out=
put with<br>
&gt; =A0 =A0diff.<br>
&gt; =A0 =A0Other than that, I think you&#39;ll need a custom program.<br>
<br>
</div>For all but the strictest definition of &quot;identical&quot;, that w=
on&#39;t work.<br>
Tables may easily contain the same information, in different on-disk<br>
order, and pg_dump will most likely give the data to you in an order<br>
similar to its ordering on disk.<br>
<br>
Something like a COPY (&lt;query&gt;) TO &lt;file&gt;, where &lt;query&gt; =
includes an<br>
ORDER BY clause, might give you a suitable result from both tables, on<br>
which you could then take a checksum.<br>
<br>
- Josh / eggyknap<br>
<br>-----BEGIN PGP SIGNATURE-----<br>
Version: GnuPG v1.4.9 (GNU/Linux)<br>
<br>
iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC<br>
rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh<br>
=3DLO6r<br>
-----END PGP SIGNATURE-----<br>
<br></blockquote></div><br>

--000e0cd151fa34a1ef0468c2ee29--