Delete Duplicate Rows

Note: If this doesn’t meet what you’re looking for in terms of deleting duplicate data rows, leave a comment below and I’ll look into it. Alternatively, you can go back to the index page of SQL queries.

Sometimes, you want to delete duplicate rows within a data table. Let’s use an example.

You have a people table and it has id, name, and address. You want to delete all the rows that have duplicated name and address. Your people table currently looks like this.

idnameaddress
1AnnaHighland Street
2BobPickering Avenue
3BobSesame Road
4AnnaHighland Street
5AnnaHighland Street
6BobPickering Avenue

So your desired outcome is simply this.

idnameaddress
1AnnaHighland Street
2BobPickering Avenue
3BobSesame Road

This is what it means by removing duplicate rows.

PostgreSQL

To do this in Postgres, the easiest way is to use Common Table Expressions. Your delete query should look like this.

WITH unique_people AS
    (SELECT DISTINCT ON ("name", "address") * FROM people)
DELETE FROM people WHERE people.id NOT IN (SELECT id FROM unique_people);