Andreas,
On 10/15/2017 11:53 PM, Andreas Kretschmer wrote:
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
I like this solution, but would using a subquery be much slower than the implicit join of `using`? My tables are not big in Postgres-standards, so it's probably not an issue, but I'm trying to learn as much as I can about Postgres now that I'm getting ready to move it to production.
Thanks,
