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

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
========================================================================