PostgreSQL如何删除重复的值
PostgreSQL如何删除重复的值
提问于 2016-11-21 18:01:21
我的Postgres数据库中有一个我忘记插入唯一索引的表。因为我现在有了重复值的索引。如何去除重复的值？我想在字段translationset_Id和key上添加一个唯一的索引。
回答 4
Stack Overflow用户
回答已采纳
发布于 2016-11-21 18:08:25
您似乎只想删除关于translationset_id列的重复记录。在这种情况下，我们可以使用Postgres的行号功能来识别重复行，然后删除这些重复行。
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)Stack Overflow用户
发布于 2016-11-21 18:06:28
我想你是在问这个：
DELETE FROM tablename WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum FROM tablename) t WHERE t.rnum > 1);
Stack Overflow用户
发布于 2016-11-21 18:09:20
delete from mytable
where exists (select 1
from mytable t2
where t2.name = mytable.name and
t2.address = mytable.address and
t2.zip = mytable.zip and
t2.ctid > mytable.ctid
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40717426
复制相关文章
相似问题
腾讯云开发者
