Often in PostgreSQL you may want to find duplicate records in a table.
You can use the following syntax to do so:
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY team ORDER BY athleteID) AS Row
FROM athletes
ORDER BY athleteID
) dup_records
WHERE dup_records.Row > 1;
This particular example will select only the rows that have duplicate values in the team column of the table named athletes.
Here is how this syntax works:
First, the inner SELECT statement selects each column from the original table and adds a new column named Row that contains the count of the value in the team column, saving the result to a table named dup_records.
Then, the outer SELECT statement only selects the rows from the dup_records table where the value in the Row column is greater than 1, i.e. is a duplicate row.
The following example shows how to use this syntax in practice.
Example: How to Find Duplicate Records in a Table 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,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Mavs', 30, 12);
INSERT INTO athletes VALUES (0003, 'Cavs', 12, 3);
INSERT INTO athletes VALUES (0004, 'Heat', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Thunder', 16, 3);
INSERT INTO athletes VALUES (0007, 'Cavs', 10, 5);
INSERT INTO athletes VALUES (0008, 'Lakers', 14, 13);
INSERT INTO athletes VALUES (0009, 'Celtics', 20, 8);
INSERT INTO athletes VALUES (0010, 'Lakers', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Mavs | 30 | 12
3 | Cavs | 12 | 3
4 | Heat | 25 | 9
5 | Mavs | 31 | 14
6 | Thunder | 16 | 3
7 | Cavs | 10 | 5
8 | Lakers | 14 | 13
9 | Celtics | 20 | 8
10 | Lakers | 26 | 3
Suppose that we would like to find all rows in the table that have duplicate values in the team column.
We can use the following syntax to do so:
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY team ORDER BY athleteID) AS Row
FROM athletes
ORDER BY athleteID
) dup_records
WHERE dup_records.Row > 1;
athleteid | team | points | assists | row
-----------+--------+--------+---------+-----
2 | Mavs | 30 | 12 | 2
5 | Mavs | 31 | 14 | 3
7 | Cavs | 10 | 5 | 2
10 | Lakers | 26 | 3 | 2
The resulting table displays all rows from the original table that have duplicate values in the team column.
The row column in this table displays the occurrence of the team name.
For example, we can see:
- The athlete with the athleteID value of 2 has a duplicate value of Mavs in the team column and it is the second occurrence of Mavs in the column.
- The athlete with the athleteID value of 5 has a duplicate value of Mavs in the team column and it is the third occurrence of Mavs in the column.
- The athlete with the athleteID value of 7 has a duplicate value of Cavs in the team column and it is the second occurrence of Cavs in the column.
- The athlete with the athleteID value of 10 has a duplicate value of Lakers in the team column and it is the second occurrence of Lakers in the column.
Note: To find duplicate records based on multiple columns, simply list out multiple column names after the PARTITION BY statement.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows Having Count Greater Than
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String
