Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8E5162E0050
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 19 Mar 2008 19:29:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 83308-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 19 Mar 2008 19:29:26 -0300 (ADT)
Received: from mx3.hub.org (mx3.hub.org [206.223.169.73])
	by postgresql.org (Postfix) with ESMTP id 5BB7D2E003D
	for <pgsql-sql@postgresql.org>; Wed, 19 Mar 2008 19:29:26 -0300 (ADT)
X-Greylist: delayed 00:29:21.297991 by SQLgrey-1.7.5
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by mx3.hub.org (Postfix) with ESMTP id E619D383284
	for <pgsql-sql@postgresql.org>; Wed, 19 Mar 2008 19:29:25 -0300 (ADT)
Received: from [172.24.2.1] (pool-71-113-24-134.sttlwa.dsl-w.verizon.net
	[71.113.24.134])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 3D23D25ED51
	for <pgsql-sql@postgresql.org>; Wed, 19 Mar 2008 14:59:58 -0700 (PDT)
Message-ID: <47E18CFF.2020408@ultimeth.com>
Date: Wed, 19 Mar 2008 15:00:31 -0700
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Reply-To: pgsql-sql <pgsql-sql@postgresql.org>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: compare 2 tables in sql
References: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F138@egcrc-ex01.egcrc.org>
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F138@egcrc-ex01.egcrc.org>
Content-Type: multipart/alternative;
	boundary="------------060200010703050900060401"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/125
X-Sequence-Number: 30453

This is a multi-part message in MIME format.
--------------060200010703050900060401
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.


--------------060200010703050900060401
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-03-19 10:56, Tena Sakai wrote:
<blockquote
 cite="mid:FE44E0D7EAD2ED4BB2165071DB8E328C0378F138@egcrc-ex01.egcrc.org"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator"
 content="MS Exchange Server version 6.5.7651.59">
  <title>compare 2 tables in sql</title>
<!-- Converted from text/plain format -->
  <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 table foo as<br>
  [select bla bla bla];<br>
  <br>
  create table moo as<br>
  [select bla bla bla];<br>
  <br>
How would I go about knowing foo and moo are identical (or not)?  Any
pointer would be appreciated.<br>
  <br>
Tena<br>
  </font>
  </p>
</blockquote>
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.<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>
</body>
</html>

--------------060200010703050900060401--