Lists: | pgsql-sql |
---|
From: | saikiran mothe <saikiran(dot)mothe(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | How to compare two tables in PostgreSQL |
Date: | 2012-11-11 03:13:31 |
Message-ID: | CA+MnYCqoDRAQRzJQ2pi4jbZkyDdHtpszc4JH+hFT2TkaE86bxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
From: | Rob Sargentg <robjsargent(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 07:23:10 |
Message-ID: | 50A0A3DE.7070701@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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?
From: | Allan Kamau <kamauallan(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 08:00:32 |
Message-ID: | CAF3N6oTJ4TiM2krTk6vyrELH8k7E_-B_RAmaDOOwQ-RCvBZzTg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)gmail(dot)com>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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql>
>
From: | Willem Leenen <willem_leenen(at)hotmail(dot)com> |
---|---|
To: | <kamauallan(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 09:13:04 |
Message-ID: | DUB104-W340F2CF90F7096079209818F6D0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(at)gmail(dot)com
To: pgsql-sql(at)postgresql(dot)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(at)gmail(dot)com> 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?
From: | Willem Leenen <willem_leenen(at)hotmail(dot)com> |
---|---|
To: | <kamauallan(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 09:20:43 |
Message-ID: | DUB104-W16F2AF3E0E0FB49A6A6CE78F6D0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(at)gmail(dot)com
To: pgsql-sql(at)postgresql(dot)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(at)gmail(dot)com> 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?
From: | Devrim GÜNDÜZ <devrim(at)gunduz(dot)org> |
---|---|
To: | saikiran mothe <saikiran(dot)mothe(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 09:28:32 |
Message-ID: | 1352712512.1935.3.camel@lenovo01-laptop03.gunduz.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | Allan Kamau <kamauallan(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-12 10:44:22 |
Message-ID: | CAF3N6oScf=JLUNYSMWto4HEsngFtT7DJv6i6FWrdA-pCxWT0yg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)hotmail(dot)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(at)gmail(dot)com
> To: pgsql-sql(at)postgresql(dot)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(at)gmail(dot)com>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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql>
>
>
>
From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | saikiran mothe <saikiran(dot)mothe(at)gmail(dot)com>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2012-11-13 15:19:19 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC08EEEC02@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
From: saikiran mothe [mailto:saikiran(dot)mothe(at)gmail(dot)com]
Sent: Saturday, November 10, 2012 10:14 PM
To: pgsql-sql(at)postgresql(dot)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
From: | Kamal Kumar TRR <kamalkumar(dot)trr(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to compare two tables in PostgreSQL |
Date: | 2014-09-15 10:43:51 |
Message-ID: | 1410777831983-5819036.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.