Re: [GENERAL] Selecting duplicates

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