From: Isaac Dover <isaacdover@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Comparing two tables of different database
Date: Sat, 2 May 2009 23:47:50 -0400
Message-ID: <b560e3300905022047v5cc302b5ncb9de1bfafd8954c@mail.gmail.com> (raw)
In-Reply-To: <cccdaefb0905021425y597c31ecq2baae0748800076d@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>
<cccdaefb0905021425y597c31ecq2baae0748800076d@mail.gmail.com>
Thank you, Lucas. I'm from the MS world, still learning these PG things.
Though, it appears that the difference is somewhat minor. In my actual
implementation, [other database] would be a linked server, which sounds like
it would be similar to the PG dblink. Regardless, I've found information
schema to be incredibly valuable.
Thanks,
- Isaac
On Sat, May 2, 2009 at 5:25 PM, Lucas Brito <lucas75@gmail.com> wrote:
> 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: isaacdover@gmail.com
Subject: Re: Comparing two tables of different database
In-Reply-To: <b560e3300905022047v5cc302b5ncb9de1bfafd8954c@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