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