Selecting duplicates

Gary Hoffman wrote:

Somehow, I've managed to get duplicate entries in my soon-to-be primary
key field. How can I select for duplicates in a field? I know how to
select for blank and NULL, but duplicates escape me.

Sorry, I only know complicated ways of doing this. The way I usually do
it is to create a temporary table:

create temp table tmp (id int4, cnt int4);
insert into tmp select id, count(*) from <table> group by id;

Then look at all entries in tmp where cnt is bigger than 1. Deciding
which entry to throw out is tougher. I have been working with the
assumption that oids are (usually anyway) assigned in ascending order.
Don't actually know whether that is true. But if they are you can delete
everything but the entry with the highest (or lowest) oid.

Hope this helps,

Adriaan

Use a self-join:

select t1.oid, t2.oid, t1.field
from table t1, table t2 where
t1.field = t2.field and t1.oid <> t2.oid;

where table is the table name in question and
field is the field name of the primary key in
question.

"oid", is, of course, the unique id PostgreSQL
assigns to each record (tuple) in the database.

Hope that helps,

Mike Mascari (mascarim@yahoo.com)

--- Adriaan Joubert <a.joubert@albourne.com> wrote:

Gary Hoffman wrote:

Somehow, I've managed to get duplicate entries in

my soon-to-be primary

key field. How can I select for duplicates in a

field? I know how to

select for blank and NULL, but duplicates escape

me.

Sorry, I only know complicated ways of doing this.
The way I usually do
it is to create a temporary table:

create temp table tmp (id int4, cnt int4);
insert into tmp select id, count(*) from <table>
group by id;

Then look at all entries in tmp where cnt is bigger
than 1. Deciding
which entry to throw out is tougher. I have been
working with the
assumption that oids are (usually anyway) assigned
in ascending order.
Don't actually know whether that is true. But if
they are you can delete
everything but the entry with the highest (or
lowest) oid.

Hope this helps,

Adriaan

_____________________________________________________________
Do You Yahoo!?
Free instant messaging and more at http://messenger.yahoo.com

vac=> create table t (x int);
CREATE
vac=> insert into t values (1);
INSERT 18579 1
vac=> insert into t values (1);
INSERT 18580 1
vac=> insert into t values (2);
INSERT 18581 1
vac=> select x from t group by x having count(*) > 1;
x
-
1
(1 row)

Vadim

Hi Garry

Perhaps following will help you (asuming that "id" is your soon-to-be
primary key):

select oid, id from tab a where exists
(select oid from tab b where b.id=a.id and b.oid<>a.oid)
order by id

Sincerely yours, Yury.
don.web-page.net, ICQ 11831432

Gary Hoffman wrote:

--
~Adriaan Joubert wrote:

Gary Hoffman wrote:

Somehow, I've managed to get duplicate entries in my soon-to-be primary
key field. How can I select for duplicates in a field? I know how to
select for blank and NULL, but duplicates escape me.

Sorry, I only know complicated ways of doing this. The way I usually do
it is to create a temporary table:

create temp table tmp (id int4, cnt int4);
insert into tmp select id, count(*) from <table> group by id;

The easier way

select field_with_duplicates, count(*) from table
group by field_with_duplicate having count(*) > 1;

Greatings Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~