Removendo registros duplicados de uma tabela no PostgreSQL

Contexto

Vamos lá.. você tem uma tabela no seu banco de dados chamada eventos com três campos: codigo, codigo_origem, tipo e timestamp. A solução foi para produção, tá rodando há uns dias e, quando você percebe existem várias entradas duplicadas no seu banco de dados pois o sistema que gera os eventos não possui garantia de exactly-once (vários não possuem) e você não tem controle sobre ele. E agora?

Unique Keys!

Uma alternativa relativamente simples para resolver o problema é usar as já conhecidas unique keys. Neste caso iremos considerar que o campo codigo_origem deve ser único dentro da tabela, então a solução é relativamente simples

alter table if exists eventos add unique (codigo_origem);

Aí você conecta na base, roda o comando e:

could not create unique index \"evento_codigo_origem_key\"
  Detail: Key (codigo_origem)=(123) is duplicated

Percebeu o problema? Como é que você vai inserir uma constraint em uma tabela que viola essa constraint?

Resposta: deletando os registros duplicados!

Existem várias formas de fazer e algumas delas incluem fazer join da tabela com ela mesma, mas se a sua tabela já tiver uma certa quantidade de registros, isso pode ser beeeem lento.

Solução: funções de array ao resgate

Vou colocar a query aqui pra turma do TLDR, mas prometo que explico

select unnest((array_agg(e.id order by created_at asc))[2:]) as id 
from eventos e 
group by e.codigo_origem 
having count(1) > 1

O que essa query vai fazer é, basicamente, selecionar todos os ids duplicados pra você removê-los usando um delete simples e ela funciona dessa forma:

1. Agrupa os registros pelo campo codigo_origem
2. Seleciona os registros duplicados (having count (1) > 1)
3. Usa a função array_agg pra gerar um agregado dos ids dos registros
4. Usa o [2:] pra selecionar todo mundo do índice 2 pra frente
5. Usa o unnest pra "explodir" o array em várias linhas

Como eu falei antes, o resultado final dessa consulta será uma lista dos códigos que você deve remover. Se você quiser, pode até fazer assim

delete from eventos where id in (
  select unnest((array_agg(e.id order by created_at asc)[2:]) as id 
  from eventos e 
  group by e.codigo_origem 
  having count(1) > 1
)