Re: [GENERAL] Delete Duplicates with Using

Am 14.10.2017 um 08:20 schrieb Igal @ Lucee.org:
>
> Hello,
>
> I run the SQL query below to delete duplicates from a table. The 
> subquery is used to identify the duplicated rows (row_num is a 
> BIGSERIAL column).
>

other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) 
are identical)

test=*# select * from dubletten ; c1 | c2 | c3
----+----+----  1 |  1 |  1  1 |  1 |  1  1 |  2 |  3  2 |  3 |  4  3 |  4 |  5  4 |  5 |  5  5 |  5 |  5  5 |  5 |  5
(8 Zeilen)

test=*# with keep as (select max(ctid) as ctid from dubletten group by 
c1,c2,c3) delete from dubletten where ctid not in (select ctid from keep);;
DELETE 2
test=*# select * from dubletten ; c1 | c2 | c3
----+----+----  1 |  1 |  1  1 |  2 |  3  2 |  3 |  4  3 |  4 |  5  4 |  5 |  5  5 |  5 |  5
(6 Zeilen)

test=*#

Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general