Re: How to compare two tables in PostgreSQL

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>
>
>
>