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 <luca...@gmail.com> wrote:
> 2009/5/2 Isaac Dover <isaacdo...@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
>
>
