From: Lucas Brito <lucas75@gmail.com>
To: Isaac Dover <isaacdover@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Comparing two tables of different database
Date: Sat, 2 May 2009 18:25:45 -0300
Message-ID: <cccdaefb0905021425y597c31ecq2baae0748800076d@mail.gmail.com> (raw)
In-Reply-To: <b560e3300905021152t1682305eqc8239458a639d67b@mail.gmail.com>
References: <e13d0d10904282339r1f853e1cpe3514e53ae47e5ef@mail.gmail.com>
<cc4f12900904290533x5e1871fdu8f8a27dc956ca616@mail.gmail.com>
<328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com>
<20090430033747.GB1126@eddie>
<e13d0d10904300312j5ec7baa1l3ae91724f6f5dbf8@mail.gmail.com>
<cccdaefb0905020801i68426f57ycd892021bf27bd8b@mail.gmail.com>
<b560e3300905021150r120d054fjc1ab9a18082120a3@mail.gmail.com>
<b560e3300905021152t1682305eqc8239458a639d67b@mail.gmail.com>
2009/5/2 Isaac Dover <isaacdover@gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lucas75@gmail.com, isaacdover@gmail.com
Subject: Re: Comparing two tables of different database
In-Reply-To: <cccdaefb0905021425y597c31ecq2baae0748800076d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox