PostgreSQL: "Duplicate Key" nach dem Import? Alle Identities mit einer Abfrage wieder geraderücken
Das Problem der “aus dem Takt geratenen Identity” tritt in PostgreSQL (und ähnlichen Datenbanksystemen) häufig auf, wenn die Werte, die eine Sequence oder Identity-Spalte erzeugt, nicht zu den tatsächlich in der Tabelle gespeicherten Daten passen. Dafür kann es verschiedene Gründe geben, etwa Fehler beim Einfügen von Werten in eine Identity-Spalte, Versehen bei der Datenbearbeitung oder Probleme mit Sequence-Generatoren. Man muss das Problem erkennen und beheben, um die Datenintegrität zu wahren und Störungen für die Benutzer zu vermeiden.
Nehmen wir an, wir haben eine Identity in einer Tabelle, die mit folgender SQL-DDL angelegt wurde.
create table mytable(
id bigint not null generated by default as identity primary key,
value1 varchar,
value2 varchar
);
Dann laden wir Daten aus einer anderen Quelle und verwenden dabei die id, die normalerweise von der Identity erzeugt würde (das passiert, wenn man Daten von einer Datenbank in eine andere importiert)
insert into mytable(id, value1, value2) values
(1,'Daniele','Teti'),
(2,'Peter','Parker'),
(3,'Bruce','Banner');
Gut! Die Daten sind da, aber wenn du versuchst, neue Daten auf dem üblichen Weg einzufügen (vielleicht direkt aus der Anwendung)…
insert into mytable(value1, value2) values ('Jake', 'The Cat');
gibt es einen Fehler.
ERROR: duplicate key value violates unique constraint "mytable_pkey"
Detail: Key ("id")=(1) already exists.
Was ist passiert? Ganz einfach: Beim Laden der Daten haben wir PostgreSQL gesagt, dass es für das Feld id den Wert verwenden soll, der direkt in der Insert-Anweisung steht, und nicht den Wert der Identity. Die Identity steht also immer noch auf 1, weil sie bisher niemand nach etwas gefragt hat.
Wäre es nicht schön, wenn PostgreSQL selbst ein Skript erzeugen würde, das das Problem löst?
Ja, wäre es… also los!
Führe diese Abfrage aus
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'
Das zurückgegebene Feld sql_stm enthält eine SQL-Anweisung für jede Tabelle der Datenbank, die eine Identity hat. Jede dieser Anweisungen behebt das Problem für jeweils eine Tabelle.
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));
...
Führe das einfach in psql oder einem anderen Tool aus, und deine Identities funktionieren wieder korrekt.
Du willst mehr über PostgreSQL wissen? Sieh dir meine Schulungen für Entwickler und Administratoren an (auf Englisch, Italienisch und Spanisch).
Comments