mostrar - sql eliminar registros repetidos dejando solo 1

Eliminar registros duplicados en PostgreSQL

Question

Tengo una tabla en una base de datos PostgreSQL 8.3.8, que no tiene claves / restricciones, y tiene varias filas con exactamente los mismos valores.

Me gustaría eliminar todos los duplicados y guardar solo 1 copia de cada fila.

Hay una columna en particular (llamada "clave") que se puede usar para identificar duplicados (es decir, solo debe existir una entrada para cada "clave" distinta).

¿Cómo puedo hacer esto? (idealmente con un solo comando SQL) La velocidad no es un problema en este caso (solo hay unas pocas filas).

Answer 1

Esto es rápido y conciso

DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid  -- delete the older versions
    AND T1.key  = T2.key;  -- add more columns if needed

Consulte también mi respuesta en Cómo eliminar filas duplicadas sin un identificador único que incluye más información.

Answer 2

Esto funcionó bien para mí. Tenía una tabla, términos, que contenía valores duplicados. Se ejecutó una consulta para completar una tabla temporal con todas las filas duplicadas. Luego ejecuté una declaración de eliminación con esos identificadores en la tabla temporal. valor es la columna que contenía los duplicados.

        CREATE TEMP TABLE dupids AS
        select id from (
                    select value, id, row_number() 
over (partition by value order by value) 
    as rownum from terms
                  ) tmp
                  where rownum >= 2;

delete from [table] where id in (select id from dupids)

Answer 3

Intenté esto:

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

provisto por la wiki de Postgres:

https://wiki.postgresql.org/wiki/Deleting_duplicates

Answer 4

Tuve que crear mi propia versión. La versión escrita por @a_horse_with_no_name es demasiado lenta en mi tabla (21M filas). Y @rapimo simplemente no elimina dups.

Esto es lo que uso en PostgreSQL 9.5

DELETE FROM your_table
WHERE ctid IN (
  SELECT unnest(array_remove(all_ctids, actid))
  FROM (
         SELECT
           min(b.ctid)     AS actid,
           array_agg(ctid) AS all_ctids
         FROM your_table b
         GROUP BY key1, key2, key3, key4
         HAVING count(*) > 1) c);

Answer 5

Una solución más rápida es

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

Answer 6

Yo usaría una tabla temporal:

create table tab_temp as
select distinct f1, f2, f3, fn
  from tab;

A continuación, elimine la tab y cambie el nombre tab_temp en la tab .

Answer 7

FUNCIONA PARA NORMAL SQL Y POSTGRESQL (TAMBIÉN FUNCIONA EN AWS ​​REDSHIFT)

DROP TABLE IF EXISTS backupOfTheTableContainingDuplicates;

CREATE TABLE aNewEmptyTemporaryOrBackupTable 
AS SELECT DISTINCT * FROM originalTableContainingDuplicates;

TRUNCATE TABLE originalTableContainingDuplicates;

INSERT INTO originalTableContainingDuplicates SELECT * FROM 
aNewEmptyTemporaryOrBackupTable ;

DROP TABLE aNewEmptyTemporaryOrBackupTable ;

EXPLICACIÓN DEL ANÁLISIS SQL ESCRITO

Asi que,

La primera consulta asegura que, si tiene una copia de seguridad / tabla temporal de la tabla original que contiene duplicados, primero suelte esa tabla.

La segunda consulta crea una nueva tabla de tabla (temporal / de respaldo) con entradas únicas en la tabla original que contiene duplicados, por lo que la nueva tabla temporal es igual que la tabla original. MINIMA las entradas duplicadas.

La tercera consulta trunca o vacía la tabla original.

la cuarta consulta, inserta o copia todas las entradas únicas en la tabla temporal a la tabla original que se ha truncado recientemente (por lo que no tiene datos). Después de que se ejecute esta consulta, la tabla original se rellenará con datos ÚNICOS que estaban en la tabla temporal.

La quinta consulta, elimina / elimina la tabla temporal innecesaria.

Por lo tanto, el resultado final es que la tabla original solo tiene ENTRADAS ÚNICAS y no hay duplicados.

Answer 8

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);