Обсуждение: query for non-unique values?

I have a file of legacy data that has duplicate entries in a column that I'd
like to have a unique index on. How can I query to find the duplicates (so I
can determine if they can be thrown out)?

thx

Andy Kriger wrote:
> I have a file of legacy data that has duplicate entries in a column that I'd
> like to have a unique index on. How can I query to find the duplicates (so I
> can determine if they can be thrown out)?

If you have yet to learn about self joins, this is a great example of
where they are handy.  Assuming a table like:

create table abc
(
    id int not null, -- primary key
    name text,       -- duplicates are here
);

Try something like:

select b.*
from abc a, abc b
where a.id < b.id
   and a.name = b.name;

HTH,
Kevin

select dup_columns,count((*)
from table
group by dup_columns
having count(*) > 1

On Monday 23 September 2002 11:46 pm, Andy Kriger wrote:
> I have a file of legacy data that has duplicate entries in a column that
> I'd like to have a unique index on. How can I query to find the duplicates
> (so I can determine if they can be thrown out)?
>
> thx
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org