From: | Ben Kim <bkim(at)tamu(dot)edu> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: compare two rows |
Date: | 2010-10-13 03:16:43 |
Message-ID: | 20101013031643.GC1813@tamu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
> > 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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2010-10-13 04:52:15 | Re: 5 pg_toast_temp and pg_temp schemas ? |
Previous Message | Kevin Grittner | 2010-10-12 22:30:28 | Re: compare two rows |