postgresql delete duplicated rows

最新推荐文章于 2024-11-13 15:21:08 发布

weixin_33755554

最新推荐文章于 2024-11-13 15:21:08 发布

阅读量126

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://segmentfault.com/a/1190000006966930

本文介绍了一种使用PostgreSQL中的窗口函数row_number()来识别并删除数据表中的重复记录的方法。通过创建临时表并利用row_number()函数对特定字段进行分区排序，可以有效地找出并移除重复的数据行。

Deleting duplicates

使用window function row_number 对数据partition

举例：

isnp=# create table student (id serial, name text, age int);
isnp=# \d student;
isnp=# with cte as (select *, round(random()*100) as d from generate_series(1,10000) as r) insert into student (name, age)  select 'lmy'||r, d from cte;
### genetate duplicate row
isnp=# insert into student (name, age) select name, age from student where 100<id and id<200; 
isnp=# with xxx (id, name, age, rn)  as (select *, row_number() over(partition by name order by id) rn from student) delete from student where id in (select id from xxx where rn > 1);