Tengo que añadir una única restricción a una tabla existente. Esto está muy bien, salvo que la tabla tiene millones de filas ya, y muchas de las filas de violar la restricción única necesito agregar.

¿Cuál es el enfoque más rápido para la eliminación de los infractores filas? Tengo una instrucción SQL que se encuentra la de los duplicados y los elimina, pero es mucho tiempo para ejecutar. Hay otra manera de resolver este problema? Tal vez la copia de seguridad de la tabla, a continuación, restaura después de que se agrega la restricción?

InformationsquelleAutor gjrwebber | 2009-11-17

16 Comentarios

  1. 101

    Por ejemplo, usted podría:

    CREATE TABLE tmp ...
    INSERT INTO tmp SELECT DISTINCT * FROM t;
    DROP TABLE t;
    ALTER TABLE tmp RENAME TO t;
    • Puedes hacer diferentes para el grupo de columnas. Tal vez «SELECT DISTINCT (t.a, t.b, t.c), * DE t»?
    • DISTINTAS EN (a, b, c): postgresql.org/docs/8.2/interactive/sql-select.html
    • Hizo el truco. Gracias.
    • más fácil tipo: CREATE TABLE tmp AS SELECT ...;. Entonces usted no necesita siquiera imaginar lo que el diseño de tmp es. 🙂
    • +1 – Gracias! Esto sólo me ha ayudado un montón 🙂
    • Esta respuesta no es realmente muy bueno por varias razones. @Randal nombrado uno. En la mayoría de los casos, especialmente si usted tiene función de los objetos, como índices, restricciones, vistas, etc., el enfoque superior es el uso de un real TABLA TEMPORAL, TRUNCAR el original y vuelva a insertar los datos.
    • la pregunta por el enfoque más rápido. la importación masiva de datos en una tabla con los índices y restricciones se va a llevar años. el manual de PostgreSQL en realidad, recomienda la eliminación de los índices y claves foráneas: postgresql.org/docs/9.1/static/populate.html. yo diría que su downvote está completamente fuera de la marca.
    • Tienes razón acerca de los índices. Caer & recreando es mucho más rápido. Pero otros en función de los objetos va a romper o evitar que se caiga de la mesa por completo – que el OP se enterara de después de haber hecho la copia de mucho para el «enfoque más rápido». Aún así, tienes razón acerca de la downvote. Es infundada, ya que no es una mala respuesta. Esto no es sólo que, bueno. Usted podría haber añadido algunos consejos acerca de los índices o en función de los objetos o un enlace con el manual de como se hizo en el comentario o alguna tipo de explicación. Creo que fue frustrado por cómo la gente vota. Eliminado el downvote.
    • Podría este enfoque causar eliminaciones en cascada en otras tablas con clave externa hace referencia a las columnas de t?
    • Uso: crear una tabla X tabla Y; — para copiar los datos de la tabla info de Y a X (nuevo), a Continuación, truncate table X; — para eliminar los datos copiados. Hace fácil el resumen de las columnas de la tabla y los detalles, pero no tanto eficiente.

  2. 173

    Algunos de estos enfoques parecen un poco complicado, por lo que generalmente hacen esto como:

    Tabla de la base de table, quiero exclusivo en (campo1, campo2) de mantenimiento de la fila con el max campo3:

    DELETE FROM table USING table alias 
      WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
        table.max_field < alias.max_field

    Por ejemplo, tengo una tabla, user_accounts, y quiero agregar una restricción unique en el correo electrónico, pero tengo algunos duplicados. Decir también que yo quiero conservar la de más reciente creación uno (max id entre los duplicados).

    DELETE FROM user_accounts USING user_accounts ua2
      WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;
    • Nota – USING no es un estándar de SQL, es un PostgreSQL extensión (pero muy útil), pero la pregunta original se menciona específicamente a PostgreSQL.
    • El segundo enfoque es muy rápido en postgres! Gracias.
    • puedes explicar mejor lo que hace USING hacer en postgresql ?
    • Este es por lejos la mejor respuesta. Incluso si usted no tiene una serie de columnas en la tabla a utilizar para la identificación de comparación, vale la pena agregar temporalmente para utilizar este enfoque simple.
    • El USING enfoque es mucho más rápido que max comparaciones. Gran respuesta.
    • Va a darle la vuelta a la menor que (<) operador más que (>) operador me dejan con el mínimo user_account.id?
    • Acabo de comprobar. La respuesta es sí, lo hará. El uso de menos (<) te deja con sólo el max id, mientras que en mayores de lo (>) te deja con sólo el min id, eliminando el resto.
    • El segundo método es mucho más rápido si el correo electrónico está indexado. Como 100 veces más rápido.
    • Gracias por la sencillez!!!
    • Este método también funciona para MySQL, usted sólo tiene que repetir el 2º ‘user_accounts’ como este: DELETE FROM user_accounts USING user_accounts, user_accounts ua2 WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;
    • También puede comparar los registros, que es más corto de escribir: WHERE (table.field1, table.field2) = (alias.field1, alias.field2)
    • uno puede usar: WHERE table1.ctid<table2.ctid – no hay necesidad de añadir número de columna

  3. 25

    Lugar de crear una nueva tabla, también puede volver a insertar filas únicas en la misma tabla, después de truncar. Hacerlo todo en una transacción. Opcionalmente, usted puede colocar la tabla temporal en la final de la transacción automáticamente con ON COMMIT DROP. Ver a continuación.

    Este enfoque sólo es útil cuando hay un montón de filas para eliminar todos sobre la mesa. En sólo un par de duplicados, usar un simple DELETE.

    Que usted ha mencionado millones de filas. Para realizar la operación rápido desea asignar suficiente los búferes temporales para la sesión. El ajuste ha de ser ajustado antes de cualquier temp tampón se utiliza en su actual período de sesiones. Averiguar el tamaño de la tabla:

    SELECT pg_size_pretty(pg_relation_size('tbl'));

    Conjunto temp_buffers en consecuencia. Ronda generosamente porque representación en la memoria necesita un poco más de RAM.

    SET temp_buffers = 200MB;    -- example value
    
    BEGIN;
    
    -- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit
    CREATE TEMPORARY TABLE t_tmp AS  -- retain temp table after commit
    SELECT DISTINCT * FROM tbl;  -- DISTINCT folds duplicates
    
    TRUNCATE tbl;
    
    INSERT INTO tbl
    SELECT * FROM t_tmp;
    -- ORDER BY id; -- optionally "cluster" data while being at it.
    
    COMMIT;

    Este método puede ser superior a la creación de una nueva tabla si en función de que los objetos existen. Vistas, índices, claves foráneas o a otros objetos que hacen referencia a la tabla. TRUNCAR hace comenzar con una pizarra limpia de todos modos (archivo nuevo en el fondo) y se mucho más rápido que DELETE FROM tbl con tablas grandes (DELETE en realidad puede ser más rápido con tablas pequeñas).

    Para tablas grandes, es regularmente más rápido a la caída de los índices y claves foráneas, rellene la tabla y volver a crear estos objetos. Tan lejos como fk limitaciones se refiere usted tiene que estar seguro de que los nuevos datos son válidos de curso o te quedarás en una excepción en el intento de crear la fk.

    Nota que TRUNCATE requiere más agresivo bloqueo de DELETE. Esto puede ser un problema para las tablas con pesada, carga simultánea.

    Si TRUNCATE no es una opción o, en general, para pequeñas y medianas tablas hay una técnica similar con un de datos-modificación del CTE (Postgres 9.1+):

    WITH del AS (DELETE FROM tbl RETURNING *)
    INSERT INTO tbl
    SELECT DISTINCT * FROM del;
    -- ORDER BY id; -- optionally "cluster" data while being at it.

    Más lento para tablas grandes, porque TRUNCATE es más rápido allí. Pero puede ser más rápido (y más simple!) para tablas pequeñas.

    Si usted no tiene ninguna función de todos los objetos, se puede crear una nueva tabla y eliminar el antiguo, pero difícilmente se puede ganar algo más de este enfoque universal.

    Muy tablas grandes que no caben en RAM disponible, la creación de un nueva tabla será considerablemente más rápido. Usted tendrá que sopesar este en contra de los posibles problemas /sobrecarga dependiendo de los objetos.

    • He utilizado este enfoque también. Sin embargo, puede ser personal, pero mi tabla temporal se ha eliminado, y no estará disponible después de la truncate… tenga cuidado de hacer esos pasos si la tabla temporal se ha creado correctamente y está disponible.
    • Usted puede comprobar la existencia para asegurarse, y, o bien utilizar un nombre diferente para la tabla temporal o reutilizar el uno en existencia .. he añadido un poco mi respuesta.
    • ADVERTENCIA: tenga cuidado de +1 a @xlash-tengo que volver a importar mis datos porque la tabla temporal era inexistente después de TRUNCATE. Como Erwin dijo, asegúrese de asegúrese de que existe antes de truncar la tabla. Ver a @codebykat la respuesta
    • Me cambié a una versión sin ON COMMIT DROP, de modo que las personas que pierden la parte donde escribí «en una transacción» no perder datos. Y he añadido BEGIN / COMMIT para aclarar «una transacción».
    • Thx @ErwinBrandstetter
    • Creo que Esta solución es menos eficiente cuando no hay tanta duplicados para eliminar de la tabla original. Y es peor cuando no hay duplicados en todos. Se puede ofrecer alguna mejora, por ejemplo, para evitar truncar cuando ambos t_tmp y el original de la tabla tienen el mismo número de filas (=> allí donde no hay duplicados). ¿DELETE más adecuado para esas situaciones ?
    • Sí, por supuesto. El procedimiento sugerido, sólo tiene sentido para eliminar grandes porciones de una mesa grande.
    • Su último ejemplo es la falta de una N en DISTINCT (uno de los personajes de las ediciones no están permitidos, al menos para mí…).
    • Gracias, corregido.
    • solución con el USO tardó más de 3 horas en la tabla con 14 millones de registros. Esta solución con temp_buffers tardó 13 minutos. Gracias.

  4. 20

    Puede utilizar oid o ctid, que normalmente es un «no visibles» las columnas de la tabla:

    DELETE FROM table
     WHERE ctid NOT IN
      (SELECT MAX(s.ctid)
        FROM table s
        GROUP BY s.column_has_be_distinct);
    • Para la eliminación de en lugar de, NOT EXISTS debe ser considerablemente más rápido: DELETE FROM tbl t WHERE EXISTS (SELECT 1 FROM tbl t1 WHERE t1.dist_col = t.dist_col AND t1.ctid > t.ctid) – o el uso de cualquier otra columna o conjunto de columnas para ordenar a elegir un sobreviviente.
    • es la consulta que usted proporcione supone que el uso de NOT EXISTS?
    • Debe ser EXISTS aquí. Lea así: «Eliminar todas las filas donde cualquier otra fila con el mismo valor en dist_col pero un mayor ctid«. El único sobreviviente por grupo de incautos será el que con la mayor ctid.
    • Solución más fácil si usted tiene sólo un par de filas duplicadas. Puede ser utilizado con LIMIT si usted sabe el número de duplicados.
  5. 19

    El PostgreSQL función de la ventana es útil para este problema.

    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);

    Ver Eliminar los duplicados.

    • Y el uso de «ctid» en lugar de «id», esto en realidad funciona plenamente las filas duplicadas.
    • Gran solución. Tenía que hacer esto para una mesa con miles de millones de registros. He añadido un DONDE al interior de SELECCIONAR a hacer en trozos.
  6. 8

    Consulta generalizada para eliminar duplicados:

    DELETE FROM table_name
    WHERE ctid NOT IN (
      SELECT max(ctid) FROM table_name
      GROUP BY column1, [column 2, ...]
    );

    La columna ctid es una columna especial para cada tabla, pero no es visible a menos que se mencione específicamente. El ctid valor de la columna es considerado único para cada fila en una tabla.

    • la única respuesta universal! Funciona sin auto/combinación cartesianas. Vale la pena agregar a pesar de que es esencial para especificar correctamente GROUP BY cláusula – esta debe ser la unicidad de criterios » que es violado ahora o si usted quisiera la clave para detectar duplicados. Si se especifica mal no funcionará correctamente
    • Gracias por enseñarme acerca de la ctid!
  7. 7

    De un viejo postgresql.org lista de correo:

    create table test ( a text, b text );

    Valores únicos

    insert into test values ( 'x', 'y');
    insert into test values ( 'x', 'x');
    insert into test values ( 'y', 'y' );
    insert into test values ( 'y', 'x' );

    Valores duplicados

    insert into test values ( 'x', 'y');
    insert into test values ( 'x', 'x');
    insert into test values ( 'y', 'y' );
    insert into test values ( 'y', 'x' );

    Uno más del doble duplicado

    insert into test values ( 'x', 'y');
    
    select oid, a, b from test;

    Seleccionar filas duplicadas

    select o.oid, o.a, o.b from test o
        where exists ( select 'x'
                       from test i
                       where     i.a = o.a
                             and i.b = o.b
                             and i.oid < o.oid
                     );

    Eliminar filas duplicadas

    Nota: PostgreSQL dosn no soporta alias en
    la tabla que se menciona en la from cláusula
    de eliminar.

    delete from test
        where exists ( select 'x'
                       from test i
                       where     i.a = test.a
                             and i.b = test.b
                             and i.oid < test.oid
                 );
    • Su explicación es muy inteligente ,pero le falta un punto ,En crear tabla especificar la oid, a continuación, acceder a la oid otro mensaje de error de la pantalla
    • Gracias por sus comentarios que atañen a la mejora de la respuesta, voy a tener la consideración de este punto.
    • Esta realidad vino de postgresql.org/message-id/…
    • Usted puede utilizar el sistema de la columna ‘ctid’ si ‘oid’ da un error.
  8. 4

    Acabo de utilizar Erwin Brandstetter la respuesta con éxito para eliminar duplicados en una tabla de combinación (una tabla que carecen de su propia primaria IDs), pero se encontró que hay una advertencia importante.

    Incluyendo ON COMMIT DROP significa que la tabla temporal se caiga en la final de la transacción. Para mí, eso significaba que la tabla temporal se ya no está disponible por el momento en que fui a insertarlo!

    Acabo de CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; y todo funcionaba bien.

    La tabla temporal no se eliminan al final de la sesión.

  9. 3

    Esta función elimina los duplicados sin la eliminación de los índices y lo hace a cualquier mesa.

    Uso: select remove_duplicates('mytable');

    --- 
    --- remove_duplicates(tablename) elimina registros duplicados de una tabla (convertir de conjunto a conjunto único) 
    --- 
    CREAR O REEMPLAZAR la FUNCIÓN de remove_duplicates(texto) DEVUELVE void COMO $$ 
    DECLARAR 
    tablename ALIAS DE $1; 
    COMENZAR 
    EJECUTAR 'CREAR una TABLA TEMPORAL _DISTINCT_' || tablename || 'AS (SELECT DISTINCT * FROM' || tablename || ');'; 
    EJECUTAR 'DELETE FROM' || tablename || ';'; 
    EJECUTAR 'INSERT INTO' || tablename || '(SELECT * FROM _DISTINCT_' || tablename || ');'; 
    EJECUTAR 'DROP TABLE _DISTINCT_' || tablename || ';'; 
    De RETORNO; 
    END; 
    $$ LANGUAGE plpgsql; 
    
  10. 3
    DELETE FROM table
      WHERE something NOT IN
        (SELECT     MAX(s.something)
          FROM      table As s
          GROUP BY  s.this_thing, s.that_thing);
    • Que es lo que estoy haciendo actualmente, pero es de tomar un tiempo muy largo para que se ejecute.
    • ¿No sería este un error si varias filas en la tabla tienen el mismo valor en la columna de algo?
  11. 3

    Si usted tiene sólo uno o un par de entradas duplicadas, y son de hecho duplicado (es decir, aparecen dos veces), se puede utilizar el «oculto» ctid columna, como se ha propuesto anteriormente, junto con los LIMIT:

    DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1);

    Esto eliminará sólo la primera de las filas seleccionadas.

    • Sé que no dirección de OP del problema, que tiene muchos duplicados en millones de filas, pero puede ser útil de todos modos.
    • Esto tendría que ser ejecutado una vez por cada fila duplicada. shekwi la respuesta de necesidad sólo se ejecuta una vez.
  12. 3

    Primer lugar, usted necesita decidir en cual de su «duplicados» se va a mantener. Si todas las columnas son iguales, OK, puede eliminar cualquiera de ellos… Pero tal vez usted quiere mantener sólo los más recientes, o algún otro criterio?

    La manera más rápida depende de su respuesta a la pregunta anterior, y también en el % de duplicados en la tabla. Si usted desecha el 50% de sus filas, mejor CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;, y si se elimina el 1% de las filas, el uso de BORRAR es mejor.

    También para las operaciones de mantenimiento de esta, es generalmente bueno para establecer work_mem a una buena parte de su memoria RAM: ejecutar EXPLICAR, compruebe el número N de tipo/hash, y establecer work_mem a su RAM /2 /N. el Uso de un montón de memoria RAM; es bueno para la velocidad. Mientras que sólo tienen una conexión concurrente…

  13. 1

    Estoy trabajando con PostgreSQL 8.4. Cuando me encontré con la propuesta de código, me encontré con que no era
    en realidad, la eliminación de los duplicados. En la ejecución de algunas pruebas, he encontrado que la adición de la
    «DISTINTAS EN (duplicate_column_name)» y el «ORDEN POR duplicate_column_name» hizo el truco. Yo no soy de SQL guru, he encontrado esto en el PostgreSQL 8.4 SELECCIONE…DISTINTOS doc.

    CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
    DECLARE
      tablename ALIAS FOR $1;
      duplicate_column ALIAS FOR $2;
    BEGIN
      EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);';
      EXECUTE 'DELETE FROM ' || tablename || ';';
      EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
      EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
      RETURN;
    END;
    $$ LANGUAGE plpgsql;
  14. 1

    Esto funciona muy bien y es muy rápido:

    CREATE INDEX otherTable_idx ON otherTable( colName );
    CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;
  15. 1
    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);

    Eliminar los duplicados por columna(s) y mantener la fila con el menor número de id. El patrón es tomado de la postgres wiki

    El uso de las Cte se puede conseguir una versión más legible de lo anterior a través de este

    WITH duplicate_ids as (
        SELECT id, rnum 
        FROM num_of_rows
        WHERE rnum > 1
    ),
    num_of_rows as (
        SELECT id, 
            ROW_NUMBER() over (partition BY column1, 
                                            column2, 
                                            column3 ORDER BY id) AS rnum
            FROM tablename
    )
    DELETE FROM tablename 
    WHERE id IN (SELECT id from duplicate_ids)
  16. 1
    CREATE TABLE test (col text);
    INSERT INTO test VALUES
     ('1'),
     ('2'), ('2'),
     ('3'),
     ('4'), ('4'),
     ('5'),
     ('6'), ('6');
    DELETE FROM test
     WHERE ctid in (
       SELECT t.ctid FROM (
         SELECT row_number() over (
                   partition BY col
                   ORDER BY col
                   ) AS rnum,
                ctid FROM test
           ORDER BY col
         ) t
        WHERE t.rnum >1);
    • He probado y funcionó; he formateado para mejorar la legibilidad. Se ve muy sofisticado, pero podría utilizar un poco de explicación. ¿Cómo se podía cambiar este ejemplo para su propia caso de uso?

Dejar respuesta

Please enter your comment!
Please enter your name here