PostgreSQL: Remove Duplicate Rows Based on Two Columns

Often in PostgreSQL you may want to remove duplicate rows from a table based on duplicate values in two columns.

You can use the following syntax to do so:

DELETE FROM athletes T1
    USING   athletes T2
WHERE   T1.athleteID < T2.athleteID 
    AND T1.team = T2.team 
    AND T1.position  = T2.position;

This particular example will delete all rows from the athletes table that have duplicate values in both the team and position columns.

The following example shows how to use this syntax in practice.

Example: Remove Duplicate Rows Based on Two Columns in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  position TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Center', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Center', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Forward', 12);

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | position | points 
-----------+-------+----------+--------
         1 | Mavs  | Guard    |     15
         2 | Mavs  | Guard    |     12
         3 | Mavs  | Forward  |     23
         4 | Mavs  | Center   |     40
         5 | Hawks | Guard    |     29
         6 | Hawks | Center   |     14
         7 | Hawks | Forward  |     33
         8 | Hawks | Forward  |     12

Suppose that we would like to remove all rows that have duplicate values in both the team and position columns.

We can use the following syntax to do so:

-- delete rows with duplicate values in team and position columns
DELETE FROM athletes T1
    USING   athletes T2
WHERE   T1.athleteID < T2.athleteID 
    AND T1.team = T2.team 
    AND T1.position  = T2.position;

-- view updated table
SELECT * FROM athletes;

 athleteid | team  | position | points 
-----------+-------+----------+--------
         2 | Mavs  | Guard    |     12
         3 | Mavs  | Forward  |     23
         4 | Mavs  | Center   |     40
         5 | Hawks | Guard    |     29
         6 | Hawks | Center   |     14
         8 | Hawks | Forward  |     12

When we view the updated table we can see that each row with duplicate values in the team and position columns has been removed.

Feel free to use similar syntax to remove duplicate rows based on two columns in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Count Duplicate Rows in Table
PostgreSQL: How to Find Duplicate Records in a Table
PostgreSQL: How to Remove Duplicate Values from Array