Thread: compare 2 tables in sql

compare 2 tables in sql

From
"Tena Sakai"
Date:
<p><font size="2">Hi Everybody,<br /><br /> Is there a sql way to compare (in a diff/cmp sense)<br /> 2 tables?  For
example,<br/><br />   create table foo as<br />   [select bla bla bla];<br /><br />   create table moo as<br />  
[selectbla bla bla];<br /><br /> How would I go about knowing foo and moo are identical<br /> (or not)?  Any pointer
wouldbe appreciated.<br /><br /> Tena<br /></font> 

Re: compare 2 tables in sql

From
Volkan YAZICI
Date:
On Wed, 19 Mar 2008, "Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
> Is there a sql way to compare (in a diff/cmp sense)
> 2 tables?

You can diff "pg_dump --schema-only" output of the related tables. (I
attached an ad-hoc script once I wrote to use for such stuff.) I don't
know about [php]pgadmin, but (IIRC) EMS products offer that
functionality.


Regards.


Attachment

Re: compare 2 tables in sql

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-03-19 10:56, Tena Sakai wrote: <blockquote
cite="mid:FE44E0D7EAD2ED4BB2165071DB8E328C0378F138@egcrc-ex01.egcrc.org"type="cite"></blockquote><p><font size="2">Hi
Everybody,<br/><br /> Is there a sql way to compare (in a diff/cmp sense) 2 tables?  For example,<br /><br />   create
tablefoo as<br />   [select bla bla bla];<br /><br />   create table moo as<br />   [select bla bla bla];<br /><br />
Howwould I go about knowing foo and moo are identical (or not)?  Any pointer would be appreciated.<br /><br /> Tena<br
/></font> You could do a full outer join of "foo" and "moo" on whatever is the common key, and then delete those rows
whichdon't have null fields in the either the left or right sides;  the remainder would be the differences.<br /><pre
class="moz-signature"cols="72">-- 
 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>

Re: compare 2 tables in sql

From
"Jonah H. Harris"
Date:
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/


Re: compare 2 tables in sql

From
"Tena Sakai"
Date:
<p><font size="2">Fantastic!  Many thanks.<br /><br /> Regards,<br /><br /> Tena Sakai<br /><br /><br /> -----Original
Message-----<br/> From: pgsql-sql-owner@postgresql.org on behalf of Jonah H. Harris<br /> Sent: Wed 3/19/2008 3:39
PM<br/> To: Tena Sakai<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] compare 2 tables in sql<br /><br />
OnWed, Mar 19, 2008 at 1:56 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:<br /> >  Is there a sql way to
compare(in a diff/cmp sense)<br /> >  2 tables?  For example,<br /><br /> SELECT * FROM foo<br /> EXCEPT<br />
SELECT* FROM moo;<br /><br /> --<br /> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324<br /> EnterpriseDB
Corporation| fax: 732.331.1301<br /> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com<br /> Edison, NJ
08837| <a href="http://www.enterprisedb.com/">http://www.enterprisedb.com/</a><br /><br /> --<br /> Sent via pgsql-sql
mailinglist (pgsql-sql@postgresql.org)<br /> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></font> 

Re: compare 2 tables in sql

From
"Jonah H. Harris"
Date:
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/


Re: compare 2 tables in sql

From
"Tena Sakai"
Date:
<p><font size="2">Hi Jonah,<br /><br /> > Still, you should UNION the result of<br /> > both exceptions into a
singleresult set.<br /><br /> Great suggestion.  Many thanks.<br /><br /> Regards,<br /><br /> Tena Sakai<br />
tsakai@gallo.ucsf.edu<br/><br /><br /> -----Original Message-----<br /> From: Jonah H. Harris [<a
href="mailto:jonah.harris@gmail.com">mailto:jonah.harris@gmail.com</a>]<br/> Sent: Thu 3/20/2008 12:21 PM<br /> To:
TenaSakai<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] compare 2 tables in sql<br /><br /> On Thu, Mar
20,2008 at 1:44 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:<br /> >  Just a postscript.  It is important to
check<br/> >  both ways.  Because (sometimes) vice versa is<br /> >  not necessarily true.  Case in point
below:<br/><br /> Yes, I'm well aware of that.  Still, you should UNION the result of<br /> both exceptions into a
singleresult set.<br /><br /> --<br /> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324<br /> EnterpriseDB
Corporation| fax: 732.331.1301<br /> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com<br /> Edison, NJ
08837| <a href="http://www.enterprisedb.com/">http://www.enterprisedb.com/</a><br /><br /></font> 

Re: compare 2 tables in sql

From
"Tena Sakai"
Date:
<p><font size="2">Hi Jonah,<br /><br /> Just a postscript.  It is important to check<br /> both ways.  Because
(sometimes)vice versa is<br /> not necessarily true.  Case in point below:<br /><br /><br /> blitzen=> select * from
foo<br/> blitzen-> except<br /> blitzen->        select * from moo;<br />  alleleid | markerid | value |
datecreated| datereplaced<br /> ----------+----------+-------+-------------+--------------<br /> (0 rows)<br /><br />
blitzen=><br/> blitzen=> select * from moo<br /> blitzen-> except<br /> blitzen->        select * from
foo;<br/>   some_id | anothrid | value |       datecreated       |    datereplaced    <br />
----------+----------+-------+-------------------------+---------------------<br/>   2892473 |  2810329 | t     |
2008-03-1214:37:18.165 | 3000-01-01 12:00:00<br /> (1 row)<br /><br /><br /> Regards,<br /><br /> Tena Sakai<br />
tsakai@gallo.ucsf.edu<br/><br /><br /> -----Original Message-----<br /> From: Jonah H. Harris [<a
href="mailto:jonah.harris@gmail.com">mailto:jonah.harris@gmail.com</a>]<br/> Sent: Wed 3/19/2008 3:39 PM<br /> To: Tena
Sakai<br/> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] compare 2 tables in sql<br /><br /> On Wed, Mar 19,
2008at 1:56 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:<br /> >  Is there a sql way to compare (in a
diff/cmpsense)<br /> >  2 tables?  For example,<br /><br /> SELECT * FROM foo<br /> EXCEPT<br /> SELECT * FROM
moo;<br/><br /> --<br /> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324<br /> EnterpriseDB Corporation |
fax:732.331.1301<br /> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com<br /> Edison, NJ 08837 | <a
href="http://www.enterprisedb.com/">http://www.enterprisedb.com/</a><br/><br /></font>