agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to compare two tables in PostgreSQL
9+ messages / 7 participants
[nested] [flat]
* How to compare two tables in PostgreSQL
@ 2012-11-11 03:13 saikiran mothe <saikiran.mothe@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: saikiran mothe @ 2012-11-11 03:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
How can i compare two tables in PostgreSQL.
Thanks,
Sai
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 07:23 Rob Sargentg <robjsargent@gmail.com>
parent: saikiran mothe <saikiran.mothe@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Rob Sargentg @ 2012-11-12 07:23 UTC (permalink / raw)
To: 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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 08:00 Allan Kamau <kamauallan@gmail.com>
parent: Rob Sargentg <robjsargent@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Allan Kamau @ 2012-11-12 08:00 UTC (permalink / raw)
To: 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@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql;
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 09:13 Willem Leenen <willem_leenen@hotmail.com>
parent: Allan Kamau <kamauallan@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Willem Leenen @ 2012-11-12 09:13 UTC (permalink / raw)
To: kamauallan@gmail.com; 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:
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
=
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 09:20 Willem Leenen <willem_leenen@hotmail.com>
parent: Allan Kamau <kamauallan@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Willem Leenen @ 2012-11-12 09:20 UTC (permalink / raw)
To: kamauallan@gmail.com; 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:
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
=
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 09:28 Devrim GÜNDÜZ <devrim@gunduz.org>
parent: saikiran mothe <saikiran.mothe@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Devrim GÜNDÜZ @ 2012-11-12 09:28 UTC (permalink / raw)
To: saikiran mothe <saikiran.mothe@gmail.com>; +Cc: 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
Attachments:
[application/pgp-signature] signature.asc (197B, ../../1352712512.1935.3.camel@lenovo01-laptop03.gunduz.org/2-signature.asc)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-12 10:44 Allan Kamau <kamauallan@gmail.com>
parent: Willem Leenen <willem_leenen@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Allan Kamau @ 2012-11-12 10:44 UTC (permalink / raw)
To: 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@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.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:
>
> 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<http://www.postgresql.org/mailpref/pgsql-sql;
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2012-11-13 15:19 Igor Neyman <ineyman@perceptron.com>
parent: saikiran mothe <saikiran.mothe@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Igor Neyman @ 2012-11-13 15:19 UTC (permalink / raw)
To: saikiran mothe <saikiran.mothe@gmail.com>; 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to compare two tables in PostgreSQL
@ 2014-09-15 10:43 Kamal Kumar TRR <kamalkumar.trr@gmail.com>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kamal Kumar TRR @ 2014-09-15 10:43 UTC (permalink / raw)
To: 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.ht...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-09-15 10:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-11 03:13 How to compare two tables in PostgreSQL saikiran mothe <saikiran.mothe@gmail.com>
2012-11-12 07:23 ` Rob Sargentg <robjsargent@gmail.com>
2012-11-12 08:00 ` Allan Kamau <kamauallan@gmail.com>
2012-11-12 09:13 ` Willem Leenen <willem_leenen@hotmail.com>
2012-11-12 10:44 ` Allan Kamau <kamauallan@gmail.com>
2012-11-12 09:20 ` Willem Leenen <willem_leenen@hotmail.com>
2012-11-12 09:28 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2012-11-13 15:19 ` Igor Neyman <ineyman@perceptron.com>
2014-09-15 10:43 ` Kamal Kumar TRR <kamalkumar.trr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox