Re: Delete Duplicates with Using

["Igal @ Lucee.org" <igal@xxxxxxxxx>]

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,

Igal Sapir
Lucee Core Developer
Lucee.org