Eliminar duplicados por id SQL en todas las tablas

drop_id_duplicates.sql

DO $$
DECLARE
    r record;
BEGIN
    FOR r IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
          -- Omitir las tablas que sean hypertables de Timescale
          AND table_name NOT IN (
              SELECT hypertable_name
              FROM timescaledb_information.hypertables
          )
    LOOP
        -- Verificamos si la tabla tiene la columna 'id'
        IF EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = 'public'
              AND table_name = r.table_name
              AND column_name = 'id'
        ) THEN
            RAISE NOTICE 'Eliminando duplicados en tabla: %', r.table_name;

            -- Ejecutamos la eliminación de duplicados por columna "id"
            EXECUTE format($fmt$
                WITH duplicados AS (
                    SELECT
                        ctid,
                        ROW_NUMBER() OVER (
                            PARTITION BY id
                            ORDER BY ctid
                        ) AS rn
                    FROM %I
                )
                DELETE FROM %I
                USING duplicados
                WHERE %I.ctid = duplicados.ctid
                  AND duplicados.rn > 1
            $fmt$, r.table_name, r.table_name, r.table_name);

        END IF;
    END LOOP;
END$$;

timescale_duplicates.sql

DO $$
DECLARE
    -- Ajusta estos valores según tus necesidades
    v_table_name  text := 'tg_event';
    v_time_column text := 'timestamp';

    v_sql text;
BEGIN
    -- Construimos dinámicamente la consulta DELETE
    v_sql := format($fmt$
        WITH duplicates AS (
            SELECT 
                ctid,
                ROW_NUMBER() OVER (
                    PARTITION BY id, %I  -- Agrupamos por (id, <columna_de_tiempo>)
                    ORDER BY ctid
                ) AS rnum
            FROM %I
        )
        DELETE FROM %I
        USING duplicates
        WHERE %I.ctid = duplicates.ctid
          AND duplicates.rnum > 1;
    $fmt$, 
        v_time_column,      -- %I para la columna de tiempo
        v_table_name,       -- %I para el FROM (tabla origen)
        v_table_name,       -- %I para el DELETE FROM (tabla destino)
        v_table_name        -- %I en el WHERE para comparar ctid
    );

    RAISE NOTICE 'Ejecutando SQL: %', v_sql;
    EXECUTE v_sql;
END;
$$;