Re: compare 2 tables in sql

Hi Everybody,

Is there a sql way to compare (in a diff/cmp sense) 2 tables?  For example,

  create table foo as
  [select bla bla bla];

  create table moo as
  [select bla bla bla];

How would I go about knowing foo and moo are identical (or not)?  Any pointer would be appreciated.

Tena
You could do a full outer join of "foo" and "moo" on whatever is the common key, and then delete those rows which don't have null fields in the either the left or right sides;  the remainder would be the differences.

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

Hi Everybody,

Is there a sql way to compare (in a diff/cmp sense)
2 tables?  For example,

  create table foo as
  [select bla bla bla];

  create table moo as
  [select bla bla bla];

How would I go about knowing foo and moo are identical
(or not)?  Any pointer would be appreciated.

Tena

Fantastic!  Many thanks.

Regards,

Tena Sakai


-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Jonah H. Harris
Sent: Wed 3/19/2008 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql

On Wed, Mar 19, 2008 at 1:56 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>  Is there a sql way to compare (in a diff/cmp sense)
>  2 tables?  For example,

SELECT * FROM foo
EXCEPT
SELECT * FROM moo;

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Hi Jonah,

Just a postscript.  It is important to check
both ways.  Because (sometimes) vice versa is
not necessarily true.  Case in point below:


blitzen=> select * from foo
blitzen-> except
blitzen->        select * from moo;
 alleleid | markerid | value | datecreated | datereplaced
----------+----------+-------+-------------+--------------
(0 rows)

blitzen=>
blitzen=> select * from moo
blitzen-> except
blitzen->        select * from foo;
  some_id | anothrid | value |       datecreated       |    datereplaced    
----------+----------+-------+-------------------------+---------------------
  2892473 |  2810329 | t     | 2008-03-12 14:37:18.165 | 3000-01-01 12:00:00
(1 row)


Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Wed 3/19/2008 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql

On Wed, Mar 19, 2008 at 1:56 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>  Is there a sql way to compare (in a diff/cmp sense)
>  2 tables?  For example,

SELECT * FROM foo
EXCEPT
SELECT * FROM moo;

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

Hi Jonah,

> Still, you should UNION the result of
> both exceptions into a single result set.

Great suggestion.  Many thanks.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Thu 3/20/2008 12:21 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql

On Thu, Mar 20, 2008 at 1:44 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>  Just a postscript.  It is important to check
>  both ways.  Because (sometimes) vice versa is
>  not necessarily true.  Case in point below:

Yes, I'm well aware of that.  Still, you should UNION the result of
both exceptions into a single result set.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/