Expurgando / Deletando linhas duplicadas no PostgreSQL

query para limpar linhas 100% duplicadas em tabelas

Galera, post bem rapidinho sobre um caso que aconteceu essa semana para expurgo de uma tabela.

O cliente tinha uma tabela sem CONSTRAINT e que continha diversas linhas duplicadas, nesse caso eu poderia fazer um rank usando ROW_NUMBER() para pegar os dados, copiar para outra tabela e depois renomear em um horário de manutenção, mas achei essa solução muito feia. Logo, lembrei do ctid (current tupple identifier ) que é uma coluna interna do postgres que identifica fisicamente aonde cada tupla está no disco ( pagina, posição)

-- exemplo 
select ctid,* from tabela;

-- Saida:
--
-- ctid | col1 | col2
-- (0,1)|'oi'  | 123
-- (0,2)|'alo' | 345

Então usei a mesma lógica para criar essa query que rankeia a linha e mantém sempre 1 registro ( provavelmente o mais antigo )

DELETE FROM tb_name
WHERE ctid IN (
    SELECT ctid
    FROM (
        SELECT ctid,
               ROW_NUMBER() OVER (
                   PARTITION BY col1,col2 -- pode botar quantas preciso
                   ORDER BY ctid
               ) rn
        FROM tb_name
    ) t
    WHERE rn > 1
);

Podemos ir um pouco além e criar uma procedure para agendar via cron

-- DROP PROCEDURE public.cleanup_table();

CREATE OR REPLACE PROCEDURE hml.cleanup_morpheus_people(IN tb_name TEXT)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_table_name    text := tb_name;

    v_start_time    timestamptz := clock_timestamp();
    v_end_time      timestamptz;

    v_rows_before   bigint := 0;
    v_rows_after    bigint := 0;
    v_rows_deleted  bigint := 0;

    v_error_message text;
    v_sql           text;
BEGIN
    ----------------------------------------------------------------
    -- COUNT BEFORE
    ----------------------------------------------------------------
    EXECUTE format('SELECT count(*) FROM %s', v_table_name)
    INTO v_rows_before;

----------------------------------------------------------------
-- DELETE DUPLICADOS )
----------------------------------------------------------------
    v_sql := format($SQL$
         DELETE FROM %s
        WHERE ctid IN (
            SELECT ctid
            FROM (
                SELECT ctid,
                   ROW_NUMBER() OVER (
                       PARTITION BY col1,col2 -- pode botar quantas preciso
                       ORDER BY ctid
                   ) AS rn
                FROM %s
            ) t
            WHERE rn > 1
        )
    $SQL$, v_table_name, v_table_name);

    EXECUTE v_sql;
    GET DIAGNOSTICS v_rows_deleted = ROW_COUNT;

    EXECUTE format('SELECT count(*) FROM %s', v_table_name)
    INTO v_rows_after;

    v_end_time := clock_timestamp();

    ----------------------------------------------------------------
    -- SUCCESS LOG
    ----------------------------------------------------------------
    INSERT INTO public.cleanup_table_log (
        table_name,
        executed_by,
        start_time,
        end_time,
        duration,
        rows_before,
        rows_deleted,
        rows_after,
        status,
        error_message,
        notes
    )
    VALUES (
        v_table_name,
        current_user,
        v_start_time,
        v_end_time,
        v_end_time - v_start_time,
        v_rows_before,
        v_rows_deleted,
        v_rows_after,
        'SUCCESS',
        NULL,
        'limpando linhas duplicadas'
    );

EXCEPTION
    WHEN OTHERS THEN
        v_end_time := clock_timestamp();
        v_error_message := SQLERRM;

        ----------------------------------------------------------------
        -- FAIL LOG (ISOLADO)
        ----------------------------------------------------------------
        BEGIN
            INSERT INTO hml.cleanup_morpheus_log (
                table_name,
                executed_by,
                start_time,
                end_time,
                duration,
                rows_before,
                rows_deleted,
                rows_after,
                status,
                error_message,
                notes
            )
            VALUES (
                v_table_name,
                current_user,
                v_start_time,
                v_end_time,
                v_end_time - v_start_time,
                COALESCE(v_rows_before, 0),
                0,
                NULL,
                'FAIL',
                v_error_message,
                'Erro durante cleanup'
            );
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Falha ao registrar log de erro: %', SQLERRM;
        END;

        RETURN;
END;
$procedure$
;

É isso pessoal, até a próxima 👋