Re: Finding (and deleting) dupes in relation table

Hello!

CSN [cool_screen_name90001(at)yahoo(dot)com] wrote:
> I have a table that relates id's of two other tables:
>
> table1id, table2id
>
> Dupes have found their way into it (create unique index
> across both fields fails). Is there a quick and easy way to
> find and delete the dupes (there are tens of thousands of records)?
>
> Thanks,
> CSN

If your table was created WITH OIDS you could identify the duplicates
thus:

select a.table1id
, a.table12d
, max(a.oid) as maxoid
, count(a.oid) as coid
from schema.mytable a,
schema.mytable b
where a.table1id = b.table1id
and a.table2id=b.table2id
and a.oid <> b.oid
group by a.table1id, a.table2id
order by a.table1id;

If you wish to delete surplus rows, you might do the following:

delete from schema.mytable where oid in (
select maxoid from (
select a.table1id, a.table12d, max(a.oid) as
maxoid, count(a.oid) as coid
from schema.mytable a,
schema.mytable b
where a.table1id = b.table1id
and a.table2id=b.table2id
and a.oid <> b.oid
group by a.table1id, a.table2id
order by a.table1id ) as foo
where coid >1 );

This will delete the oldest tuple of a duplicate set of rows; if there
are more than two tuples in a set, you'll want to execute this a couple
of times until there's no duplicate left, as the delete will only reduce
a set by one tuple at a time. I'd also recommend to apply a PRIMARY KEY
constraint afterwards instead of just a unique index - this will prevent
NULL-entries as well as creating the desired unique index - and I think
it's good practice to have a primary key on about every table there is,
except when it's just a junk data table like a logging table where
content is regularly evaluated and discarded.

Kind regards

Markus