Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DF9C86324D7 for ; Fri, 1 May 2009 14:01:15 -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 02677-02 for ; Fri, 1 May 2009 14:01:14 -0300 (ADT) X-Greylist: delayed 00:30:00.373864 by SQLgrey-1.7.6 Received: from mta2.srv.hcvlny.cv.net (mta2.srv.hcvlny.cv.net [167.206.4.197]) by mail.postgresql.org (Postfix) with ESMTP id B588A632237 for ; Fri, 1 May 2009 14:01:14 -0300 (ADT) Received: from [192.168.1.103] (ool-60383673.static.optonline.net [96.56.54.115]) by mta2.srv.hcvlny.cv.net (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTP id <0KIZ0046J4K0A7D0@mta2.srv.hcvlny.cv.net> for pgsql-sql@postgresql.org; Fri, 01 May 2009 12:31:12 -0400 (EDT) Date: Fri, 01 May 2009 12:31:12 -0400 From: Wei Weng Subject: Re: Comparing two tables of different database In-reply-to: <618950b80905010855y70609c47j187f16967ab0da77@mail.gmail.com> To: pgsql-sql@postgresql.org Message-id: <49FB23D0.10403@kencast.com> MIME-version: 1.0 Content-type: text/plain; charset=windows-1252; format=flowed Content-transfer-encoding: 8BIT References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> <006501c9c9e0$14d5c120$3e814360$@com> <618950b80905010855y70609c47j187f16967ab0da77@mail.gmail.com> User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200905/2 X-Sequence-Number: 32494 On 05/01/2009 11:55 AM, John Zhang wrote: > > 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 > > wrote: > > 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 > How do you formulate the query using dblink? Thanks Wei