Re: Comparing two tables of different database
| От | Lucas Brito |
|---|---|
| Тема | Re: Comparing two tables of different database |
| Дата | |
| Msg-id | cccdaefb0905021425y597c31ecq2baae0748800076d@mail.gmail.com обсуждение исходный текст |
| Ответ на | Fwd: Comparing two tables of different database (Isaac Dover <isaacdover@gmail.com>) |
| Ответы |
Re: Comparing two tables of different database
|
| Список | pgsql-sql |
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.- isaacselect 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
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
В списке pgsql-sql по дате отправления: