Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TXq4q-00084m-8d for pgsql-sql@postgresql.org; Mon, 12 Nov 2012 09:13:08 +0000 Received: from dub0-omc4-s17.dub0.hotmail.com ([157.55.2.92]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TXq4o-0005c1-4E for pgsql-sql@postgresql.org; Mon, 12 Nov 2012 09:13:07 +0000 Received: from DUB104-W34 ([157.55.2.73]) by dub0-omc4-s17.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Mon, 12 Nov 2012 01:13:04 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_3835a491-f35f-4934-b9d3-1211f62dcafa_" X-Originating-IP: [145.77.106.6] From: Willem Leenen To: , Subject: Re: How to compare two tables in PostgreSQL Date: Mon, 12 Nov 2012 09:13:04 +0000 Importance: Normal In-Reply-To: References: , <50A0A3DE.7070701@gmail.com>, MIME-Version: 1.0 X-OriginalArrivalTime: 12 Nov 2012 09:13:04.0712 (UTC) FILETIME=[EC25B480:01CDC0B5] X-Pg-Spam-Score: -2.1 (--) X-Archive-Number: 201211/8 X-Sequence-Number: 36941 --_3835a491-f35f-4934-b9d3-1211f62dcafa_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 My advice: for comparing databases=2C tables =2C data etc=2C don't go scrip= ting yourself. There are already tools in the market for that and they give= nice reports on differences in constraints=2C indexes=2C columnnames=2C da= ta etc.=20 I used dbdiff from dkgas.com=2C but it seems the website is down. =20 I would try to stick to SQL solutions as much as possible=2C instead of cre= ating files and compare them. (got that from Joe Celko =3B) ) =20 =20 =20 Date: Mon=2C 12 Nov 2012 11:00:32 +0300 Subject: Re: [SQL] How to compare two tables in PostgreSQL From: kamauallan@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=2C you may want to leave out fields whose values are provi= ded by default for example fields populated from sequence object and/or tim= estamp fields. You may need to include triming of leading and trailing empty spaces for th= e text based fields if such white spaces are not relevant for your definati= on of similarity. The same may apply on rounding and formatting numeric data for example 9.90= 0 could be equivalent to 9.9 in the other table based on your application o= f the data. Include an ORDER BY clause to ensure you get the records in a predictable o= rder. Output these data to a CSV file without the CSV header. Now rewrite the same query for the other table=2C this is required if the t= able 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 wi= th the value returned on running sha1sum with the second file. Perhaps use "diff" tool. Allan. On Mon=2C Nov 12=2C 2012 at 10:23 AM=2C Rob Sargentg wrote: On 11/10/2012 08:13 PM=2C saikiran mothe wrote: Hi=2C How can i compare two tables in PostgreSQL. Thanks=2C Sai Compare their content or their definition? --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql = --_3835a491-f35f-4934-b9d3-1211f62dcafa_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 =3B
My advice: for comparing databases=2C tables =2C data etc=2C don't go scrip= ting yourself. There are already tools in the market for that and they give= nice reports on differences in constraints=2C indexes=2C columnnames=2C da= ta etc.
I used dbdiff from dkgas.com=2C but it seems the website is down. =3B <= BR>
I would try to =3Bstick to SQL solutions as much as possible=2C ins= tead of creating files and compare them. (got that from Joe Celko =3B) )  =3B
 =3B
 =3B
Date: Mon=2C 12 Nov 2012 11:00:32 +0300
Subject: Re: [SQL] How to compar= e two tables in PostgreSQL
From: kamauallan@gmail.com
To: pgsql-sql@p= ostgresql.org

If you would like to compare their contents perhaps this may help.
Write a select statement containing the fields for which you would lik= e to compare data for=2C you may want to leave out fields whose values are = provided by default for example fields populated from sequence object and/o= r timestamp fields.
You may need to include triming of leading and trailing empty spaces f= or the text based fields if such white spaces are not relevant for your def= ination 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 applicat= ion of the data.
Include an ORDER BY clause to ensure you get the records in a predicta= ble order.
Output these data to a CSV file without the CSV header.
Now rewrite the same query for the other table=2C 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 val= ue with the value returned on running sha1sum with the second file.
Perhaps use "diff" tool.

Allan.


On Mon=2C Nov 12=2C 2012 at 10:23 AM=2C Rob Sar= gentg <=3Brobjsa= rgent@gmail.com>=3B wrote:
On 11/10/2012 08:13 PM=2C saikiran mothe wrote:
Hi=2C

How can i compare two tables in PostgreSQL.=

Thanks=2C
Sai
Compare their content = or their definition?

<= BR>--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscr= iption:
http://www.postgresql.org/mailpref/pgsql-sql

= --_3835a491-f35f-4934-b9d3-1211f62dcafa_--