Removing duplicate rows in PostgreSQL

After some time of absence due to a high workload I finally got around to write more on an issue, which has been on my mind for quite a while: Removing duplicate rows.

It happens from time to time that PostgreSQL support clients accidentally load data twice or somehow manage to duplicate data (human error, bugs in applications, etc). The main question is, how can the problem be fixed? How can developers handle duplicate data and cleanup things? This post tries to answer exactly that question.

Detecting and finding duplicate entries

To demonstrate how duplicates can be found, I have prepared some sample data:

CREATE TABLE t_location
(
     country     text,
     city        text
);

The idea is to store a couple of German cities:

COPY t_location FROM stdin DELIMITER ',';
Germany,Berlin
Germany,Frankfurt
Germany,Frankfurt
Germany,Dortmund
Germany,Dortmund
Germany,Dresden
\.

Obviously some cities show up more than once:

test=# SELECT country, city, count(*)
       FROM   t_location
       GROUP BY 1, 2;

 country |   city    | count
---------+-----------+-------
 Germany | Dresden   |     1
 Germany | Dortmund  |     2
 Germany | Berlin    |     1
 Germany | Frankfurt |     2
(4 rows)

“Dortmund” and “Frankfurt” are the ones, which have been added more than once and need a proper cleanup.

Identifying rows

Before we can approach the problem head on I want to tell you about a thing called “ctid”, which is used in PostgreSQL to identify the row in a table:

test=# SELECT ctid, * FROM t_location;
 ctid  | country |   city
-------+---------+-----------
 (0,1) | Germany | Berlin
 (0,2) | Germany | Frankfurt
 (0,3) | Germany | Frankfurt
 (0,4) | Germany | Dortmund
 (0,5) | Germany | Dortmund
 (0,6) | Germany | Dresden
(6 rows)

(0, 1) means that we are looking at the first block and the first row. (0, 2) is the second row in the first 8k block and so on. The trouble is: There is no such thing as “DELETE … LIMIT 1”. Therefore we have to find a way to use the “hidden“ columns to solve the problem. The ctid gives us a free identifier, which can help to delete just a single row.

Here is a query, which returns the first incarnation of a row:

test=# SELECT  t_location, min(ctid)
       FROM    t_location
       GROUP BY 1
       HAVING count(*) > 1;

     t_location      |  min
---------------------+-------
 (Germany,Dortmund)  | (0,4)
 (Germany,Frankfurt) | (0,2)
(2 rows)

Now that we have identified all the duplicate rows in the table, it is possible to write a DELETE statement, which just kills the undesired data:

WITH x AS (SELECT      t_location dup, min(ctid)
           FROM        t_location
           GROUP BY 1
           HAVING count(*) > 1
)
DELETE FROM    t_location
USING     x
WHERE     (t_location) = (dup)
          AND t_location.ctid <> x.min
RETURNING *;

 country |   city    |         dup         |  min
---------+-----------+---------------------+-------
 Germany | Dortmund  | (Germany,Dortmund)  | (0,4)
 Germany | Frankfurt | (Germany,Frankfurt) | (0,2)
(2 rows)

As you can see the two rows we don’t want any more are removed from the table. Mind that the ctid is not a static thing. UPDATE statements, VACUUM FULL, etc. can actually change it. Therefore it is highly recommended to perform this operation only if you are the only one on the system and if the table is locked appropriately.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.