Quantcast

Comparing two tables of different database

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Comparing two tables of different database

Nicholas I
Hi,

  can anybody me suggest me, how to compare two tables of different database.

-Nicholas I
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Pavel Stehule
Hello

try to look on http://pgfoundry.org/forum/forum.php?forum_id=1392

regards
Pavel Stehule

2009/4/29 Nicholas I <[hidden email]>:
> Hi,
>
>   can anybody me suggest me, how to compare two tables of different
> database.
>
> -Nicholas I
>

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

paul socha
In reply to this post by Nicholas I
2009/4/29 Nicholas I <[hidden email]>:
> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Osvaldo Kussama
In reply to this post by Nicholas I
2009/4/29 Nicholas I <[hidden email]>:
>
>   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

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Thomas Kellerer
In reply to this post by Nicholas I
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


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Adam Ruth
In reply to this post by paul socha
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 <[hidden email]>:
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

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

signature.asc (204 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Nicholas I
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 <[hidden email]> 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-----


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Jasen Betts-5
In reply to this post by Nicholas I
On 2009-04-29, Nicholas I <[hidden email]> 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.


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Jasen Betts-5
In reply to this post by Nicholas I
On 2009-04-29, Nicholas I <[hidden email]> 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.


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Edward W. Rouse
In reply to this post by Nicholas I

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: [hidden email] [mailto:[hidden email]] On Behalf Of Nicholas I
Sent: Thursday, April 30, 2009 6:12 AM
To: Joshua Tolley
Cc: Adam Ruth; Pawel Socha; [hidden email]
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 <[hidden email]> 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-----

 

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Blue Sky

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 <[hidden email]> 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: [hidden email] [mailto:[hidden email]] On Behalf Of Nicholas I
Sent: Thursday, April 30, 2009 6:12 AM
To: Joshua Tolley
Cc: Adam Ruth; Pawel Socha; [hidden email]
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
   
     



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Wei Weng


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
> <[hidden email] <mailto:[hidden email]>> 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


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Mario Dankoor
In reply to this post by Nicholas I
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


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Lucas Brito
In reply to this post by Nicholas I
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Fwd: Comparing two tables of different database

Isaac Dover
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Lucas Brito
2009/5/2 Isaac Dover <[hidden email]>
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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing two tables of different database

Isaac Dover
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 <[hidden email]> wrote:
2009/5/2 Isaac Dover <[hidden email]>

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


Loading...