Como lidar com dados duplicados no PostgreSQL usando a coluna de sistema ctid

Você que utiliza PostgreSQL, já deve ter se deparado com o problema dos dados duplicados. É possível que em uma tabela existam campos que contenham valores repetidos e sua necessidade seja tornar esses registros únicos.
Como proceder com valores repetidos sem eliminar todos eles? Seria possível deixar somente o mais atual armazenado? É o que veremos a seguir.

Coluna de sistema ctid

Toda tabela tem algumas colunas implicitamente definidas pelo sistema e cujos nomes são palavras-chave reservadas.
Atualmente as colunas de sistema [1] são: tableoid, xmin, cmin, xmax, cmax e ctid. Cada uma possui os metadados da tabela à qual pertencem.

A coluna de sistema ctid tem por finalidade armazenar a versão da localização física da linha. Essa versão pode mudar caso a linha seja atualizada (UPDATE) ou a tabela passe por um VACUUM FULL (esvaziamento completo).

A coluna ctid é do tipo tid [2], que significa tuple identifier (ou row identifier), que é um par (número do bloco, índice de tupla dentro do bloco) que identifica a localização física da linha dentro da tabela. Essa coluna tem sempre seu valor único na tabela, sendo assim, quando há linhas com valores repetidos esta coluna pode ser utilizada como critério para eliminação desses.

Criação de tabela de teste para ctid:

CREATE TABLE tb_teste_ctid (
    col1 int,
    col2 text);

Popular a tabela:

INSERT INTO tb_teste_ctid VALUES 
    (1, 'foo'),
    (2, 'bar'),
    (3, 'baz');

Verificar a tabela:

SELECT ctid, * FROM tb_teste_ctid;

 ctid  | col1 | col2 
-------+------+------
 (0,1) |    1 | foo
 (0,2) |    2 | bar
 (0,3) |    3 | baz

Atualizar uma linha:

UPDATE tb_teste_ctid
    SET col2 = 'spam'
    WHERE col1 = 1;

Verificando a tabela após o UPDATE:

SELECT ctid, * FROM tb_teste_ctid;

 ctid | col1 | col2 
-------+------+------
(0,2) | 2 | bar
(0,3) | 3 | baz
(0,4) | 1 | spam

Nota-se que o registro atualizado teve seu ctid alterado também.

Um simples teste de VACUUM:

VACUUM FULL tb_teste_ctid;

Checando novamente a tabela (pós VACUUM FULL):

SELECT ctid, * FROM tb_teste_ctid;

ctid   | col1 | col2 
-------+------+------
(0,1)  | 2    | bar
(0,2)  | 3    | baz
(0,3)  | 1    | spam

Nota-se agora que após o processo de VACUUM FULL os ctids foram renovados, com os valores como
se a tabela tivesse recebido os dados pela primeira vez.

Atualizar um registro com a cláusula RETURNING:

UPDATE tb_teste_ctid
    SET col2 = 'eggs'
    WHERE col1 = 1
    RETURNING ctid;

ctid 
-------
(0,4)

Verificando a tabela novamente após o UPDATE:

SELECT ctid, * FROM tb_teste_ctid;

 ctid  | col1 | col2 
-------+------+------
 (0,1) |    2 | bar
 (0,2) |    3 | baz
 (0,4) |    1 | eggs

E assim podemos entender o papel da coluna de sistema ctid, vemos na prática como ela controla a versão de cada registro.

Eliminação de valores repetidos com ctid

Se uma tabela tem valores repetidos em um campo e desejamos tornar esse campo único posteriormente, também temos outras ações a tomar.
Vale lembrar que um campo PRIMARY KEY também é único.

Foi decidido que os valores repetidos desse campo serão eliminados.
Agora é necessário estabelecer um critério para decidir dentre esses valores repetidos qual permanecerá.
No caso a seguir o critério é a linha mais atual, ou seja, a linha que tiver o maior valor de ctid.

Criação da tabela de teste:

CREATE TABLE tb_foo(
    id_ int,  -- Futura PRIMARY KEY
    letter char(1)
);

Popular a tabela:

INSERT INTO tb_foo (id_, letter)
    SELECT generate_series(1, 10), 'a';

Verificar a tabela:

SELECT id_, letter FROM tb_foo;

 id_ | letter 
-----+--------
   1 | a
   2 | a
   3 | a
   4 | a
   5 | a
   6 | a
   7 | a
   8 | a
   9 | a
  10 | a

Inserir mais 3 registros:

INSERT INTO tb_foo (id_, letter) SELECT generate_series(1, 3), 'b';

Verificar os itens duplicados:

SELECT id_, letter FROM tb_foo WHERE id_ <= 3;

 id_ | letter  
-----+--------
   1 | a
   2 | a
   3 | a
   1 | b
   2 | b
   3 | b

Há duplicidades de valores no campo id_ da tabela…

Vamos realizar uma tentativa de fazer com que o campo id_ seja uma chave primária:

ALTER TABLE tb_foo ADD CONSTRAINT tb_foo_pkey PRIMARY KEY (id_);

ERROR:  could not create unique index "tb_foo_pkey"
DETAIL:  Key (id_)=(3) is duplicated.

Utilizando CTE e funções de janela descobrimos quais valores repetidos manteremos:

WITH t AS (
SELECT
    id_,
    count(id_) OVER (PARTITION BY id_) AS count_id,  -- Count
    ctid,
    max(ctid) OVER (PARTITION BY id_) AS max_ctid  -- Most current ctid
    
    FROM tb_foo
)
SELECT
    t.id_,
    t.max_ctid
    FROM t
    WHERE t.count_id > 1  -- Filters which values repeat
    GROUP by id_, max_ctid;

 id_ | max_ctid 
-----+----------
   3 | (0,13)
   1 | (0,11)
   2 | (0,12)

O próximo passo é limpar a tabela deixando somente valores únicos para o campo id_ removendo as linhas mais antigas:

WITH
t1 AS (
SELECT
    id_,
    count(id_) OVER (PARTITION BY id_) AS count_id,
    ctid,
    max(ctid) OVER (PARTITION BY id_) AS max_ctid
    
    FROM tb_foo
),

t2 AS (  -- Tabela virtual que filtra valores repetidos que permanecerão
SELECT t1.id_, t1.max_ctid
    FROM t1
    WHERE t1.count_id > 1
    GROUP by t1.id_, t1.max_ctid)

DELETE  -- DELETE com JOIN 
    FROM tb_foo AS f
    USING t2
    WHERE 
        f.id_ = t2.id_ AND  -- tb_foo tenha id_ igual a t2 (valores repetidos)
        f.ctid < t2.max_ctid;  -- ctid seja menor quer o máximo (mais atual)

Verifique agora os valores da tabela sem os registros duplicados no campo id_:

SELECT id_, letter FROM tb_foo;

 id_ | letter 
-----+--------
   4 | a
   5 | a
   6 | a
   7 | a
   8 | a
   9 | a
  10 | a
   1 | b
   2 | b
   3 | b        

Agora é possível alterar a tabela para apontar o campo id_ como PRIMARY KEY:

ALTER TABLE tb_foo ADD CONSTRAINT tb_foo_pkey PRIMARY KEY (id_);

Conclusão

É importante conhecer a respeito de colunas de sistema no PostgreSQL, pois em uma situação como essa, com dados repetidos, temos um critério de unicidade onde deve ser mantido somente o que realmente for necessário.
Além de tudo utilizarmos recursos interessantes do PostgreSQL como CTE (Common Table Expression) [3] e Funções de Janela (Window Functions).

Esperamos que ajude o leitor com problemas semelhantes!

Até mais!

Mais informações

[1] Colunas de sistema: https://www.postgresql.org/docs/current/ddl-system-columns.html

[2] Tipos oid: https://www.postgresql.org/docs/current/datatype-oid.html

[3] CTE: https://www.postgresql.org/docs/current/queries-with.html

[4] Funções de Janela: https://www.postgresql.org/docs/current/tutorial-window.html