Fwd: Comparing two tables of different database - Mailing list pgsql-sql

From Isaac Dover
Subject Fwd: Comparing two tables of different database
Date
Msg-id b560e3300905021152t1682305eqc8239458a639d67b@mail.gmail.com
Whole thread Raw
In response to Comparing two tables of different database  (Nicholas I <nicholas.domnic.i@gmail.com>)
Responses Re: Comparing two tables of different database
List 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
 
On Sat, May 2, 2009 at 11:01 AM, Lucas Brito <lucas75@gmail.com> wrote:
Nicholas,

To use the dblink:
  1. 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.

  2. 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)

  3. 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 by date:

Previous
From: johnf
Date:
Subject: using a list to query
Next
From: Lucas Brito
Date:
Subject: Re: Comparing two tables of different database