Date: Tue, 12 Oct 2010 22:16:43 -0500
From: Ben Kim <bkim@tamu.edu>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Richard Broersma <richard.broersma@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: compare two rows
Message-ID: <20101013031643.GC1813@tamu.edu>
References: <AANLkTimX4BNmUm7KJ72FUBu+FgcKDxZdMcK4FkTfOmwA@mail.gmail.com>
	<4CB479DB02000025000368A9@gw.wicourts.gov>
	<20101012201747.GA720@tamu.edu>
	<AANLkTinpScsTqJwiM_=3=PN+jjdvHrR8RTdWToA_iP7P@mail.gmail.com>
	<20101012221055.GC985@tamu.edu>
	<4CB49B3402000025000368C0@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4CB49B3402000025000368C0@gw.wicourts.gov>
User-Agent: Mutt/1.5.20 (2009-12-10)

> > create table test_dup (id serial primary key, val text);
>  
> > How can I check whether the two rows are the same or different?
>  
> Well, with a primary key in there, they had *better* be different.
>  
> It would seem you want to see if some *subset* of the columns in two
> rows match?  All columns except those in the primary key?
>  
> The next question is whether you want to just compare two specific
> rows or list all duplicates.

I was wanting something like 

select row(select ... from test_dup where id=1) = row(select ... from
  test_dup where id=2)

where ... is all fields except the primary key field. (Which can be
more than 100 fields in some tables.)

I guess I've roughly found what I need between the answers posted. 

select row(t1.*) = row(t2.*) from 
(select val1, val2, val3, ..., val100 from test_dup where id=1) t1,
(select val1, val2, val3, ..., val100 from test_dup where id=2) t2
;


This gave me a blank row when all of val1 ... val100 matched, and a
value of "f" when something did not match.

(Wish there was a shorthand way to express "all fields (*)  except id field". Is it possible?)


Thanks.

Ben Kim