Обсуждение: compare 2 tables in sql
<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>
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.
Вложения
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>
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/
<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>
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/
<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>
<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>