From: Ben Kim <bkim@tamu.edu>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Richard Broersma <richard.broersma@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: compare two rows
Date: Tue, 12 Oct 2010 22:16:43 -0500
Message-ID: <20101013031643.GC1813@tamu.edu> (raw)
In-Reply-To: <4CB49B3402000025000368C0@gw.wicourts.gov>
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>
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bkim@tamu.edu, Kevin.Grittner@wicourts.gov, richard.broersma@gmail.com
Subject: Re: compare two rows
In-Reply-To: <20101013031643.GC1813@tamu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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