From: | John Sidney-Woollett <johnsw(at)wardbrook(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: remote duplicate rows |
Date: | 2006-09-14 07:40:07 |
Message-ID: | 45090757.6000906@wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If you have a primary key value (or OID?) then you can delete the
duplicates in situ using something like (untested)
-- should work if never more than 1 duplicate row for colname1, colname2
delete from table where pk_value in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) > 1
)
-- if you can have multiple duplicate rows for colname1, colname2
-- then you need something like
delete from table where pk_value not in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) = 1
)
Hope that helps.
John
A. Kretschmer wrote:
> am Wed, dem 13.09.2006, um 15:46:58 -0700 mailte Junkone folgendes:
>> hI
>> i have a bad situation that i did not have primary key. so i have a
>> table like this
>> colname1 colname2
>> 1 apple
>> 1 apple
>> 2 orange
>> 2 orange
>>
>> It is a very large table. how do i remove the duplctes quickly annd
>> without much change.
>
> begin;
> alter table foo rename to tmp;
> create table foo as select distinct * from tmp;
> commit;
>
> You should create a primary key now to avoid duplicated entries...
>
>
> HTH, Andreas
From | Date | Subject | |
---|---|---|---|
Next Message | Dhanaraj M | 2006-09-14 10:41:06 | Is there any utility to update the table whenever text file gets changed? |
Previous Message | Stijn Vanroye | 2006-09-14 07:16:26 | Re: select unique items in db |