pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcompare two rows
7+ messages / 4 participants
[nested] [flat]
* compare two rows
@ 2010-10-12 20:17 Ben Kim <bkim@tamu.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Ben Kim @ 2010-10-12 20:17 UTC (permalink / raw)
To: pgsql-admin
Admins,
What is the best way to compare two rows from within psql cli client?
It has ~30 fields, and the two rows are duplicate data but there might
be differences.
id field1 field2 field3 ...
===========================
id1 value1 value2 value3 ...
id2 value1 value2 value3 ...
I could write a generic script to iterate through the list of fields and
compare each field values, or concatenate the fields to a string, but wondered what is out there.
Also, is there a way around to use select row(...) = row(...)?
Thanks.
--
Ben Kim
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-12 21:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ben Kim <bkim@tamu.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-10-12 21:11 UTC (permalink / raw)
To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin
Ben Kim <bkim@tamu.edu> writes:
> What is the best way to compare two rows from within psql cli client?
In 8.4 and up you can just compare the row values, eg
select x.* = y.*
if x and y are known to be of the same rowtype.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-12 21:12 Richard Broersma <richard.broersma@gmail.com>
parent: Ben Kim <bkim@tamu.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Broersma @ 2010-10-12 21:12 UTC (permalink / raw)
To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin
On Tue, Oct 12, 2010 at 1:17 PM, Ben Kim <bkim@tamu.edu> wrote:
> Admins,
>
> What is the best way to compare two rows from within psql cli client?
>
> It has ~30 fields, and the two rows are duplicate data but there might
> be differences.
>
> id field1 field2 field3 ...
> ===========================
> id1 value1 value2 value3 ...
> id2 value1 value2 value3 ...
>
> I could write a generic script to iterate through the list of fields and
> compare each field values, or concatenate the fields to a string, but wondered what is out there.
>
> Also, is there a way around to use select row(...) = row(...)?
Does: (T1.value1,T1.value2, T1.value3, ... ) IS NOT DISTINCT FROM
(T2.value1,T2.value2, T2.value3,...)
or: Row( T1.* ) IS NOT DISTINCT FROM ( T2.* )
work for you?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-12 22:10 Ben Kim <bkim@tamu.edu>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Ben Kim @ 2010-10-12 22:10 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: pgsql-admin
Thanks.
I'm on 8.4.2.
I was not clear but the two rows are from the same table. Here's the test case.
create table test_dup (id serial primary key, val text);
insert into test_dup(val)values('some text');
insert into test_dup(val)values('some texta');
select * from test_dup;
id | val
----+------------
1 | some text
2 | some texta
How can I check whether the two rows are the same or different?
Thanks.
On Tue, Oct 12, 2010 at 02:12:36PM -0700, Richard Broersma wrote:
> On Tue, Oct 12, 2010 at 1:17 PM, Ben Kim <bkim@tamu.edu> wrote:
> > Admins,
> >
> > What is the best way to compare two rows from within psql cli client?
> >
> > It has ~30 fields, and the two rows are duplicate data but there might
> > be differences.
> >
> > id field1 field2 field3 ...
> > ===========================
> > id1 value1 value2 value3 ...
> > id2 value1 value2 value3 ...
> >
> > I could write a generic script to iterate through the list of fields and
> > compare each field values, or concatenate the fields to a string, but wondered what is out there.
> >
> > Also, is there a way around to use select row(...) = row(...)?
>
> Does: (T1.value1,T1.value2, T1.value3, ... ) IS NOT DISTINCT FROM
> (T2.value1,T2.value2, T2.value3,...)
> or: Row( T1.* ) IS NOT DISTINCT FROM ( T2.* )
> work for you?
>
>
>
>
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
> http://pugs.postgresql.org/lapug
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-12 22:17 Richard Broersma <richard.broersma@gmail.com>
parent: Ben Kim <bkim@tamu.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Broersma @ 2010-10-12 22:17 UTC (permalink / raw)
To: Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin
On Tue, Oct 12, 2010 at 3:10 PM, Ben Kim <bkim@tamu.edu> wrote:
> create table test_dup (id serial primary key, val text);
> insert into test_dup(val)values('some text');
> insert into test_dup(val)values('some texta');
>
> select * from test_dup;
> id | val
> ----+------------
> 1 | some text
> 2 | some texta
SELECT Array_agg( id ) AS duplicate_ids, val
FROM test_dup
GROUP BY val
HAVING COUNT(*) > 1;
SELECT T1.id, T2.id, T.val
FROM test_dup AS T1
INNER JOIN test_dup AS T2
USING ( val );
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-12 22:30 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Ben Kim <bkim@tamu.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2010-10-12 22:30 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; Ben Kim <bkim@tamu.edu>; +Cc: pgsql-admin
Ben Kim <bkim@tamu.edu> wrote:
> 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.
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: compare two rows
@ 2010-10-13 03:16 Ben Kim <bkim@tamu.edu>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Ben Kim @ 2010-10-13 03:16 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Richard Broersma <richard.broersma@gmail.com>; 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-10-13 03:16 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-12 20:17 compare two rows Ben Kim <bkim@tamu.edu>
2010-10-12 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-12 21:12 ` Richard Broersma <richard.broersma@gmail.com>
2010-10-12 22:10 ` Ben Kim <bkim@tamu.edu>
2010-10-12 22:17 ` Richard Broersma <richard.broersma@gmail.com>
2010-10-12 22:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-13 03:16 ` Ben Kim <bkim@tamu.edu>
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