We’ve had some serious hassle with duplicate row issues on some of our PostgreSQL databases.
While simply deleting the rows worked for most of the issues, some of them were just unable to delete. Trying to delete or update them resulted in the curious error message: Postgres ERROR: tuple already updated by self on deleting duplicate rows.
The only way for us to delete these row was to find differences between the rows. Unfortunately, they were completely identical, including oid values. After some search on the internet, we found a solution:
SELECT ctid, *
ctid here holds the block and item number of the file on the hard disk, which contains the table row. This id was different for both of the rows opposed to all the other fields. Thus we were able to delete the row:
DELETE FROM TABLE
WHERE id = 123
AND ctid = '(2134,1234)'
We hope this blog article can help out people who are having the same hard time as we had.
Author: Alexander Schöcke