Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E97BB632C96 for ; Thu, 30 Apr 2009 19:30:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97048-03-7 for ; Thu, 30 Apr 2009 19:29:55 -0300 (ADT) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by mail.postgresql.org (Postfix) with ESMTP id 81708641D30 for ; Thu, 30 Apr 2009 19:21:24 -0300 (ADT) X-Greylist: delayed 00:13:20.577573 by SQLgrey-1.7.6 Received: from smtp.comsquared.com (smtp.comsquared.com [209.155.237.140]) by mx3.hub.org (Postfix) with ESMTP id E7AE937B48E for ; Thu, 30 Apr 2009 19:21:23 -0300 (ADT) Received: from mdpserv.comsquared.com (mdpserv [10.82.40.1]) by smtp.comsquared.com (Switch-3.2.7/Switch-3.2.7) with ESMTP id n3UM7fxt013709 for ; Thu, 30 Apr 2009 18:07:44 -0400 (EDT) Received: from erouse052008 (erouse052008.ntc2s.comsquared.com [10.82.60.61]) by mdpserv.comsquared.com (8.13.6+Sun/8.13.6) with ESMTP id n3UM7fnx023279 for ; Thu, 30 Apr 2009 18:07:41 -0400 (EDT) From: "Edward W. Rouse" To: References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> In-Reply-To: Subject: Re: Comparing two tables of different database Date: Thu, 30 Apr 2009 18:07:41 -0400 Message-ID: <006501c9c9e0$14d5c120$3e814360$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0066_01C9C9BE.8DC42120" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcnJ2Ex6KzxPlq4pRN+QUU7Sg+QrvQABxrUw Content-Language: en-us 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/144 X-Sequence-Number: 32490 This is a multipart message in MIME format. ------=_NextPart_000_0066_01C9C9BE.8DC42120 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Can't you use this? select name from database2.sr_1 where name not in (select name from database2.pr_1); 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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Nicholas I Sent: Thursday, April 30, 2009 6:12 AM To: Joshua Tolley Cc: Adam Ruth; Pawel Socha; pgsql-sql@postgresql.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 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 () TO , where 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----- ------=_NextPart_000_0066_01C9C9BE.8DC42120 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Can’t you use this?

 

select name from database2.sr_1 where name not in (select = name from database2.pr_1);

 

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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Nicholas I
Sent: Thursday, April 30, 2009 6:12 AM
To: Joshua Tolley
Cc: Adam Ruth; Pawel Socha; pgsql-sql@postgresql.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@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
=3DLO6r
-----END PGP SIGNATURE-----

 

------=_NextPart_000_0066_01C9C9BE.8DC42120--