Quando uma tabela do banco de dados Postgresql possui registros duplicados é
possível identificar utilizando uma "Window Function" que vai gerar um novo
valor sequencial baseado nos critérios que forem definidos pelo usuário.
Para deixar mais claro vou exemplificar com uma tabela bem simples:
select orb_pto, ano, lim_ndvi, lim_nbrfrom tabela_limiares tl order by orb_pto, ano; orb_pto ano lim_ndvi lim_nbr 216_065 2018 0.2 0.5 216_065 2018 0.2 0.5 216_065 2019 0.2 0.5 216_065 2019 0.2 0.5 216_066 2018 0.2 0.5 216_066 2018 0.2 0.5
É possível notar que existem linhas duplicadas porém difícil de identificar.
Utilizando um comando para criar um número sequencial baseado em uma condição é possível melhorar esta visualização
select orb_pto, ano, lim_ndvi, lim_nbr, row_number() over( partition by orb_pto, ano order by 1,2 ) as num_lin from tabela_limiares tl; orb_pto ano lim_ndvi lim_nbr num_lin 216_065 2018 0.2 0.5 1 216_065 2018 0.2 0.5 2 216_065 2019 0.2 0.5 1 216_065 2019 0.2 0.5 2 216_066 2018 0.2 0.5 1 216_066 2018 0.2 0.5 2
Note que sempre que o num_lin for igual a 2 representa que aquela tupla considerando orb_pto e ano estão duplicadas. Porém antes de apagar esta linha quero me certificar que as quatro condições são únicas, e por isso vou fazer a consulta um pouco mais refinada:
with duplicados as ( select orb_pto, ano, lim_ndvi, lim_nbr, row_number() over( partition by orb_pto, ano, lim_ndvi, lim_nbr order by 1,2 ) as num_lin from tabela_limiares tl ) select * from duplicados where duplicados.num_lin =2 ; orb_pto ano lim_ndvi lim_nbr num_lin 216_065 2018 0.2 0.5 2 216_065 2019 0.2 0.5 2 216_066 2018 0.2 0.5 2 216_066 2019 0.2 0.5 2 216_067 2018 0.2 0.5 2 217_063 2019 0.2 0.5 2
Pronto agora eu posso apagar ou inserir em outra tabela conforme meu interesse
with duplicados as ( select *, row_number() over( partition by orb_pto, ano order by 1,2 ) as num_lin from tabela_limiares tl )insert into aq30m_limiar (orbita_ponto, lim_ndvi, lim_nbr, ano) (select orb_pto, lim_ndvi, lim_nbr, ano from duplicados where duplicados.num_lin =1 );CUIDADO COM O DELETE!!!!
A inspiração para este post veio do vídeo https://www.scalingpostgres.com/episodes/225-psql-gexec-delete-duplicates-postgres-podcast-puny-powerful/
