pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcompare 2 tables in sql
8+ messages / 4 participants
[nested] [flat]
* compare 2 tables in sql
@ 2008-03-19 17:56 Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 19:09 ` Re: compare 2 tables in sql Volkan YAZICI <yazicivo@ttmail.com>
2008-03-19 22:00 ` Re: compare 2 tables in sql Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-03-19 22:39 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Tena Sakai @ 2008-03-19 17:56 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2008-03-19 19:09 ` Volkan YAZICI <yazicivo@ttmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Volkan YAZICI @ 2008-03-19 19:09 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
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.
Attachments:
pg_difftable (application/octet-stream, 888B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2008-03-19 22:00 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2 siblings, 0 replies; 8+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-03-19 22:00 UTC (permalink / raw)
To: pgsql-sql
On 2008-03-19 10:56, Tena Sakai wrote:
>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2008-03-19 22:39 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 02:45 ` Re: compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-20 17:44 ` Re: compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2 siblings, 2 replies; 8+ messages in thread
From: Jonah H. Harris @ 2008-03-19 22:39 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 22:39 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
@ 2008-03-20 02:45 ` Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Tena Sakai @ 2008-03-20 02:45 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 22:39 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
@ 2008-03-20 17:44 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-20 19:21 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tena Sakai @ 2008-03-20 17:44 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 22:39 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 17:44 ` Re: compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2008-03-20 19:21 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 20:04 ` Re: compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Jonah H. Harris @ 2008-03-20 19:21 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: compare 2 tables in sql
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 22:39 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 17:44 ` Re: compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-20 19:21 ` Re: compare 2 tables in sql Jonah H. Harris <jonah.harris@gmail.com>
@ 2008-03-20 20:04 ` Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Tena Sakai @ 2008-03-20 20:04 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-03-20 20:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-19 17:56 compare 2 tables in sql Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-19 19:09 ` Volkan YAZICI <yazicivo@ttmail.com>
2008-03-19 22:00 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-03-19 22:39 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 02:45 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-20 17:44 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2008-03-20 19:21 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-03-20 20:04 ` Tena Sakai <tsakai@gallo.ucsf.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox