Re: [SQL] Comparing two tables of different database
On 2009-04-29, Nicholas I 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 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 () TO , where 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 : > > 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 () TO , where 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 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 () TO , where 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 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
[SQL] freeing memory after fetch all
How should I free memory after I allow FETCH ALL statements in a C
program to auto-allocate memory? I assume that it's OK to use a simple
free statement to free memory allocated for an array of values (e.g., an
array of float values). But what about memory allocated for and
associated with an array of pointers (e.g., an array of pointers to
character strings).
For example:
long number_fetched ;
long record ;
exec sql begin declare section ;
char **char_column ;
float *float_column ;
exec sql end declare section ;
$declare fetch_cursor cursor for
select char_column, float_column from my_table ;
$open fetch_cursor ;
$fetch all fetch_cursor into $char_column, $float_column ;
$close fetch_cursor ;
$free fetch_cursor ;
number_fetched = sqlca.sqlerrd[2] ;
for ( record = 0 ; record < number_fetched ; record++ )
{
// Do something with the fetched data ...
printf ( "character string : %s value : %f\n",
char_column[record], float_column[record] ) ;
}
if ( char_column != NULL ) free (char_column ) ;
if ( float_column != NULL ) free ( float_column ) ;
In this example should I free the memory allocated for each of the
pointers in the char_column array before freeing char_column? :
for ( record = 0 ; record < number_fetched ; record++ )
{
free ( char_column[record] ) ;
}
Thanks.
Chris
--
Chris Bovitz
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Drive West, Chanhassen, MN 55317-8582 USA
Phone: +1 952 368-2507
: +1 952 361-6610 x 2507
Fax:+1 952 361-6634
E-mail: christopher (dot) bovitz (at) noaa (dot) gov
Web:http://www.nohrsc.noaa.gov
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
