"Duplicate key" no PostgreSQL após uma importação? Realinhe todas as identities com uma query
O problema do “desalinhamento da identity” aparece com frequência no PostgreSQL (e em outros bancos de dados parecidos) quando existe uma diferença entre os valores gerados por uma sequence ou por uma coluna identity e os dados realmente gravados na tabela. Esse desalinhamento pode acontecer por vários motivos, como erros na inserção de valores em uma coluna identity, enganos durante a manipulação dos dados ou problemas com os geradores de sequence. É fundamental detectar e resolver o problema para manter a integridade dos dados e evitar interrupções nas operações dos usuários.
Digamos que temos uma identity em uma tabela criada com o seguinte DDL SQL.
create table mytable(
id bigint not null generated by default as identity primary key,
value1 varchar,
value2 varchar
);
Depois carregamos alguns dados de outra fonte usando o id que normalmente deveria ser gerado pela identity (acontece quando você importa dados de um banco para outro)
insert into mytable(id, value1, value2) values
(1,'Daniele','Teti'),
(2,'Peter','Parker'),
(3,'Bruce','Banner');
Ótimo! Os dados estão lá, mas quando você tenta inserir novos dados do jeito de sempre (talvez direto da aplicação)…
insert into mytable(value1, value2) values ('Jake', 'The Cat');
Recebemos um erro.
ERROR: duplicate key value violates unique constraint "mytable_pkey"
Detail: Key ("id")=(1) already exists.
O que aconteceu? Simples: quando carregamos os dados na tabela, dissemos ao PostgreSQL para usar no campo id o valor informado diretamente no insert, e não o valor fornecido pela identity. Por isso a identity continua valendo 1: ninguém ainda pediu nada a ela.
Não seria bom pedir ao PostgreSQL que gere um script para resolver o problema?
Seria, sim… vamos fazer isso!
Execute esta query
select
'SELECT setval(pg_get_serial_sequence('''
|| pg_class.relname || ''',''' || attname || '''), (select max('
|| attname || ') from ' || pg_class.relname || '));' sql_stm
from
pg_attribute join pg_class on pg_attribute.attrelid = pg_class.oid
where
attnum > 0 and attidentity = 'd'
O campo retornado, chamado sql_stm, contém uma instrução SQL para cada tabela do banco que tenha uma identity. Essa instrução SQL corrige o problema, uma tabela por vez.
SELECT setval(pg_get_serial_sequence('approval_groups','id'), (select max(id) from approval_groups));
SELECT setval(pg_get_serial_sequence('approval_profiles','id'), (select max(id) from approval_profiles));
SELECT setval(pg_get_serial_sequence('attendance_sheets','id'), (select max(id) from attendance_sheets));
SELECT setval(pg_get_serial_sequence('clocking_points','id'), (select max(id) from clocking_points));
SELECT setval(pg_get_serial_sequence('clocking_points_profiles','id'), (select max(id) from clocking_points_profiles));
SELECT setval(pg_get_serial_sequence('clocking_points_types','id'), (select max(id) from clocking_points_types));
SELECT setval(pg_get_serial_sequence('clockings','id'), (select max(id) from clockings));
SELECT setval(pg_get_serial_sequence('commissions','id'), (select max(id) from commissions));
SELECT setval(pg_get_serial_sequence('countries','id'), (select max(id) from countries));
SELECT setval(pg_get_serial_sequence('country_festivities','id'), (select max(id) from country_festivities));
SELECT setval(pg_get_serial_sequence('department_festivities','id'), (select max(id) from department_festivities));
SELECT setval(pg_get_serial_sequence('departments','id'), (select max(id) from departments));
SELECT setval(pg_get_serial_sequence('emails','id'), (select max(id) from emails));
SELECT setval(pg_get_serial_sequence('events','id'), (select max(id) from events));
...
Basta executar isso no psql ou em outra ferramenta e suas identities voltam a funcionar corretamente.
Quer saber mais sobre PostgreSQL? Confira meus treinamentos para desenvolvedores e administradores (disponíveis em inglês, italiano e espanhol).
Comments