更新时间:2023-01-29 19:01:21
在此添加不会重新创建表的解决方案。这是因为重新创建表可能会中断大量现有配置和历史记录。
相反,我们将只删除重复的行,并在事务中插入每个行的单个副本:
-- find all duplicates
create or replace transient table duplicate_holder as (
select $1, $2, $3
from some_table
group by 1,2,3
having count(*)>1
);
-- time to use a transaction to insert and delete
begin transaction;
-- delete duplicates
delete from some_table a
using duplicate_holder b
where (a.$1,a.$2,a.$3)=(b.$1,b.$2,b.$3);
-- insert single copy
insert into some_table
select *
from duplicate_holder;
-- we are done
commit;
优点: