> > 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