¿Cómo elimino registros duplicados en una tabla de unión en PostgreSQL?

Este equipo de trabajo ha pasado mucho tiempo investigando la respuesta a tus interrogantes, te regalamos la soluciones por eso nuestro deseo es resultarte de gran apoyo.

En mi experiencia (y como se muestra en muchas pruebas) NOT IN como lo demuestra @gsiems, es bastante lento y escala terriblemente. el inverso IN suele ser más rápido (donde puede reformular de esa manera, como en este caso), pero esta consulta con EXISTS (hacer exactamente lo que pediste) debería ser mucho más rápido aún, con mesas grandes por órdenes de magnitud:

DELETE FROM questions_tags q
WHERE  EXISTS (
   SELECT FROM questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q1.question_id = q.question_id
   AND    q1.tag_id = q.tag_id
   );

Elimina todas las filas donde otra fila con lo mismo (tag_id, question_id) y un mas pequeño ctid existe. (Efectivamente, mantiene la primera instancia de acuerdo con el orden físico de las tuplas). Usando ctid en ausencia de una mejor alternativa, su tabla no parece tener un PK ni ninguna otra columna (s) única (s).

ctid es el identificador de tupla interno presente en cada renglón y necesariamente único. Otras lecturas:

• ¿Cómo descompongo ctid en números de página y fila?
• ¿Cómo enumerar todas las tablas con cambios de datos en las últimas 24 horas?
• ¿Cómo puedo (o puedo) SELECCIONAR DISTINTO en varias columnas?

Prueba

Ejecuté un caso de prueba con esta tabla que coincidía con su pregunta y 100k filas:

CREATE TABLE questions_tags(
  question_id integer NOT NULL
, tag_id      integer NOT NULL
);

INSERT INTO questions_tags (question_id, tag_id)
SELECT (random()* 100)::int, (random()* 100)::int
FROM   generate_series(1, 100000);

ANALYZE questions_tags;

Los índices no ayudan en este caso.

Resultados

NOT IN

El SQLfiddle se agota.
Intenté lo mismo localmente pero también lo cancelé después de varios minutos.

EXISTS

Termina en medio segundo en este SQLfiddle.

Alternativas

Si vas a borrar la mayoría de las filas, será más rápido seleccionar a los supervivientes en otra mesa, soltar el original y cambiar el nombre de la mesa de supervivientes. Ojo, esto tiene implicaciones si tienes vista o exterior keys (u otras dependencias) definidas en el original.

Si tiene dependencias y desea mantenerlas, podría:

• Deja todo extranjero keys e índices - para el rendimiento.
• SELECT sobrevivientes a una mesa temporal.
• TRUNCATE el original.
• Re-INSERT sobrevivientes
• Re-CREATE índices y extranjeros keys. Las vistas pueden quedarse, no tienen ningún impacto en el rendimiento. Más aquí o aquí.

Puede usar el ctid para lograr eso. Por ejemplo:

Crea una tabla con duplicados:

=# create table foo (id1 integer, id2 integer);
CREATE TABLE

=# insert into foo values (1,1), (1, 2), (1, 2), (1, 3);
INSERT 0 4

=# select * from foo;
 id1 | id2 
-----+-----
   1 |   1
   1 |   2
   1 |   2
   1 |   3
(4 rows)

Seleccione los datos duplicados:

=# select foo.ctid, foo.id1, foo.id2, foo2.min_ctid
-#  from foo
-#  join (
-#      select id1, id2, min(ctid) as min_ctid 
-#          from foo 
-#          group by id1, id2 
-#          having count (*) > 1
-#      ) foo2 
-#      on foo.id1 = foo2.id1 and foo.id2 = foo2.id2
-#  where foo.ctid <> foo2.min_ctid ;
 ctid  | id1 | id2 | min_ctid 
-------+-----+-----+----------
 (0,3) |   1 |   2 | (0,2)
(1 row)

Eliminar los datos duplicados:

=# delete from foo
-# where ctid not in (select min (ctid) as min_ctid from foo group by id1, id2);
DELETE 1

=# select * from foo;
 id1 | id2 
-----+-----
   1 |   1
   1 |   2
   1 |   3
(3 rows)

En tu caso, lo siguiente debería funcionar:

delete from questions_tags
    where ctid not in (
        select min (ctid) as min_ctid 
            from questions_tags 
            group by question_id, tag_id
        );

Valoraciones y comentarios

---

---

Utiliza Nuestro Buscador