Re: [GENERAL] 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