PostgreSQL如何删除重复的值 [英] PostgreSQL how to delete duplicated values

问题描述

我在Postgres数据库中有一个表，忘记了插入唯一索引.由于该索引，我现在有重复的值.如何删除重复的值?我想在字段translationset_Id和键上添加唯一索引.

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.

推荐答案

似乎您只想删除与translationset_id列重复的记录.在这种情况下，我们可以使用Postgres的行号功能来区分重复的行，然后删除那些重复的行.

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)

这篇关于PostgreSQL如何删除重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！