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:
1 | CREATE TABLE tb_teste_ctid ( |
Popular a tabela:
1 | INSERT INTO tb_teste_ctid VALUES |
Verificar a tabela:
1 | SELECT ctid, * FROM tb_teste_ctid; |
ctid | col1 | col2
-------+------+------
(0,1) | 1 | foo
(0,2) | 2 | bar
(0,3) | 3 | baz
Atualizar uma linha:
Verificando a tabela após o UPDATE:
1 | 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:
1 | VACUUM FULL tb_teste_ctid; |
Checando novamente a tabela (pós VACUUM FULL):
1 | 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:
ctid
-------
(0,4)
Verificando a tabela novamente após o UPDATE:
1 | 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:
Popular a tabela:
1 | INSERT INTO tb_foo (id_, letter) |
2 | SELECT generate_series(1, 10), 'a' ; |
Verificar a tabela:
1 | 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:
1 | INSERT INTO tb_foo (id_, letter) SELECT generate_series(1, 3), 'b' ; |
Verificar os itens duplicados:
1 | 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:
1 | 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:
04 | count (id_) OVER (PARTITION BY id_) AS count_id, |
06 | max (ctid) OVER (PARTITION BY id_) AS max_ctid |
14 | WHERE t.count_id > 1 |
15 | 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:
05 | count (id_) OVER (PARTITION BY id_) AS count_id, |
07 | max (ctid) OVER (PARTITION BY id_) AS max_ctid |
13 | SELECT t1.id_, t1.max_ctid |
15 | WHERE t1.count_id > 1 |
16 | GROUP by t1.id_, t1.max_ctid) |
23 | f.ctid < t2.max_ctid; |
Verifique agora os valores da tabela sem os registros duplicados no campo id_:
1 | 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:
1 | 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