PostgreSQL: How to Count Duplicate Rows in Table

Often in PostgreSQL you may want to count the number of duplicate rows in a table.

You can use the following methods to do so:

Method 1: Count Duplicate Rows by Group

SELECT team,
       COUNT(*) -1 AS duplicate_count
FROM athletes
GROUP BY 1;

This particular example will count the number of duplicate rows for each unique value in the team column of the athletes table.

Method 2: Count Total Duplicate Rows in Table

SELECT COUNT(*) FROM (
  SELECT *,
  ROW_NUMBER() OVER (PARTITION BY team) AS Row
  FROM athletes
) dup_records
WHERE dup_records.Row > 1;

This particular example will return the total count of duplicate rows (based on duplicate values in the team column) in the athletes table.

The following example shows how to use each of these methods in practice.

Example: How to Find Duplicate Rows 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 (
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES ('Mavs', 22);
INSERT INTO athletes VALUES ('Mavs', 30);
INSERT INTO athletes VALUES ('Cavs', 12);
INSERT INTO athletes VALUES ('Heat', 25);
INSERT INTO athletes VALUES ('Mavs', 31);
INSERT INTO athletes VALUES ('Thunder', 16);
INSERT INTO athletes VALUES ('Cavs', 10);
INSERT INTO athletes VALUES ('Lakers', 14);
INSERT INTO athletes VALUES ('Celtics', 20);
INSERT INTO athletes VALUES ('Lakers', 26);

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

  team   | points 
---------+--------
 Mavs    |     22
 Mavs    |     30
 Cavs    |     12
 Heat    |     25
 Mavs    |     31
 Thunder |     16
 Cavs    |     10
 Lakers  |     14
 Celtics |     20
 Lakers  |     26

We can use the following syntax to count the number of times that each value in the team column has a duplicate row:

SELECT team,
       COUNT(*) -1 AS duplicate_count
FROM athletes
GROUP BY 1;

  team   | duplicate_count 
---------+-----------------
 Heat    |               0
 Lakers  |               1
 Mavs    |               2
 Celtics |               0
 Thunder |               0
 Cavs    |               1

The resulting table displays the number of duplicate rows for each team.

For example, we can see:

• The team name Heat has 0 duplicate rows.
• The team name Lakers has 1 duplicate row.
• The team name Mavs has 2 duplicate rows.

And so on.

We can also use the following syntax to count the total number of duplicate rows in the table:

SELECT COUNT(*) FROM (
  SELECT *,
  ROW_NUMBER() OVER (PARTITION BY team) AS Row
  FROM athletes
) dup_records
WHERE dup_records.Row > 1;

 count 
-------
     4

From the output we can see that there are 4 duplicate rows (based on duplicate values in the team column) in the table.

You can use similar syntax to count duplicate rows in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Find Duplicate Records in a Table
PostgreSQL: How to Remove Duplicate Values from Array
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: How to Select Rows Having Count Greater Than