From: | Lucas Brito <lucas75(at)gmail(dot)com> |
---|---|
To: | Isaac Dover <isaacdover(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Comparing two tables of different database |
Date: | 2009-05-02 21:25:45 |
Message-ID: | cccdaefb0905021425y597c31ecq2baae0748800076d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2009-05-03 01:34:57 | Re: using a list to query |
Previous Message | Isaac Dover | 2009-05-02 18:52:25 | Fwd: Comparing two tables of different database |