Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AE07D632B31 for ; Fri, 1 May 2009 13:21:37 -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 14080-05 for ; Fri, 1 May 2009 13:21:35 -0300 (ADT) X-Greylist: delayed 00:26:08.459942 by SQLgrey-1.7.6 Received: from mail-gx0-f167.google.com (mail-gx0-f167.google.com [209.85.217.167]) by mail.postgresql.org (Postfix) with ESMTP id E7CBB632A10 for ; Fri, 1 May 2009 13:21:35 -0300 (ADT) Received: by gxk11 with SMTP id 11so976765gxk.19 for ; Fri, 01 May 2009 09:21:35 -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:content-type; bh=gaQthIaVizCXtwFxZlgDmidn0DWKlB/nMp4I+0jlIvw=; b=R1SV/SToVbeo/kiY3W/dMVgwnuJmhWVuplsadbPrgfRJbbOtJzk8DqYIwfCPJM1ZVF F+TdL/DBPlRFRq5+Flhx5c1q7h8CkvhA34Zl2qCWwx4dFm4KNCUfgOuPHQtlHZrpndi6 /9+FYPNebyoA1YspuhEG1ZHrQKuXWrtOhdRFg= 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 :content-type; b=LZ5PMMI0xpc3M8kRV1iniVVOHD9MvPTAutQPHKbhOdWm8cPnSXxBpRNplTTPGi1ssd nW9QV0IKQUtO5SdFCN1ceee1zaRGKDS0Yb+0cfRwtoxeWYsv5LjCPclS2yh961H8YKcZ jrvFJG3ckg0EwCKoiuCZq2JoVtbAnMBSONfUg= MIME-Version: 1.0 Received: by 10.90.104.15 with SMTP id b15mr2357892agc.98.1241193325163; Fri, 01 May 2009 08:55:25 -0700 (PDT) In-Reply-To: <006501c9c9e0$14d5c120$3e814360$@com> References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> <006501c9c9e0$14d5c120$3e814360$@com> Date: Fri, 1 May 2009 08:55:24 -0700 Message-ID: <618950b80905010855y70609c47j187f16967ab0da77@mail.gmail.com> Subject: Re: Comparing two tables of different database From: John Zhang To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016362836063e75d40468dbd724 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: 200905/1 X-Sequence-Number: 32493 --0016362836063e75d40468dbd724 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Hi Nicholas, The query is across database query. dblink is needed for that task. Hope it helps, John On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse wro= te: > Can=92t you use this? > > > > select name from database2.sr_1 where name not in (select name from > database2.pr_1); > > > > My test database VM isn=92t running so I can=92t test it, but I seem to > remember that that=92s 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 th= ere > is template0 as the default and you add addition databases to the same > =91instance=92. If you are talking about 2 different database servers, th= en 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 tha= t > 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 di= d > not understand about the dblink. > > is there any exaples on dblink. can we do it without using dblink. > > -Nicholas I > > > > --0016362836063e75d40468dbd724 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
Hi Nicholas,

The query is across database query.=A0 dblink is ne= eded for that task.

Hope it helps,

John

On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse <erouse@comsquared.com= > wrote:

Can=92t you us= e this?

=A0

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

=A0

My test databa= se VM isn=92t running so I can=92t test it, but I seem to remember that that=92s 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 databas= es to the same =91instance=92. If you are talking about 2 different database servers, then I have no idea.

=A0

Edward W. Rous= e

=A0

From: pgsql-s= ql-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=

=A0

Hi All,

=A0 For example,

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


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

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

-Nicholas I
=A0=A0=A0
=A0=A0=A0=A0=A0



--0016362836063e75d40468dbd724--