PostgreSQL – Como eliminar valores repetidos

PostgreSQL – Como eliminar valores repetidos

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

 

Líder em Treinamento e serviços de Consultoria, Suporte e Implantação para o mundo open source. Conheça nossas soluções:

CURSOSCONSULTORIA

Anterior Gitea básico
Próxima Consultoria 4Linux In-Company 2021: O Ano da Reabertura para sua Empresa

About author

Juliano Atanazio
Juliano Atanazio 2 posts

Graduado em "Informática para Gestão de Negócios" pela FATEC Zona Sul. Atua com Linux e Software Livre desde 2006. Hoje atua principalmente como DBA PostgreSQL e programador Python.

View all posts by this author →

Você pode gostar também

Banco de Dados

Atividade mostra o grande expertise da 4Linux no banco de dados postgreSQL.

O TCE-AM (Tribunal de Contas do Estado do Amazonas) teve 16.500 processos apagados indevidamente devido a uma pane nos sistemas e-Contas e Spede (Sistema de Processos e Documentos Eletrônicos) depois

Banco de Dados

Instalação do MySQL 8 em Debian e CentOS

Ao tentar instalar o MySQL – o banco de dados open-source mais popular do mundo – você pode ser surpreendido no final do processo ao obter o MariaDB. Este fork

Banco de Dados

Mercado pediu e 4Linux lança curso de MySQL.

DBA moderno precisa conhecer vários banco de dados. Atendendo a uma demanda do mercado, a 4Linux anunciou nesta data o lançamento do seu mais novo curso Administração MySQL com Alta Performance