Remove only duplicates from specific Postgresql table

Question

I have a table which contains duplicates and I would like to keep only one row for each duplicates.

I can select duplicates with my SQL command :

SELECT DISTINCT ON (email, first_name, last_name) * from customer;

But I would like to use DELETE with my previous command.

This command should work right ?

DELETE FROM customer WHERE customer.id NOT IN 
    (SELECT id FROM 
        (SELECT DISTINCT ON (email, first_name, last_name) * from customer));

Is it true ?

kkica · Accepted Answer · 2019-01-18 16:09:59Z

2

I guess you have a id field.

delete from customer 
where id not in (
    select min(id)
    from customer
    group by email, first_name, last_name
)

The subquery finds the id of the rows you want to keep. Then you delete the other rows

answered Jan 18, 2019 at 16:09

kkica

4,1041 gold badge22 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yes it works fine ! My query didn't work because I need to add an alias I assume. Thank you anyway ! :)

Drinkwater · Accepted Answer · 2019-01-18 16:22:53Z

1

I can't find your ID in (SELECT DISTINCT ON (email, first_name, last_name) * from customer));
The distinct on only return the first row of the duplication data that is unpredictable

answered Jan 18, 2019 at 16:22

Drinkwater

231 gold badge1 silver badge6 bronze badges