Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6AC36632B31 for ; Sat, 2 May 2009 16:13:58 -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 30476-01-5 for ; Sat, 2 May 2009 16:13:56 -0300 (ADT) X-Greylist: domain auto-whitelisted 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 853D5634231 for ; Sat, 2 May 2009 15:52:37 -0300 (ADT) Received: by gxk11 with SMTP id 11so2082567gxk.19 for ; Sat, 02 May 2009 11:52:25 -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=JgzT7CxwM8i+fKCHd+UvFgxTlHYS2vKgXUTxBK+/zWo=; b=Z8WA8qN6EOda7HsdikZ18KhEttIahrb4PaPRoHgqNpi8P43ZkJxZFtxe0Af1FOoDlc St59aDx/pSVZYqw1FLqafQwmnN3p5c2XBCY6QBS2JeIlZUwm39yTE8PPPMyFu6x/TW8m QKGYrTjGAWHedshnT7iHmd3K70X0WWMkYmP48= 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=djv2sS7SUNXE0j7EISVBITumcoCuo1Sss+B69fg1qr2R+mG4YXfw6/cCfSqY57+4iC xFRNTpRTgH8gi78XPrTh3vQ7iMTIOKrFkfEj5PdG7fHnSyrn4d4FRRIPfR0n+TcNbKnQ 93mn+Nv9U3Vq6Sk82uJG2AZCw5tST0sPYY0ZA= MIME-Version: 1.0 Received: by 10.151.68.11 with SMTP id v11mr8267914ybk.30.1241290345898; Sat, 02 May 2009 11:52:25 -0700 (PDT) In-Reply-To: References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> Date: Sat, 2 May 2009 14:52:25 -0400 Message-ID: Subject: Fwd: Comparing two tables of different database From: Isaac Dover To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001e680f10d42161f70468f26e1e 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/6 X-Sequence-Number: 32498 --001e680f10d42161f70468f26e1e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit i've not tried this in postgres, but using information_schema makes comparing structures of databases trivial. i've been using this query for a while with MSSQL. Maybe this helps to answer the question. - isaac select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, * from [database].information_schema.Columns ST full outer join [other database].information_schema.Columns DV on ST.Table_Name = DV.Table_name and ST.Column_Name = DV.Column_Name where ST.Column_Name is null or DV.Column_Name is NULL On Sat, May 2, 2009 at 11:01 AM, Lucas Brito wrote: > Nicholas, > > To use the dblink: > > 1. In your postgres server you should find a file *dblink.sql*. > In my beta installation is in *share/postgresql/contrib*. It is the > installation for the dblink contrib module that usually is already compiled > in. It will create a lot of dblink functions. > > 2. on database2 create a function nammed db_datbase1() which returns > "dbname=database1" (if you need a login use "dbname=database1 password=xxx", > you can also specify host= port= to connect in a remote postgresql database) > > 3. now execute the sql: > select * from dblink(db_database1(), 'select "id", "name", "time" from > pr_1') as pr_1("id" integer, "name" text, "time" time) > then you will see the table "pr_1" on the datbase2 > > -- > Lucas Brito --001e680f10d42161f70468f26e1e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
i've not tried this in postgres, but using information_schema make= s comparing structures of databases trivial. i've been using this query= for a while with MSSQL. Maybe this helps to answer the question.
=A0
- isaac
= =A0
= select ST.T= able_Name, ST.Column_Name, DV.<= font size=3D"2">Table_Name, DV.Column_Name= , = *
from [database].<= font color=3D"#008000" size=3D"2">inform= ation_schema.Columns ST
full outer join [other database].info= rmation_schema.Columns DV
on ST.T= able_Name =3D DV.Table_name
and ST.C= olumn_Name =3D DV.Column_Name
where ST.= Column_Name is null or DV.Column_Name is NULL
= =A0
On Sat, May 2, 2009 at 11:01 AM, Lucas Brito <l= ucas75@gmail.com> wrote:
Nicholas,

To use the dbli= nk:
  1. In your postgres server you should find a file dblink.sql.
    I= n my beta installation is in share/postgresql/contrib. It is the ins= tallation for the dblink contrib module that usually is already compiled in= . It will create a lot of dblink functions.

  2. on database2 create a function nammed db_datbase1() which returns "= ;dbname=3Ddatabase1" (if you need a login use "dbname=3Ddatabase1= password=3Dxxx", you can also specify host=3D port=3D to connect in a= remote postgresql database)

  3. now execute the sql:
    select * fr= om dblink(db_database1(), 'select "id", "name", &qu= ot;time" from pr_1') as pr_1("id" integer, "name&qu= ot; text, "time" time)
    then you will see the table "pr_1" on the datbase2    =A0
--
Lucas Brito
=


--001e680f10d42161f70468f26e1e--