Re: Comparing two tables of different database

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(at)gmail(dot)com> wrote:

> 2009/5/2 Isaac Dover <isaacdover(at)gmail(dot)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
>
>