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