Re: [SQL] Comparing two tables of different database
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
Re: [SQL] Comparing two tables of different database
2009/5/2 Isaac Dover isaacdo...@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
Re: [SQL] Comparing two tables of different database
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 luca...@gmail.com wrote:
2009/5/2 Isaac Dover isaacdo...@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
Re: [SQL] Comparing two tables of different database
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 ero...@comsquared.comwrote: 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-ow...@postgresql.org [mailto: pgsql-sql-ow...@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
Re: [SQL] Comparing two tables of different database
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 ero...@comsquared.com mailto:ero...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
On 2009-04-29, Nicholas I nicholas.domni...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 eggyk...@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-
Re: [SQL] Comparing two tables of different database
2009/4/29 Nicholas I nicholas.domni...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
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 Description: Digital signature
Re: [SQL] Comparing two tables of different database
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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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 eggyk...@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-
Re: [SQL] Comparing two tables of different database
On 2009-04-29, Nicholas I nicholas.domni...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
2009/4/29 Nicholas I nicholas.domni...@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
Re: [SQL] Comparing two tables of different database
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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
Hello try to look on http://pgfoundry.org/forum/forum.php?forum_id=1392 regards Pavel Stehule 2009/4/29 Nicholas I nicholas.domni...@gmail.com: Hi, can anybody me suggest me, how to compare two tables of different database. -Nicholas I -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
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.domni...@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
