Re: Deleting all but one row of a list of non-uniques - Mailing list pgsql-general

Try this.

Create a temp table with a list of the duplicate unid's

eg

create temp table duplicates
as
select min(oid) as oid_val, unid from <table>
group by unid having count(*) > 1;

Then isolate the unwanted rows

update <table>
set unid  = null  <this could be any value you choose>
from duplicates
where <table>.unid = duplicates.unid
and    oid_val <> <table>.oid

Then delete them

delete from <table> where unid is null

Thanks

Andrew

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Zak McGregor
Sent: Wednesday, 23 June 2004 10:18 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Deleting all but one row of a list of non-uniques


Hi all


I have a table, for simplicity's sake containing one field, called unid.


for example, select unid, oid from table gives me something like this:

   unid   |   oid
 ---------+---------
 XNO24ORK | 40276607
 XNPGJDPU | 40276673 *
 XNPGJDPU | 40276674 *
 XNXAAVQ2 | 40277583
 ZAZAFAA4 | 40276600
 ZAZV5UG4 | 40276446
 ZD66A1LL | 40277162
 ZDXZ27RS | 40277454
 ZEKRT3GM | 40277739 *
 ZEKRT3GM | 40277740 *
 ZEKRT3GM | 40277741 *

(I've marked the rows with duplicated unids with the * to the right)

I'd like to delete rows in such a way that one (and any one) row for each
unid
remains, and all other duplicates are removed. Does anyone have any ideas
that
may help me here please?

slecting distinct is not helpful as in reality there are other fields which
contain data like timestamps that will differ but I don't mind which one
remains, but I'd need to work with them on selects to the table afterwards.

 I've tried:

 delete from table where oid in (select p1.oid from table p1, table p2 where
p1.oid != p2.oid and p1.unid=p2.unid);

which only works in a few cases - I suspect where there are only two rows
with
the same unid. Is it even possible?

Alternatively, can I get the \copy command to ignore attempts to insert
duplicated rows into a UNIQUE column instead of aborting the copy? Not sure
if
any of the options that can be supplied to the table at creation time for
unique
will help here.

Thanks.

Ciao

Zak

--
========================================================================
http://www.carfolio.com/        Searchable database of 10 000+ car specs
========================================================================

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match