Delete duplicate record from table in Postgresql

Question

Situation

Currently I performed a process twice in which it should be processed only once. So my table was populated twice with the same data.

tb_kardex
cd_kardex | cd_item

tb_kardex
| 1 | 45875 |
| 2 | 45876 |
| 3 | 45877 |
| 4 | 45875 |
| 5 | 45876 |
| 6 | 45877 |

Doubt

How to delete only the "second" record of the same item?

Answer 1

Do it like this:

DELETE a FROM nomes AS a, nomes AS b WHERE a.nome=b.nome AND a.id < b.id

Code find here.

UPDATING

To postgresql 9.3 would do so: (Here a working Fiddle)

DELETE FROM tb_kardex a
  where exists (select 1 from tb_kardex b 
      WHERE a.cd_kardex=b.cd_kardex 
        AND a.cd_item < b.cd_item);

Answer 2

Solution

DELETE FROM tb_kardex
WHERE cd_kardex IN (
SELECT  cd_kardex
FROM
    (
        SELECT  cd_kardex,
            ROW_NUMBER() OVER 
            (PARTITION BY cd_item ORDER BY cd_kardex asc) AS rn -- CRIA UM CONTAGEM DE PARA CADA CD_ITEM REPETIDO
        FROM    tb_kardex                                       -- É IMPORTANTE A UTILIZAÇÃO DO ORDER PARA MANTER O RN 2
    ) A                                                         -- NO ULTIMO cd_kardex
WHERE   rn = 2
)

Altering

If you have done a process repeat several times having more than 2 Rows change the criteria rn > 1.

Answer 3

According to this reply on Soen you can create a temporary table and use DISTINCT:

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

I recommend you take a backup first