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
address. You want to delete all the rows that have duplicated name and address. Your
people table currently looks like this.
So your desired outcome is simply this.
This is what it means by removing duplicate rows.
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);