pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate rows
12+ messages / 7 participants
[nested] [flat]
* Duplicate rows
@ 1998-05-15 20:01 Andy Lewis <alewis@mpsi.net>
0 siblings, 1 reply; 12+ messages in thread
From: Andy Lewis @ 1998-05-15 20:01 UTC (permalink / raw)
To: pgsql-sql
I have a postgres db with at least 1000 rows in it. I'd like to know if its
possible to detect duplicate values of a column if I don't know which values
that are dups?
Thanks
Andy
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Duplicate rows
@ 1998-05-16 09:31 Oleg Broytmann <phd@comus.ru>
parent: Andy Lewis <alewis@mpsi.net>
0 siblings, 1 reply; 12+ messages in thread
From: Oleg Broytmann @ 1998-05-16 09:31 UTC (permalink / raw)
To: Andy Lewis <alewis@mpsi.net>; +Cc: pgsql-sql
Hello.
On Fri, 15 May 1998, Andy Lewis wrote:
> I have a postgres db with at least 1000 rows in it. I'd like to know if its
> possible to detect duplicate values of a column if I don't know which values
> that are dups?
Try to create UNIQUE index on a problematic column. If creating the
unique index failes - there are duplicates (providing that creating the
index wouldn't fail by other reasons - disk full, memory shortage, etc.)
Oleg.
----
Oleg Broytmann http://members.tripod.com/~phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Duplicate rows
@ 1998-05-16 13:18 Andy Lewis <alewis@mpsi.net>
parent: Oleg Broytmann <phd@comus.ru>
0 siblings, 2 replies; 12+ messages in thread
From: Andy Lewis @ 1998-05-16 13:18 UTC (permalink / raw)
To: phd2@earthling.net; +Cc: pgsql-sql
Right, I know that there are dups in the column. But, I don't know where they
are nor do I know their value(s). I want to be able to find, say, two interger
values that are in the same column but, different rows.
Thanks
Andy
On Sat, 16 May 1998, Oleg Broytmann wrote:
>Hello.
>
>On Fri, 15 May 1998, Andy Lewis wrote:
>> I have a postgres db with at least 1000 rows in it. I'd like to know if its
>> possible to detect duplicate values of a column if I don't know which values
>> that are dups?
>
> Try to create UNIQUE index on a problematic column. If creating the
>unique index failes - there are duplicates (providing that creating the
>index wouldn't fail by other reasons - disk full, memory shortage, etc.)
>
>Oleg.
>----
> Oleg Broytmann http://members.tripod.com/~phd2/ phd2@earthling.net
> Programmers don't die, they just GOSUB without RETURN.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Duplicate rows
@ 1998-05-16 14:02 Bruce Stephens <bruce@cenderis.demon.co.uk>
parent: Andy Lewis <alewis@mpsi.net>
1 sibling, 0 replies; 12+ messages in thread
From: Bruce Stephens @ 1998-05-16 14:02 UTC (permalink / raw)
To: pgsql-sql
Andy Lewis <alewis@mpsi.net> writes:
> Right, I know that there are dups in the column. But, I don't know
> where they are nor do I know their value(s). I want to be able to
> find, say, two interger values that are in the same column but,
> different rows.
How about
SELECT t1.col,t1.oid, t2.oid FROM fred AS t1, fred AS t2
WHERE t1.col=t2.col AND t1.oid<t2.oid
(I haven't tested this, but it's the right kind of idea: select the
interesting bits from a self-join.)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Duplicate rows
@ 1998-05-16 14:06 Oleg Broytmann <phd@comus.ru>
parent: Andy Lewis <alewis@mpsi.net>
1 sibling, 1 reply; 12+ messages in thread
From: Oleg Broytmann @ 1998-05-16 14:06 UTC (permalink / raw)
To: Andy Lewis <alewis@mpsi.net>; +Cc: pgsql-sql
Hi!
On Sat, 16 May 1998, Andy Lewis wrote:
> Right, I know that there are dups in the column. But, I don't know where they
> are nor do I know their value(s). I want to be able to find, say, two interger
> values that are in the same column but, different rows.
It seems that you need a correlated subquery - a loop for every row, that
tests whether there are equal values.
SELECT oid, mycolumn FROM mytable a
WHERE mycolumn IN
(SELECT oid, mycolumn FROM mytable b
WHERE a.oid <> b.oid)
Or may be, join with the same table. Not sure what is better in this
situation.
SELECT oid, mycolumn FROM mytable a, mytable b
WHERE a.oid <> b.oid AND
a.mycolumn = b.mycolumn
In both cases "a.oid <> b.oid" excludes the same row from comparison (I
am pretty sure that in the same row a.mycolumn = b.column :).
Oleg.
----
Oleg Broytmann http://members.tripod.com/~phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Duplicate rows
@ 1998-05-16 14:10 Andy Lewis <alewis@mpsi.net>
parent: Oleg Broytmann <phd@comus.ru>
0 siblings, 0 replies; 12+ messages in thread
From: Andy Lewis @ 1998-05-16 14:10 UTC (permalink / raw)
To: phd2@earthling.net; +Cc: pgsql-sql
Thanks, I'll give it a try.
Andy
On Sat, 16 May 1998, Oleg Broytmann wrote:
>Hi!
>
>On Sat, 16 May 1998, Andy Lewis wrote:
>> Right, I know that there are dups in the column. But, I don't know where they
>> are nor do I know their value(s). I want to be able to find, say, two interger
>> values that are in the same column but, different rows.
>
> It seems that you need a correlated subquery - a loop for every row, that
>tests whether there are equal values.
>
>SELECT oid, mycolumn FROM mytable a
> WHERE mycolumn IN
> (SELECT oid, mycolumn FROM mytable b
> WHERE a.oid <> b.oid)
>
>Or may be, join with the same table. Not sure what is better in this
>situation.
>
>SELECT oid, mycolumn FROM mytable a, mytable b
> WHERE a.oid <> b.oid AND
> a.mycolumn = b.mycolumn
>
> In both cases "a.oid <> b.oid" excludes the same row from comparison (I
>am pretty sure that in the same row a.mycolumn = b.column :).
>
>Oleg.
>----
> Oleg Broytmann http://members..tripod.com/~phd2/ phd2@earthling.net
> Programmers don't die, they just GOSUB without RETURN.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Duplicate rows
@ 2010-08-10 19:18 Edward W. Rouse <erouse@comsquared.com>
0 siblings, 2 replies; 12+ messages in thread
From: Edward W. Rouse @ 2010-08-10 19:18 UTC (permalink / raw)
To: pgsql-sql
Is there any way to remove a duplicate row from a table? Not my db but I
have to work with it. On version 7.4 right now.
Edward W. Rouse
Comsquared System, Inc.
770-734-5301
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Duplicate rows
@ 2010-08-10 19:44 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Edward W. Rouse <erouse@comsquared.com>
1 sibling, 1 reply; 12+ messages in thread
From: Andreas Kretschmer @ 2010-08-10 19:44 UTC (permalink / raw)
To: pgsql-sql
Edward W. Rouse <erouse@comsquared.com> wrote:
> Is there any way to remove a duplicate row from a table? Not my db but I have
> to work with it. On version 7.4 right now.
>
How to select the right records?
You can try to use the ctid-column, see my simple example:
test=# select * from dups ;
i
---
1
1
1
2
2
3
4
(7 Zeilen)
Zeit: 0,145 ms
test=*# delete from dups where (ctid, i) not in (select max(ctid), i from dups group by i);
DELETE 3
Zeit: 0,378 ms
test=*# select * from dups ;
i
---
1
2
3
4
(4 Zeilen)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Duplicate rows
@ 2010-08-10 19:59 msi77 <msi77@yandex.ru>
parent: Edward W. Rouse <erouse@comsquared.com>
1 sibling, 0 replies; 12+ messages in thread
From: msi77 @ 2010-08-10 19:59 UTC (permalink / raw)
To: Edward W. Rouse <erouse@comsquared.com>; +Cc: pgsql-sql
A few ways to do this:
http://www.sql-ex.ru/help/select17.php
> Is there any way to remove a duplicate row from a table? Not my db but I have to work with it. On version 7.4 right now.
> Edward W. Rouse
> Comsquared System, Inc.
> 770-734-5301
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Duplicate rows
@ 2010-08-10 20:42 Edward W. Rouse <erouse@comsquared.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 12+ messages in thread
From: Edward W. Rouse @ 2010-08-10 20:42 UTC (permalink / raw)
To: pgsql-sql
I am trying to test this but get an error.
select ctid, * from test where id < 3000000 order by id, ctid;
ERROR: could not identify an ordering operator for type tid
HINT: Use an explicit ordering operator or modify the query.
If I do a select I get this:
select ctid, * from test where id < 3000000 order by id;
ctid | id | activated | wake_up_time
(108,22) | 316 | f |
(36,17) | 316 | f |
(used 2 rows only for brevity
And when I tried max(ctid) I got:
ERROR: function max(tid) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
I appreciate all the help and this feels like I'm almost there.
Thanks
Edward W. Rouse
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andreas Kretschmer
Sent: Tuesday, August 10, 2010 3:45 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Duplicate rows
Edward W. Rouse <erouse@comsquared.com> wrote:
> Is there any way to remove a duplicate row from a table? Not my db but I
have
> to work with it. On version 7.4 right now.
>
How to select the right records?
You can try to use the ctid-column, see my simple example:
test=# select * from dups ;
i
---
1
1
1
2
2
3
4
(7 Zeilen)
Zeit: 0,145 ms
test=*# delete from dups where (ctid, i) not in (select max(ctid), i from
dups group by i);
DELETE 3
Zeit: 0,378 ms
test=*# select * from dups ;
i
---
1
2
3
4
(4 Zeilen)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Duplicate rows
@ 2010-08-10 21:19 Edward W. Rouse <erouse@comsquared.com>
parent: Edward W. Rouse <erouse@comsquared.com>
0 siblings, 1 reply; 12+ messages in thread
From: Edward W. Rouse @ 2010-08-10 21:19 UTC (permalink / raw)
To: pgsql-sql
Solved. Because this is a 7.4 version and we used with oids by default, I
can use the oids instead of the ctid to remove the duplicates.
Thanks.
Edward W. Rouse
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Edward W. Rouse
Sent: Tuesday, August 10, 2010 4:43 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Duplicate rows
I am trying to test this but get an error.
select ctid, * from test where id < 3000000 order by id, ctid;
ERROR: could not identify an ordering operator for type tid
HINT: Use an explicit ordering operator or modify the query.
If I do a select I get this:
select ctid, * from test where id < 3000000 order by id;
ctid | id | activated | wake_up_time
(108,22) | 316 | f |
(36,17) | 316 | f |
(used 2 rows only for brevity
And when I tried max(ctid) I got:
ERROR: function max(tid) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
I appreciate all the help and this feels like I'm almost there.
Thanks
Edward W. Rouse
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andreas Kretschmer
Sent: Tuesday, August 10, 2010 3:45 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Duplicate rows
Edward W. Rouse <erouse@comsquared.com> wrote:
> Is there any way to remove a duplicate row from a table? Not my db but I
have
> to work with it. On version 7.4 right now.
>
How to select the right records?
You can try to use the ctid-column, see my simple example:
test=# select * from dups ;
i
---
1
1
1
2
2
3
4
(7 Zeilen)
Zeit: 0,145 ms
test=*# delete from dups where (ctid, i) not in (select max(ctid), i from
dups group by i);
DELETE 3
Zeit: 0,378 ms
test=*# select * from dups ;
i
---
1
2
3
4
(4 Zeilen)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Duplicate rows
@ 2010-08-11 04:50 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Edward W. Rouse <erouse@comsquared.com>
0 siblings, 0 replies; 12+ messages in thread
From: A. Kretschmer @ 2010-08-11 04:50 UTC (permalink / raw)
To: pgsql-sql
In response to Edward W. Rouse :
> Solved. Because this is a 7.4 version and we used with oids by default, I
> can use the oids instead of the ctid to remove the duplicates.
Yeah, that's right ;-)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2010-08-11 04:50 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-15 20:01 Duplicate rows Andy Lewis <alewis@mpsi.net>
1998-05-16 09:31 ` Oleg Broytmann <phd@comus.ru>
1998-05-16 13:18 ` Andy Lewis <alewis@mpsi.net>
1998-05-16 14:02 ` Bruce Stephens <bruce@cenderis.demon.co.uk>
1998-05-16 14:06 ` Oleg Broytmann <phd@comus.ru>
1998-05-16 14:10 ` Andy Lewis <alewis@mpsi.net>
2010-08-10 19:18 Duplicate rows Edward W. Rouse <erouse@comsquared.com>
2010-08-10 19:44 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-08-10 20:42 ` Edward W. Rouse <erouse@comsquared.com>
2010-08-10 21:19 ` Edward W. Rouse <erouse@comsquared.com>
2010-08-11 04:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-08-10 19:59 ` msi77 <msi77@yandex.ru>
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