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
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