Re: delete non-unique

am  Wed, dem 25.04.2007, um 13:08:15 +0400 mailte henlin folgendes:
> hello.
>
>
> i need to clean up some tables: from several equal rows keep only one
> (like `sort -u`)

You can create a new table as select distinct from the original table
and rename both tables properly.

Other solution, i demonstrate with an example:

test=*# select ctid,* from doubles ;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | foo
 (0,2) |  1 | foo
 (0,3) |  2 | bar
 (0,4) |  2 | bar
 (0,5) |  2 | bar
 (0,6) |  3 | batz
 (0,7) |  3 | batz
 (0,8) |  3 | batz
(8 rows)


test=*# delete from doubles where ctid not in (select distinct on (id, name) ctid from doubles );
DELETE 5
test=*# select ctid,* from doubles ;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | foo
 (0,3) |  2 | bar
 (0,6) |  3 | batz
(3 rows)



The ctid-column is a hidden column with an unique value within this
table.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net