Thread: How to compare two tables in PostgreSQL
Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
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?
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 <robjsargent@gmail.com> wrote:
Compare their content or their definition?On 11/10/2012 08:13 PM, saikiran mothe wrote:Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: 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, 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 <robjsargent@gmail.com> wrote:
Compare their content or their definition?On 11/10/2012 08:13 PM, saikiran mothe wrote:Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: 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, 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 <robjsargent@gmail.com> wrote:
Compare their content or their definition?On 11/10/2012 08:13 PM, saikiran mothe wrote:Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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
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 <willem_leenen@hotmail.com> 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: kamauallan@gmail.com
To: pgsql-sql@postgresql.orgIf 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 <robjsargent@gmail.com> wrote:Compare their content or their definition?On 11/10/2012 08:13 PM, saikiran mothe wrote:Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
From: saikiran mothe [mailto:saikiran.mothe@gmail.com] Sent: Saturday, November 10, 2012 10:14 PM To: pgsql-sql@postgresql.org Subject: How to compare two tables in PostgreSQL Hi, How can i compare two tables in PostgreSQL. Thanks, Sai Here is simple sql to show data in table1, but not in table2: SELECT <common_column_list> from table1 EXCEPT SELECT <common_column_list> from table2; And this sql shows data in table2 but not in table1: SELECT <common_column_list> from table2 EXCEPT SELECT <common_column_list> from table1; Or, you could combine them in one statement, adding "indicator" column: SELECT <common_column_list>, 'not in table2' as indicator from table1 EXCEPT SELECT <common_column_list>, 'not in table2' as indicator from table2 UNION SELECT <common_column_list>, 'not in table1' as indicator from table2 EXCEPT SELECT <common_column_list>, 'not in table1' as indicator from table1; Regards, Igor Neyman
NOTE: SIMILAR TABLES -- Ignore if it doesn't meet your requirement. If you like to compare the structure of the similar tables, then prefer below example which will help you in locating the missing/additional column. Consider we have these two tables on different schema. Table: 1 : billing_name columns: no, name, invid, amount Table 2: billing_name columns: no,name,invid *select * from (select column_name from information_schema.columns where table_schema = 'TEST' and table_name like 'billing_name') WHERE column_name NOT IN (select column_name from information_schema.columns where table_schema = 'public' and table_name like 'billing_name');* *Result Set: amount* Then, you can use any function/SP to dynamically pick the datatype and alter column on run time. Thank you for your time. Kamal -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-compare-two-tables-in-PostgreSQL-tp5731597p5819036.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.