Removendo registros idênticos no PostgreSQL

Eu sei, em teoria toda tabela deveria ter uma chave primaria e registros jamais poderiam ser duplicados. Mas a vida é uma caixinha de surpresas…. em algumas situações bem peculiares, você pode precisar de uma tabela sem uma PK, como numa área intermediária para a carga de dados. Estes dias a pergunta aparecer na lista do PGBR e eu resolvi mostrar aqui a solução:

postgres=# create table teste (t integer);
CREATE TABLE
postgres=# INSERT INTO teste VALUES (1), (2), (2), (3), (4);
INSERT 0 5
postgres=# SELECT * FROM teste;
t
---
1
2
2
3
4
(5 rows)

Note que os registros de número ‘2’ são idênticos e não temos como diferecia-los olhando apenas para os dados. A única diferença entre eles é o local onde estão armazenados no disco. E o campo que informa onde eles estão fisicamente armazenados se chama CTID, e ele tem um tipo de dados peculiar que é o TID. Um TID é um par de valores que representam o bloco onde e a posição do bloco onde o registro se encontra:

postgres=# SELECT ctid, * FROM teste;
ctid | t
-------+---
(0,1) | 1
(0,2) | 2
(0,3) | 2
(0,4) | 3
(0,5) | 4
(5 rows)

postgres=# DELETE FROM teste WHERE ctid = '(0,2)'::tid;
DELETE 1
postgres=# SELECT * FROM teste;
t
---
1
2
3