PostgreSQL - How to delete duplicated rows in a table?
You have a table and want to delete all the duplicated entries on the col2. Given col1 the primary key.
1. Using USING extension:
DELETE FROM table_name USING table_name temp_table
WHERE table_name.col2 = temp_table.col2 AND table_name.col1 < temp_table.col1;
2. SELECT DISTINCT then DROP TABLE
CREATE TABLE temp_table (col1, col2);
INSERT INTO temp_table SELECT DISTINCT * FROM table_name;
DROP TABLE table_name;
ALTER TABLE temp_table RENAME TO table_name;
Need a good GUI Tool for PostgreSQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
