Código fuente para probar registros duplicados
Ver la entrada correspondiente en el siguiente enlace:
Registros duplicados en PostgreSQL (y otros sistemas)
BEGIN; ------------------------------------------------------------------------------- CREATE TABLE test1(first INTEGER, second TEXT, third TEXT) WITH (OIDS = TRUE) --PostgreSQL ; INSERT INTO test1 VALUES (0, 'zero' , 'OK' ); INSERT INTO test1 VALUES (0, 'zero' , 'OK' ); INSERT INTO test1 VALUES (0, 'zero' , 'OK' ); INSERT INTO test1 VALUES (1, 'one' , 'OK' ); INSERT INTO test1 VALUES (2, 'two' , 'OK' ); INSERT INTO test1 VALUES (3, 'three', 'OK' ); --Para ver las columnas ocultas. SELECT * , oid, ctid --PostgreSQL FROM test1 ; --Para ver sólo los registros duplicados. SELECT COUNT(*), first, second, third FROM test1 GROUP BY first, second, third HAVING (COUNT(*) > 1); /* --PostgreSQL. --Para borrar todos los repetidos. DELETE FROM test1 WHERE (first, second, third) IN (SELECT first, second, third FROM test1 GROUP BY first, second, third HAVING (COUNT(*) > 1)) ; */ /* --PostgreSQL. --Para borrar todos los repetidos menos uno usando CTID. DELETE FROM test1 WHERE ctid NOT IN (SELECT min(ctid) FROM test1 GROUP BY first, second, third) ; */ /* --PostgreSQL. --Para borrar todos los repetidos menos uno usando OID. DELETE FROM test1 WHERE oid NOT IN (SELECT min(oid) FROM test1 GROUP BY first, second, third) ; */ --Para ver lo que queda finalmente en la tabla. SELECT * FROM test1; DROP TABLE test1; ------------------------------------------------------------------------------- ROLLBACK;
No hay comentarios:
Publicar un comentario