Fwd: Comparing two tables of different database
| От | Isaac Dover |
|---|---|
| Тема | Fwd: Comparing two tables of different database |
| Дата | |
| Msg-id | b560e3300905021152t1682305eqc8239458a639d67b@mail.gmail.com обсуждение исходный текст |
| Ответ на | Comparing two tables of different database (Nicholas I <nicholas.domnic.i@gmail.com>) |
| Ответы |
Re: Comparing two tables of different database
|
| Список | pgsql-sql |
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
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
On Sat, May 2, 2009 at 11:01 AM, Lucas Brito <lucas75@gmail.com> wrote:
Nicholas,
To use the dblink:--
- In your postgres server you should find a file dblink.sql.
In my beta installation is in share/postgresql/contrib. It is the installation for the dblink contrib module that usually is already compiled in. It will create a lot of dblink functions.- on database2 create a function nammed db_datbase1() which returns "dbname=database1" (if you need a login use "dbname=database1 password=xxx", you can also specify host= port= to connect in a remote postgresql database)
- now execute the sql:
select * from dblink(db_database1(), 'select "id", "name", "time" from pr_1') as pr_1("id" integer, "name" text, "time" time)
then you will see the table "pr_1" on the datbase2
Lucas Brito
В списке pgsql-sql по дате отправления: