pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComparing two tables of different database
18+ messages / 14 participants
[nested] [flat]
* Comparing two tables of different database
@ 2009-04-29 06:39 Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:15 ` Re: Comparing two tables of different database Pavel Stehule <pavel.stehule@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 12:48 ` Re: Comparing two tables of different database Osvaldo Kussama <osvaldo.kussama@gmail.com>
2009-04-29 13:12 ` Re: Comparing two tables of different database Thomas Kellerer <spam_eater@gmx.net>
2009-04-30 12:05 ` Re: Comparing two tables of different database Jasen Betts <jasen@xnet.co.nz>
2009-04-30 12:08 ` Re: Comparing two tables of different database Jasen Betts <jasen@xnet.co.nz>
2009-05-02 10:16 ` Re: Comparing two tables of different database M.P.Dankoor <m.p.dankoor@gmail.com>
0 siblings, 7 replies; 18+ messages in thread
From: Nicholas I @ 2009-04-29 06:39 UTC (permalink / raw)
To: pgsql-sql
Hi,
can anybody me suggest me, how to compare two tables of different
database.
-Nicholas I
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-29 12:15 ` Pavel Stehule <pavel.stehule@gmail.com>
6 siblings, 0 replies; 18+ messages in thread
From: Pavel Stehule @ 2009-04-29 12:15 UTC (permalink / raw)
To: Nicholas I <nicholas.domnic.i@gmail.com>; +Cc: pgsql-sql
Hello
try to look on http://pgfoundry.org/forum/forum.php?forum_id=1392
regards
Pavel Stehule
2009/4/29 Nicholas I <nicholas.domnic.i@gmail.com>:
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
>
> -Nicholas I
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-29 12:33 ` Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
6 siblings, 1 reply; 18+ messages in thread
From: Pawel Socha @ 2009-04-29 12:33 UTC (permalink / raw)
To: Nicholas I <nicholas.domnic.i@gmail.com>; +Cc: pgsql-sql
2009/4/29 Nicholas I <nicholas.domnic.i@gmail.com>:
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
>
> -Nicholas I
>
what you mean 'different database' :)
diffrent version, 2 instance ?
Maybe this help:
http://www.postgresql.org/docs/current/static/dblink.html
--
Pawel Socha
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
@ 2009-04-29 22:20 ` Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Adam Ruth @ 2009-04-29 22:20 UTC (permalink / raw)
To: Pawel Socha <pawel.socha@gmail.com>; +Cc: Nicholas I <nicholas.domnic.i@gmail.com>; pgsql-sql
The simple answer is to pg_dump both tables and compare the output
with diff.
Other than that, I think you'll need a custom program.
On 29/04/2009, at 10:33 PM, Pawel Socha wrote:
> 2009/4/29 Nicholas I <nicholas.domnic.i@gmail.com>:
>> Hi,
>>
>> can anybody me suggest me, how to compare two tables of different
>> database.
>>
>> -Nicholas I
>>
>
> what you mean 'different database' :)
> diffrent version, 2 instance ?
>
> Maybe this help:
> http://www.postgresql.org/docs/current/static/dblink.html
>
>
> --
> Pawel Socha
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
@ 2009-04-30 03:37 ` Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Joshua Tolley @ 2009-04-30 03:37 UTC (permalink / raw)
To: Adam Ruth <adamruth@mac.com>; +Cc: Pawel Socha <pawel.socha@gmail.com>; Nicholas I <nicholas.domnic.i@gmail.com>; pgsql-sql
On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:
> The simple answer is to pg_dump both tables and compare the output with
> diff.
> Other than that, I think you'll need a custom program.
For all but the strictest definition of "identical", that won't work.
Tables may easily contain the same information, in different on-disk
order, and pg_dump will most likely give the data to you in an order
similar to its ordering on disk.
Something like a COPY (<query>) TO <file>, where <query> includes an
ORDER BY clause, might give you a suitable result from both tables, on
which you could then take a checksum.
- Josh / eggyknap
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
@ 2009-04-30 10:12 ` Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-30 22:07 ` Re: Comparing two tables of different database Edward W. Rouse <erouse@comsquared.com>
2009-05-02 15:01 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
0 siblings, 2 replies; 18+ messages in thread
From: Nicholas I @ 2009-04-30 10:12 UTC (permalink / raw)
To: Joshua Tolley <eggyknap@gmail.com>; +Cc: Adam Ruth <adamruth@mac.com>; Pawel Socha <pawel.socha@gmail.com>; pgsql-sql
Hi All,
For example,
There are two database. database1 and database 2;
database1 has a table called pr_1 with the columns, id,name and time.
database2 has a table called sr_1 with the_columns id,name and time.
i would like to find out the differences that is, find the names that
are not in sr_1 but in pr_1.
we can achieve this by the query,
select name from sr_1 where name not in (select name from pr_1);
the above query will work in case of two tables in the same database.
But the problem is, these two tables are in different database. i did
not understand about the dblink.
is there any exaples on dblink. can we do it without using dblink.
-Nicholas I
On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <eggyknap@gmail.com> wrote:
> On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:
> > The simple answer is to pg_dump both tables and compare the output
> with
> > diff.
> > Other than that, I think you'll need a custom program.
>
> For all but the strictest definition of "identical", that won't work.
> Tables may easily contain the same information, in different on-disk
> order, and pg_dump will most likely give the data to you in an order
> similar to its ordering on disk.
>
> Something like a COPY (<query>) TO <file>, where <query> includes an
> ORDER BY clause, might give you a suitable result from both tables, on
> which you could then take a checksum.
>
> - Josh / eggyknap
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC
> rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh
> =LO6r
> -----END PGP SIGNATURE-----
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-30 22:07 ` Edward W. Rouse <erouse@comsquared.com>
2009-05-01 15:55 ` Re: Comparing two tables of different database John Zhang <johnzhang06@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Edward W. Rouse @ 2009-04-30 22:07 UTC (permalink / raw)
To: pgsql-sql
Can't you use this?
select name from database2.sr_1 where name not in (select name from
database2.pr_1);
My test database VM isn't running so I can't test it, but I seem to remember
that that's how I did it for a few queries of that type. This is assuming
the 2 databases are running on the same machine, like the way there is
template0 as the default and you add addition databases to the same
'instance'. If you are talking about 2 different database servers, then I
have no idea.
Edward W. Rouse
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Nicholas I
Sent: Thursday, April 30, 2009 6:12 AM
To: Joshua Tolley
Cc: Adam Ruth; Pawel Socha; pgsql-sql@postgresql.org
Subject: Re: [SQL] Comparing two tables of different database
Hi All,
For example,
There are two database. database1 and database 2;
database1 has a table called pr_1 with the columns, id,name and time.
database2 has a table called sr_1 with the_columns id,name and time.
i would like to find out the differences that is, find the names that
are not in sr_1 but in pr_1.
we can achieve this by the query,
select name from sr_1 where name not in (select name from pr_1);
the above query will work in case of two tables in the same database.
But the problem is, these two tables are in different database. i did
not understand about the dblink.
is there any exaples on dblink. can we do it without using dblink.
-Nicholas I
On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <eggyknap@gmail.com> wrote:
On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:
> The simple answer is to pg_dump both tables and compare the output with
> diff.
> Other than that, I think you'll need a custom program.
For all but the strictest definition of "identical", that won't work.
Tables may easily contain the same information, in different on-disk
order, and pg_dump will most likely give the data to you in an order
similar to its ordering on disk.
Something like a COPY (<query>) TO <file>, where <query> includes an
ORDER BY clause, might give you a suitable result from both tables, on
which you could then take a checksum.
- Josh / eggyknap
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC
rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh
=LO6r
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-30 22:07 ` Re: Comparing two tables of different database Edward W. Rouse <erouse@comsquared.com>
@ 2009-05-01 15:55 ` John Zhang <johnzhang06@gmail.com>
2009-05-01 16:31 ` Re: Comparing two tables of different database Wei Weng <wweng@kencast.com>
0 siblings, 1 reply; 18+ messages in thread
From: John Zhang @ 2009-05-01 15:55 UTC (permalink / raw)
To: pgsql-sql
Hi Nicholas,
The query is across database query. dblink is needed for that task.
Hope it helps,
John
On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse <erouse@comsquared.com>wrote:
> Can’t you use this?
>
>
>
> select name from database2.sr_1 where name not in (select name from
> database2.pr_1);
>
>
>
> My test database VM isn’t running so I can’t test it, but I seem to
> remember that that’s how I did it for a few queries of that type. This is
> assuming the 2 databases are running on the same machine, like the way there
> is template0 as the default and you add addition databases to the same
> ‘instance’. If you are talking about 2 different database servers, then I
> have no idea.
>
>
>
> Edward W. Rouse
>
>
>
> *From:* pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org] *On Behalf Of *Nicholas I
> *Sent:* Thursday, April 30, 2009 6:12 AM
> *To:* Joshua Tolley
> *Cc:* Adam Ruth; Pawel Socha; pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] Comparing two tables of different database
>
>
>
> Hi All,
>
> For example,
>
> There are two database. database1 and database 2;
>
> database1 has a table called pr_1 with the columns, id,name and time.
> database2 has a table called sr_1 with the_columns id,name and time.
>
> i would like to find out the differences that is, find the names that
> are not in sr_1 but in pr_1.
> we can achieve this by the query,
>
> select name from sr_1 where name not in (select name from pr_1);
> the above query will work in case of two tables in the same database.
>
>
> But the problem is, these two tables are in different database. i did
> not understand about the dblink.
>
> is there any exaples on dblink. can we do it without using dblink.
>
> -Nicholas I
>
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-30 22:07 ` Re: Comparing two tables of different database Edward W. Rouse <erouse@comsquared.com>
2009-05-01 15:55 ` Re: Comparing two tables of different database John Zhang <johnzhang06@gmail.com>
@ 2009-05-01 16:31 ` Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 18+ messages in thread
From: Wei Weng @ 2009-05-01 16:31 UTC (permalink / raw)
To: pgsql-sql
On 05/01/2009 11:55 AM, John Zhang wrote:
>
> Hi Nicholas,
>
> The query is across database query. dblink is needed for that task.
>
> Hope it helps,
>
> John
>
> On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse
> <erouse@comsquared.com <mailto:erouse@comsquared.com>> wrote:
>
> Can’t you use this?
>
> select name from database2.sr_1 where name not in (select name
> from database2.pr_1);
>
> My test database VM isn’t running so I can’t test it, but I seem
> to remember that that’s how I did it for a few queries of that
> type. This is assuming the 2 databases are running on the same
> machine, like the way there is template0 as the default and you
> add addition databases to the same ‘instance’. If you are talking
> about 2 different database servers, then I have no idea.
>
> Edward W. Rouse
>
How do you formulate the query using dblink?
Thanks
Wei
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-05-02 15:01 ` Lucas Brito <lucas75@gmail.com>
2009-05-02 18:52 ` Fwd: Comparing two tables of different database Isaac Dover <isaacdover@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Lucas Brito @ 2009-05-02 15:01 UTC (permalink / raw)
To: Nicholas I <nicholas.domnic.i@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Fwd: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-05-02 15:01 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
@ 2009-05-02 18:52 ` Isaac Dover <isaacdover@gmail.com>
2009-05-02 21:25 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Isaac Dover @ 2009-05-02 18:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-05-02 15:01 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
2009-05-02 18:52 ` Fwd: Comparing two tables of different database Isaac Dover <isaacdover@gmail.com>
@ 2009-05-02 21:25 ` Lucas Brito <lucas75@gmail.com>
2009-05-03 03:47 ` Re: Comparing two tables of different database Isaac Dover <isaacdover@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Lucas Brito @ 2009-05-02 21:25 UTC (permalink / raw)
To: Isaac Dover <isaacdover@gmail.com>; +Cc: 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.
>
> - 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
>
>
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
--
Lucas Brito
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:33 ` Re: Comparing two tables of different database Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Re: Comparing two tables of different database Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Re: Comparing two tables of different database Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Re: Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-05-02 15:01 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
2009-05-02 18:52 ` Fwd: Comparing two tables of different database Isaac Dover <isaacdover@gmail.com>
2009-05-02 21:25 ` Re: Comparing two tables of different database Lucas Brito <lucas75@gmail.com>
@ 2009-05-03 03:47 ` Isaac Dover <isaacdover@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Isaac Dover @ 2009-05-03 03:47 UTC (permalink / raw)
To: pgsql-sql
Thank you, Lucas. I'm from the MS world, still learning these PG things.
Though, it appears that the difference is somewhat minor. In my actual
implementation, [other database] would be a linked server, which sounds like
it would be similar to the PG dblink. Regardless, I've found information
schema to be incredibly valuable.
Thanks,
- Isaac
On Sat, May 2, 2009 at 5:25 PM, Lucas Brito <lucas75@gmail.com> wrote:
> 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.
>>
>> - 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
>>
>>
>
> 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
>
> --
> Lucas Brito
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-29 12:48 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
6 siblings, 0 replies; 18+ messages in thread
From: Osvaldo Kussama @ 2009-04-29 12:48 UTC (permalink / raw)
To: Nicholas I <nicholas.domnic.i@gmail.com>; +Cc: pgsql-sql
2009/4/29 Nicholas I <nicholas.domnic.i@gmail.com>:
>
> can anybody me suggest me, how to compare two tables of different
> database.
>
Two PostgreSQL databases: dblink
http://www.postgresql.org/docs/current/interactive/dblink.html
Distinct DBMS: dbilink
http://pgfoundry.org/projects/dbi-link/
Osvaldo
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-29 13:12 ` Thomas Kellerer <spam_eater@gmx.net>
6 siblings, 0 replies; 18+ messages in thread
From: Thomas Kellerer @ 2009-04-29 13:12 UTC (permalink / raw)
To: pgsql-sql
Nicholas I, 29.04.2009 08:39:
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
Do you want to compare the data or the structure of the two tables?
Thomas
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-30 12:05 ` Jasen Betts <jasen@xnet.co.nz>
6 siblings, 0 replies; 18+ messages in thread
From: Jasen Betts @ 2009-04-30 12:05 UTC (permalink / raw)
To: pgsql-sql
On 2009-04-29, Nicholas I <nicholas.domnic.i@gmail.com> wrote:
> --000e0cd1d5062f2ca40468abd813
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: 7bit
>
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
you probably want to use some sort of join.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-04-30 12:08 ` Jasen Betts <jasen@xnet.co.nz>
6 siblings, 0 replies; 18+ messages in thread
From: Jasen Betts @ 2009-04-30 12:08 UTC (permalink / raw)
To: pgsql-sql
On 2009-04-29, Nicholas I <nicholas.domnic.i@gmail.com> wrote:
> --000e0cd1d5062f2ca40468abd813
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: 7bit
>
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
you want to use a join but can't...
you need to get both tables into the same database to be able to do a
join on them.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Comparing two tables of different database
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
@ 2009-05-02 10:16 ` M.P.Dankoor <m.p.dankoor@gmail.com>
6 siblings, 0 replies; 18+ messages in thread
From: M.P.Dankoor @ 2009-05-02 10:16 UTC (permalink / raw)
To: pgsql-sql
In case dblink was not installed, you could try the following:
1. dump only the data from the table from database 1
pg_dump -U username -a -d -t tablename dbname > tablename.sql
2. create a (temp) table in database 2
SELECT * INTO tablename_bak from tablename WHERE 1 = 2
3. restore the dumped data in the bak table in database 2
pg_restore -U username -a -t tablename_bak dbname tablename.sql
4.select * from tablename except select * from tablename_bak
or you could dump the data from both tables and use some kind of diff tool
Nicholas I wrote:
> Hi,
>
> can anybody me suggest me, how to compare two tables of different
> database.
>
> -Nicholas I
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2009-05-03 03:47 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-29 06:39 Comparing two tables of different database Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-29 12:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-04-29 12:33 ` Pawel Socha <pawel.socha@gmail.com>
2009-04-29 22:20 ` Adam Ruth <adamruth@mac.com>
2009-04-30 03:37 ` Joshua Tolley <eggyknap@gmail.com>
2009-04-30 10:12 ` Nicholas I <nicholas.domnic.i@gmail.com>
2009-04-30 22:07 ` Edward W. Rouse <erouse@comsquared.com>
2009-05-01 15:55 ` John Zhang <johnzhang06@gmail.com>
2009-05-01 16:31 ` Wei Weng <wweng@kencast.com>
2009-05-02 15:01 ` Lucas Brito <lucas75@gmail.com>
2009-05-02 18:52 ` Isaac Dover <isaacdover@gmail.com>
2009-05-02 21:25 ` Lucas Brito <lucas75@gmail.com>
2009-05-03 03:47 ` Isaac Dover <isaacdover@gmail.com>
2009-04-29 12:48 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2009-04-29 13:12 ` Thomas Kellerer <spam_eater@gmx.net>
2009-04-30 12:05 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-30 12:08 ` Jasen Betts <jasen@xnet.co.nz>
2009-05-02 10:16 ` M.P.Dankoor <m.p.dankoor@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox