Re: Delete Duplicates with Using

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 <http://lucee.org/>