Cecil Boye Cecil Boye - 14 days ago 8
SQL Question

PostgresSQL how to delete duplicated values

I have a table in my Postgres database where i forgot to insert a unique index. because of that index that i have now duplicated values. How to remove the duplicated values? I want to add a unique index on the fields translationset_Id and key.

enter image description here

Answer

It appears that you only want to delete records which are duplicate with regard to the translationset_id column. In this case, we can use Postgres' row number functionality to discern between duplicate rows, and then to delete those duplicates.

WITH cte AS
(
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY translationset_id, key) AS rnum
    FROM yourTable t
)

DELETE FROM yourTable
WHERE translationset_id IN (SELECT translationset_id FROM cte WHERE rnum > 1)
Comments