¿"Duplicate Key" en PostgreSQL tras una importación? Realinea todas las identity con una consulta
El problema de la “desalineación de las identity” aparece con frecuencia en PostgreSQL (y en otros sistemas de bases de datos parecidos) cuando no coinciden los valores generados por una secuencia o una columna identity y los datos realmente guardados en la tabla. Esta desalineación puede tener varias causas: errores al insertar valores en una columna identity, equivocaciones durante la manipulación de los datos o problemas con los generadores de secuencias. Es fundamental detectar y resolver el problema para mantener la integridad de los datos y evitar interrupciones en las operaciones de los usuarios.
Supongamos que tenemos una identity en una tabla creada con el siguiente DDL SQL.
create table mytable(
id bigint not null generated by default as identity primary key,
value1 varchar,
value2 varchar
);
Después cargamos datos desde otra fuente usando el id que normalmente debería generar la identity (pasa cuando importas datos de una base de datos a otra).
insert into mytable(id, value1, value2) values
(1,'Daniele','Teti'),
(2,'Peter','Parker'),
(3,'Bruce','Banner');
¡Bien! Los datos están ahí, pero cuando intentas insertar datos nuevos de la forma habitual (quizás directamente desde la aplicación)…
insert into mytable(value1, value2) values ('Jake', 'The Cat');
Obtenemos un error.
ERROR: duplicate key value violates unique constraint "mytable_pkey"
Detail: Key ("id")=(1) already exists.
¿Qué ha pasado? Sencillo: al cargar los datos en la tabla le dijimos a PostgreSQL que usara, para el campo id, el valor indicado directamente en la sentencia insert y no el valor proporcionado por la identity. Por eso la identity sigue valiendo 1: nadie le ha pedido nada todavía.
¿No estaría bien pedirle a PostgreSQL que genere un script para resolver el problema?
Sí, estaría bien… ¡hagámoslo!
Ejecuta esta consulta
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'
El campo devuelto, llamado sql_stm, contiene una sentencia SQL por cada tabla de la base de datos que tenga una identity. Cada sentencia corrige el problema, tabla por tabla.
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));
...
Ejecuta el resultado en psql o en otra herramienta y tus identity vuelven a funcionar correctamente.
¿Quieres saber más sobre PostgreSQL? Echa un vistazo a mis cursos para desarrolladores y administradores (disponibles en inglés, italiano y español).
Comments