Re: [SQL] How to compare two tables in PostgreSQL

[Allan Kamau]

If you would like to compare their contents perhaps this may help.
Write a select statement containing the fields for which you would like to
compare data for, you may want to leave out fields whose values are
provided by default for example fields populated from sequence object
and/or timestamp fields.
You may need to include triming of leading and trailing empty spaces for
the text based fields if such white spaces are not relevant for your
defination of similarity.
The same may apply on rounding and formatting numeric data for example
9.900 could be equivalent to 9.9 in the other table based on your
application of the data.
Include an ORDER BY clause to ensure you get the records in a predictable
order.
Output these data to a CSV file without the CSV header.
Now rewrite the same query for the other table, this is required if the
table definations are not common between the two tables.
Remember to substitute the table name accordingly.
Output these data to another CSV file without the CSV header.

Now run sha1sum on the first file and compare the returned sha1sum value
with the value returned on running sha1sum with the second file.
Perhaps use "diff" tool.

Allan.


On Mon, Nov 12, 2012 at 10:23 AM, Rob Sargentg wrote:

> On 11/10/2012 08:13 PM, saikiran mothe wrote:
>
>> Hi,
>>
>> How can i compare two tables in PostgreSQL.
>>
>> Thanks,
>> Sai
>>
> Compare their content or their definition?
>
>
> --
> 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] How to compare two tables in PostgreSQL

[Willem Leenen]

 
My advice: for comparing databases, tables , data etc, don't go scripting 
yourself. There are already tools in the market for that and they give nice 
reports on differences in constraints, indexes, columnnames, data etc. 
I used dbdiff from dkgas.com, but it seems the website is down.  

I would try to stick to SQL solutions as much as possible, instead of creating 
files and compare them. (got that from Joe Celko ;) )
 
 
 



Date: Mon, 12 Nov 2012 11:00:32 +0300
Subject: Re: [SQL] How to compare two tables in PostgreSQL
From: kamaual...@gmail.com
To: pgsql-sql@postgresql.org



If you would like to compare their contents perhaps this may help.
Write a select statement containing the fields for which you would like to 
compare data for, you may want to leave out fields whose values are provided by 
default for example fields populated from sequence object and/or timestamp 
fields.
You may need to include triming of leading and trailing empty spaces for the 
text based fields if such white spaces are not relevant for your defination of 
similarity.
The same may apply on rounding and formatting numeric data for example 9.900 
could be equivalent to 9.9 in the other table based on your application of the 
data.
Include an ORDER BY clause to ensure you get the records in a predictable order.
Output these data to a CSV file without the CSV header.
Now rewrite the same query for the other table, this is required if the table 
definations are not common between the two tables.
Remember to substitute the table name accordingly.
Output these data to another CSV file without the CSV header.


Now run sha1sum on the first file and compare the returned sha1sum value with 
the value returned on running sha1sum with the second file.
Perhaps use "diff" tool.


Allan.



On Mon, Nov 12, 2012 at 10:23 AM, Rob Sargentg  wrote:



On 11/10/2012 08:13 PM, saikiran mothe wrote:

Hi,

How can i compare two tables in PostgreSQL.

Thanks,
Sai
Compare their content or their definition?


-- 
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] How to compare two tables in PostgreSQL

[Willem Leenen]

 
According to Dr Google, this tool may suit your needs:
 
http://www.sqlmanager.net/en/products/postgresql/datacomparer?gclid=CImMsbmLybMCFQRc3godNgQAdQ
 
For business use only $133.
 
 



Date: Mon, 12 Nov 2012 11:00:32 +0300
Subject: Re: [SQL] How to compare two tables in PostgreSQL
From: kamaual...@gmail.com
To: pgsql-sql@postgresql.org



If you would like to compare their contents perhaps this may help.
Write a select statement containing the fields for which you would like to 
compare data for, you may want to leave out fields whose values are provided by 
default for example fields populated from sequence object and/or timestamp 
fields.
You may need to include triming of leading and trailing empty spaces for the 
text based fields if such white spaces are not relevant for your defination of 
similarity.
The same may apply on rounding and formatting numeric data for example 9.900 
could be equivalent to 9.9 in the other table based on your application of the 
data.
Include an ORDER BY clause to ensure you get the records in a predictable order.
Output these data to a CSV file without the CSV header.
Now rewrite the same query for the other table, this is required if the table 
definations are not common between the two tables.
Remember to substitute the table name accordingly.
Output these data to another CSV file without the CSV header.


Now run sha1sum on the first file and compare the returned sha1sum value with 
the value returned on running sha1sum with the second file.
Perhaps use "diff" tool.


Allan.



On Mon, Nov 12, 2012 at 10:23 AM, Rob Sargentg  wrote:



On 11/10/2012 08:13 PM, saikiran mothe wrote:

Hi,

How can i compare two tables in PostgreSQL.

Thanks,
Sai
Compare their content or their definition?


-- 
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] How to compare two tables in PostgreSQL

[Devrim GÜNDÜZ]

Hi,

On Sun, 2012-11-11 at 08:43 +0530, saikiran mothe wrote:
> How can i compare two tables in PostgreSQL.

http://pgfoundry.org/projects/pg-comparator/

Open source, under active development.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [SQL] How to compare two tables in PostgreSQL

[Allan Kamau]

Thanks Willem for the advise to stick to SQL solutions where possible.

A simple SQL solution would be to perform a full out join on the two tables.



On Mon, Nov 12, 2012 at 12:13 PM, Willem Leenen
wrote:

>
> My advice: for comparing databases, tables , data etc, don't go scripting
> yourself. There are already tools in the market for that and they give nice
> reports on differences in constraints, indexes, columnnames, data etc.
> I used dbdiff from dkgas.com, but it seems the website is down.
>
> I would try to stick to SQL solutions as much as possible, instead of
> creating files and compare them. (got that from Joe Celko ;) )
>
>
>
>  --
> Date: Mon, 12 Nov 2012 11:00:32 +0300
> Subject: Re: [SQL] How to compare two tables in PostgreSQL
> From: kamaual...@gmail.com
> To: pgsql-sql@postgresql.org
>
>
>  If you would like to compare their contents perhaps this may help.
> Write a select statement containing the fields for which you would like to
> compare data for, you may want to leave out fields whose values are
> provided by default for example fields populated from sequence object
> and/or timestamp fields.
> You may need to include triming of leading and trailing empty spaces for
> the text based fields if such white spaces are not relevant for your
> defination of similarity.
> The same may apply on rounding and formatting numeric data for example
> 9.900 could be equivalent to 9.9 in the other table based on your
> application of the data.
> Include an ORDER BY clause to ensure you get the records in a predictable
> order.
> Output these data to a CSV file without the CSV header.
> Now rewrite the same query for the other table, this is required if the
> table definations are not common between the two tables.
> Remember to substitute the table name accordingly.
> Output these data to another CSV file without the CSV header.
>
> Now run sha1sum on the first file and compare the returned sha1sum value
> with the value returned on running sha1sum with the second file.
> Perhaps use "diff" tool.
>
> Allan.
>
>
> On Mon, Nov 12, 2012 at 10:23 AM, Rob Sargentg wrote:
>
>  On 11/10/2012 08:13 PM, saikiran mothe wrote:
>
> Hi,
>
> How can i compare two tables in PostgreSQL.
>
> Thanks,
> Sai
>
> Compare their content or their definition?
>
>
> --
> 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] find sudstring on text (data type) field failure

[Achilleas Mantzios]

Gia sou Panteli,

are you suggesting that the results are incinsistent and unpredictable with 
every run?
pls present a case demontsrating the problem.

On Τετ 07 Νοε 2012 10:36:34 pantelis vlachos wrote:

I was trying to find a substring on a text (data type) column like 'cat foo dog 
...'. 
I use the query below
SELECT id FROM table WHERE name LIKE '% foo %';
Sometimes the query return with nTuples=0 but there are matching rows.
On retry, the query return with expected results. Any ideas;

(postgres ver 9.2, libpq - C Library)




-
Achilleas Mantzios
IT DEPT