Thread: Comparing two tables of different database
Hi, <br /><br /> can anybody me suggest me, how to compare two tables of different database.<br /><br />-Nicholas I<br />
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
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
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 >
2009/4/29 Nicholas I <nicholas.domnic.i@gmail.com>:Hi,can anybody me suggest me, how to compare two tables of differentdatabase.-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
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.
Hi All, <br /><br /> For example, <br /><br /> There are two database. database1 and database 2;<br /> <br /> database1 has a table called pr_1 with the columns, id,name and time.<br /> database2 has a table called sr_1 withthe_columns id,name and time.<br /> <br /> i would like to find out the differences that is, find the names thatare not in sr_1 but in pr_1.<br /> we can achieve this by the query, <br /> <br /> select name from sr_1where name not in (select name from pr_1);<br /> the above query will work in case of two tables in the same database.<br/><br /><br /> But the problem is, these two tables are in different database. i did not understand aboutthe dblink.<br /><br /> is there any exaples on dblink. can we do it without using dblink.<br /><br />-Nicholas I<br/> <br /> <br /><br /><div class="gmail_quote">On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <span dir="ltr"><<ahref="mailto:eggyknap@gmail.com">eggyknap@gmail.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><divclass="im">On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:<br /> > The simple answer is to pg_dumpboth tables and compare the output with<br /> > diff.<br /> > Other than that, I think you'll need a customprogram.<br /><br /></div>For all but the strictest definition of "identical", that won't work.<br /> Tables may easilycontain the same information, in different on-disk<br /> order, and pg_dump will most likely give the data to you inan order<br /> similar to its ordering on disk.<br /><br /> Something like a COPY (<query>) TO <file>, where<query> includes an<br /> ORDER BY clause, might give you a suitable result from both tables, on<br /> which youcould then take a checksum.<br /><br /> - Josh / eggyknap<br /><br />-----BEGIN PGP SIGNATURE-----<br /> Version: GnuPGv1.4.9 (GNU/Linux)<br /><br /> iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC<br /> rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh<br/> =LO6r<br /> -----END PGP SIGNATURE-----<br /><br /></blockquote></div><br />
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
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
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-----
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.
Hi Nicholas,
The query is across database query. dblink is needed for that task.
Hope it helps,
John
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 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
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
Nicholas,<br /><br />To use the dblink:<br /><ol><li>In your postgres server you should find a file <b>dblink.sql</b>.
<br/> In my beta installation is in <b>share/postgresql/contrib</b>. It is the installation for the dblink contrib
modulethat usually is already compiled in. It will create a lot of dblink functions.<br /><br /><li>on database2 create
afunction 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)<br /><br /><li>now execute
thesql:<br /><span style="color: rgb(0, 0, 153);">select * from dblink(db_database1(), 'select "id", "name", "time"
frompr_1') as pr_1("id" integer, "name" text, "time" time)<font color="#000000"><br /> then you will see the table
"pr_1"on the datbase2</font> </span></ol>-- <br />Lucas Brito
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
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
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.- isaacselect 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".
However this can be done with dblink function like:
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
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.- isaacselect 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