Comment réinitialiser la séquence de clés primaires de postgres lorsqu'elle n'est plus synchronisée ?

Question

J'ai rencontré le problème suivant : la séquence de ma clé primaire n'est pas synchronisée avec les lignes de ma table.

En d'autres termes, lorsque j'insère une nouvelle ligne, j'obtiens une erreur de clé dupliquée car la séquence impliquée dans le type de données série renvoie un numéro qui existe déjà.

Il semble être causé par l'importation/restauration qui ne maintient pas la séquence correctement.

Answer 1

Si vous voyez cette erreur lorsque vous chargez des données SQL personnalisées pour l'initialisation, une autre façon d'éviter cela est :

Au lieu d'écrire :

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

Retirer le id (clé primaire) à partir des données initiales

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

Cela permet de garder la séquence Postgres synchronisée !

Answer 2

Pour redémarrer toutes les séquences à 1 utilisation :

-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
    relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$

DECLARE

BEGIN
    EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$

LANGUAGE 'plpgsql';

-- Use Function
SELECT 
    relname
    ,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';

Answer 3

select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
from (
       SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
       from information_schema.columns
       where column_default ilike 'nextval%'
     ) as sequense_query

Answer 4

Il suffit d'exécuter la commande ci-dessous :

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

Answer 5

La réponse de Klaus est la plus utile, à l'exception d'une petite erreur : vous devez ajouter DISTINCT dans l'instruction select. il faut ajouter DISTINCT dans l'instruction select.

Cependant, si vous êtes sûr qu'aucun nom de table+colonne ne peut être équivalent pour deux tables différentes, vous pouvez également utiliser :

select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
       reset_sequence(split_part(sequence_name, '_id_seq',1))
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
where sequence_schema='public';

qui est une extension de la solution de l'utilisateur 457226 pour le cas où le nom d'une colonne intéressée n'est pas 'ID'.