delete duplicate rows postgresql

Obtenga columnas que difieren entre 2 filas

Question

Tengo una company mesa con 60 columnas. El objetivo es crear una herramienta para buscar, comparar y eliminar duplicados en esta tabla.

Ejemplo: encuentro 2 compañías que potencialmente son iguales, pero necesito saber qué valores (columnas) difieren entre estas 2 filas para continuar.

Creo que es posible comparar columna por columna x 60, pero busco una solución más simple y más genérica.

Algo como:

SELECT * FROM company where co_id=22
SHOW DIFFERENCE
SELECT * FROM company where co_id=33

El resultado debe ser los nombres de columna que difieren.

Answer 1

Aquí hay un procedimiento almacenado que debería ayudarte la mayor parte del camino ...

Si bien esto debería funcionar "tal como está", no tiene comprobación de errores, que debe agregar.

Obtiene todas las columnas en la tabla y pasa por encima de ellas. Una diferencia es cuando el recuento de los elementos distintos es más de uno. Además, la salida es:

• El recuento de la cantidad de diferencias
• Mensajes para cada columna donde hay una diferencia

Puede ser más útil devolver un conjunto de filas de las columnas con las diferencias. ¡En fin, buena suerte!

Uso:

SELECT showdifference(''public'',''company'',''co_id'',22,33)
CREATE OR REPLACE FUNCTION showdifference(p_schema text, p_tablename text,p_idcolumn text,p_firstid integer, p_secondid integer)
RETURNS INTEGER AS
$BODY$
DECLARE
l_diffcount INTEGER;
l_column text;
l_dupcount integer;
column_cursor CURSOR FOR select column_name from information_schema.columns where table_name = p_tablename and table_schema = p_schema and column_name <> p_idcolumn;
BEGIN
-- need error checking here, to ensure the table and schema exist and the columns exist
-- Should also check that the records ids exist.
-- Should also check that the column type of the id field is integer
-- Set the number of differences to zero.
l_diffcount := 0;
-- use a cursor to iterate over the columns found in information_schema.columns
-- open the cursor
OPEN column_cursor;
LOOP
FETCH column_cursor INTO l_column;
EXIT WHEN NOT FOUND;
-- build a query to see if there is a difference between the columns. If there is raise a notice
EXECUTE ''select count(distinct '' || quote_ident(l_column) || '' ) from '' || quote_ident(p_schema) || ''.'' || quote_ident(p_tablename) || '' where '' || quote_ident(p_idcolumn) || '' in (''|| p_firstid || '','' || p_secondid ||'')''
INTO l_dupcount;
IF l_dupcount > 1 THEN
-- increment the counter
l_diffcount := l_diffcount +1;
RAISE NOTICE ''% has % differences'', l_column, l_dupcount ; -- for "real" you might want to return a rowset and could do something here
END IF;
END LOOP;
-- close the cursor
CLOSE column_cursor;
RETURN l_diffcount;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;

Answer 2

Puede usar la extensión hstore para esto. A menudo resulta útil cuando existe la necesidad de iterar sobre columnas.

El truco consiste en convertir, para cada fila, los contenidos en column_name=>value pares de column_name=>value en un valor hstore, y luego usar las funciones hstore para calcular las diferencias.

Manifestación:

CREATE TABLE table1 (id int primary key, t1 text, t2 text, t3 text);

Inserte dos filas que difieran en la clave principal y en otra columna ( t3 ).

INSERT INTO table1 VALUES (
(1,''foo'',''bar'',''baz''),
(2,''foo'',''bar'',''biz'')
);

La consulta:

SELECT skeys(h1-h2) from
(select hstore(t.*) as h1 from table1 t where id=1) h1
CROSS JOIN
(select hstore(t.*) as h2 from table1 t where id=2) h2;

h1-h2 calcula la clave de diferencia por clave y skeys() emite el resultado como un conjunto.

Resultado:

skeys ------- id t3

La lista de selección podría refinarse con skeys((h1-h2)-''id'') para eliminar siempre el id , que, como la clave principal, siempre diferirá entre las filas.