Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 77EC16339F5 for ; Sat, 2 May 2009 18:25:48 -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 68414-09 for ; Sat, 2 May 2009 18:25:46 -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 6DBF563365D for ; Sat, 2 May 2009 18:25:46 -0300 (ADT) Received: by gxk11 with SMTP id 11so2179019gxk.19 for ; Sat, 02 May 2009 14:25:45 -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=GUje2xEbeNp7o+fgrsRlXDqU+KaprA+86yNdTTnPAp4=; b=OGW06WAiQ504d6xqnhGueBndOKhxGLTxOzR7DMMoVGBw5UnStzfYkTBn+lu+n+midg ErQ7rDZ4jDlGr6dorml81DotcwZGmvOziLA0OUDfB09JL0+2ctokOvpaePGK2g2SVAx4 aHW7IGOhhtu6/kTpmT7hx8OyvKcTx9c260axg= 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=k/sxb7iRTbzXMmcXsHGWT4zWk6yrFIqCyeHfgOPCPJbSh05dpYSId3WozO56ywfX/W gVmQ5kIxP+Cllux+Wr16dmmRJsTFez+e7BpC3YB/c4+bhMJ4DOcQFzg3SWZDEV8uJ9Xo cXTG3hWsAxtNU4Cd/8xZqJJkv5QbEG6auzgsg= MIME-Version: 1.0 Received: by 10.90.106.3 with SMTP id e3mr3630324agc.54.1241299545247; Sat, 02 May 2009 14:25:45 -0700 (PDT) In-Reply-To: References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> Date: Sat, 2 May 2009 18:25:45 -0300 Message-ID: Subject: Re: Comparing two tables of different database From: Lucas Brito To: Isaac Dover Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=00163616448d744fa00468f4927a 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/7 X-Sequence-Number: 32499 --00163616448d744fa00468f4927a Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 2009/5/2 Isaac Dover > 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 > > Isaac, this query will return "ERROR: cross-database references are not implemented". Postgres does not support queries in databases other than current one. Even a simple select * from otherdatabase.information_schema.columns will not work and throw the same error. However this can be done with dblink function like: select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, * from information_schema.Columns ST full outer join dblink('dbname=otherdatabase','select Table_Name, Column_Name from information_schema.Columns') DV(Table_Name text, Column_Name text) 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 -- Lucas Brito --00163616448d744fa00468f4927a Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
2009/5/2 Isaac Dover <isaacdover@gmail.com>
<= blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 2= 04, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
i've not tried this in postgr= es, but using information_schema makes comparing structures of databases tr= ivial. i've been using this query for a while with MSSQL. Maybe this he= lps to answer the question.
=A0
- isaac
=A0
select ST.Table_Name, ST<= font size=3D"2">.Column_Name, DV.Table_Name= , DV.Column_N= ame, *
from [database].= information_schema.Columns<= font size=3D"2"> ST
full outer join [other database].information_schema.Columns DV
on = ST.= Table_Name =3D DV.Table_name
and= ST.Column_Name =3D DV.Column_Name
where ST.Column_Name is null<= font size=3D"2"> or<= font size=3D"2"> DV.= Column_Name is NULL<= /font>
= =A0
Isaac, this query will return "ERROR:=A0 cross-database references are not implemented".

Postgres does not support queries in = databases other than current one. Even a simple select * from otherdatab= ase.information_schema.columns will not work and throw the sa= me error.

However this can be done with dblink function like:

<= font size=3D"2">select ST.Table_Name, ST<= font size=3D"2">.Column_Nam= e, = DV.= Table_Name, DV.Column_Name, *
from=A0information_schema.Colu= mns ST
full outer join dblink('dbname=3Dotherdatabase','select Tabl= e_Name, Column_Name from information_schema.Columns') DV(Table_Name = text, Column_Name text)
on = ST.= Table_Name =3D DV.Table_name
and= ST.Column_Name =3D DV.Column_Name
where ST.Column_Name is null<= font size=3D"2"> or<= font size=3D"2"> DV.= Column_Name is NULL<= /font>

--
Lucas Brito

--00163616448d744fa00468f4927a--