Here are seven ways to return duplicate rows in PostgreSQL when those rows have a primary key or other unique identifier column.

This means that the duplicate rows share exactly the same values across all columns with the exception of their primary key/unique ID column.

Sample Data

We’ll use the following data for our examples:

SELECT * FROM Dogs;

Result:

 dogid | firstname | lastname 
-------+-----------+----------
     1 | Bark      | Smith
     2 | Bark      | Smith
     3 | Woof      | Jones
     4 | Ruff      | Robinson
     5 | Wag       | Johnson
     6 | Wag       | Johnson
     7 | Wag       | Johnson

The first two rows are duplicates (except for the DogId column, which is the table’s primary key, and contains a unique value across all rows). The last three rows are also duplicates (except for the DogId column).

The primary key column ensures that there are no duplicate rows, which is good practice in RDBMSs, because primary keys help to enforce data integrity. But because primary keys prevent duplicate rows, they have the potential to interfere with our ability to find duplicates.

In our table above, the primary key column is an incrementing number, and its value carries no meaning and is not significant. We therefore need to ignore that row if we want to find duplicates in the other columns.

Option 1

We can use the SQL GROUP BY clause to group the columns by their significant columns, then use the COUNT() function to return the number of identical rows:

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) AS Count
FROM Dogs
GROUP BY FirstName, LastName;

Result:

 firstname | lastname | count 
-----------+----------+-------
 Ruff      | Robinson |     1
 Wag       | Johnson  |     3
 Woof      | Jones    |     1
 Bark      | Smith    |     2

Here we excluded the primary key column by omitting it from our query.

The result tells us that there are three rows containing Wag Johnson and two rows containing Bark Smith. These are duplicates (or triplicates in the case of Wag Johnson). The other two rows do not have any duplicates.

Option 2

We can exclude non-duplicates from the output with the HAVING clause:

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) AS Count
FROM Dogs
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;

Result:

 firstname | lastname | count 
-----------+----------+-------
 Wag       | Johnson  |     3
 Bark      | Smith    |     2

Option 3

Here’s an example of checking for duplicates on concatenated columns. In this case we use the CONCAT() function to concatenate our two columns, use the DISTINCT keyword to get distinct values, then use the COUNT() function to return the count:

SELECT
    DISTINCT CONCAT(FirstName, ' ', LastName) AS DogName,
    COUNT(*) AS Count
FROM Dogs
GROUP BY CONCAT(FirstName, ' ', LastName);

Result:

    dogname    | count 
---------------+-------
 Wag Johnson   |     3
 Ruff Robinson |     1
 Woof Jones    |     1
 Bark Smith    |     2

Option 4

We can alternatively use the ROW_NUMBER() window function:

SELECT 
    *,
    ROW_NUMBER() OVER ( 
        PARTITION BY FirstName, LastName 
        ORDER BY FirstName, LastName
        ) AS Row_Number
FROM Dogs;

Result:

 dogid | firstname | lastname | row_number 
-------+-----------+----------+------------
     1 | Bark      | Smith    |          1
     2 | Bark      | Smith    |          2
     4 | Ruff      | Robinson |          1
     5 | Wag       | Johnson  |          1
     6 | Wag       | Johnson  |          2
     7 | Wag       | Johnson  |          3
     3 | Woof      | Jones    |          1

Using the PARTITION BY clause results in a new column being added, with a row number that increments each time there’s a duplicate, but resets again when there’s a unique row.

In this case we don’t group the results, which means we can see each duplicate row, including its unique identifier column.

Option 5

We can also use the previous example as a common table expression in a larger query:

WITH cte AS 
    (
        SELECT 
            *,
            ROW_NUMBER() OVER ( 
                PARTITION BY FirstName, LastName 
                ORDER BY FirstName, LastName
                ) AS Row_Number
        FROM Dogs
    )
SELECT * FROM cte WHERE Row_Number <> 1;

Result:

 dogid | firstname | lastname | row_number 
-------+-----------+----------+------------
     2 | Bark      | Smith    |          2
     6 | Wag       | Johnson  |          2
     7 | Wag       | Johnson  |          3

This excludes non-duplicates from the output, and it excludes one row of each duplicate from the output. In other words, it only shows the excess rows from the duplicates. These rows are prime candidates for being deleted in a de-duping operation.

Option 6

Here’s a more concise way to get the same output as the previous example:

SELECT * FROM Dogs 
WHERE DogId IN (
    SELECT DogId FROM Dogs 
    EXCEPT SELECT MIN(DogId) FROM Dogs 
    GROUP BY FirstName, LastName
    );

Result:

 dogid | firstname | lastname 
-------+-----------+----------
     6 | Wag       | Johnson
     2 | Bark      | Smith
     7 | Wag       | Johnson

One difference between this example and the previous one is that this example doesn’t require generating our own separate row number.

Option 7

Here’s yet another option for returning duplicate rows in Postgres:

SELECT * 
FROM Dogs d1, Dogs d2 
WHERE d1.FirstName = d2.FirstName 
AND d1.LastName = d2.LastName
AND d1.DogId <> d2.DogId 
AND d1.DogId = (
    SELECT MAX(DogId) 
    FROM Dogs d3 
    WHERE d3.FirstName = d1.FirstName 
    AND d3.LastName = d1.LastName
);

Result:

 dogid | firstname | lastname | dogid | firstname | lastname 
-------+-----------+----------+-------+-----------+----------
     2 | Bark      | Smith    |     1 | Bark      | Smith
     7 | Wag       | Johnson  |     5 | Wag       | Johnson
     7 | Wag       | Johnson  |     6 | Wag       | Johnson