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

Dans l'exemple ci-dessous, le tableau le nom est users et le schéma le nom est public (schéma par défaut), remplacez-le en fonction de vos besoins.

1. Vérifiez l'id. max :

SELECT MAX(id) FROM public.users;

2. Vérifiez la valeur suivante :

SELECT nextval('public."users_id_seq"');

3. Si le max id est supérieure à la next value et le réinitialiser :

SELECT setval('public."users_id_seq"',
  (SELECT MAX(id) FROM public.users)
);

Answer 2

Cette commande permet uniquement de modifier la valeur de la séquence de clés générée automatiquement dans postgresql.

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

A la place du zéro, vous pouvez mettre n'importe quel numéro à partir duquel vous voulez recommencer la séquence.

le nom de la séquence par défaut sera "TableName_FieldName_seq" . Par exemple, si le nom de votre table est "MyTable" et le nom de votre champ est "MyID" alors le nom de votre séquence sera "MyTable_MyID_seq" .

Cette réponse est la même que celle de @murugesanponappan, mais il y a une erreur de syntaxe dans sa solution. Vous ne pouvez pas utiliser la sous-requête. (select max()...) sur alter commande. Ainsi, soit vous devez utiliser une valeur numérique fixe, soit vous devez utiliser une variable à la place de la sous-requête.

Answer 3

Remise à zéro de toutes les séquences, aucune hypothèse sur les noms sauf que la clé primaire de chaque table est "id" :

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
    EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
    (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';

Answer 4

Je suggère cette solution trouvée sur le wiki postgres. Elle met à jour toutes les séquences de vos tables.

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Comment utiliser (à partir du wiki postgres) :

• Enregistrez ceci dans un fichier, disons 'reset.sql'.
• Exécutez le fichier et enregistrez sa sortie d'une manière qui n'inclut pas les en-têtes habituels, puis exécutez cette sortie. Exemple :

Exemple :

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

Article original(également avec correction pour la propriété des séquences) ici

Answer 5

Ces fonctions présentent de nombreux dangers lorsque les noms de séquence, de colonne, de table ou de schéma comportent des caractères bizarres tels que des espaces, des signes de ponctuation, etc. J'ai écrit ceci :

CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS  $$
DECLARE
 tabrelid oid;
 colname name;
 r record;
 newmax bigint;
BEGIN
 FOR tabrelid, colname IN SELECT attrelid, attname
               FROM pg_attribute
              WHERE (attrelid, attnum) IN (
                      SELECT adrelid::regclass,adnum
                        FROM pg_attrdef
                       WHERE oid IN (SELECT objid
                                       FROM pg_depend
                                      WHERE refobjid = $1
                                            AND classid = 'pg_attrdef'::regclass
                                    )
          ) LOOP
      FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
          IF newmax IS NULL OR r.max > newmax THEN
              newmax := r.max;
          END IF;
      END LOOP;
  END LOOP;
  RETURN newmax;
END; $$ ;

Vous pouvez l'appeler pour une seule séquence en lui passant l'OID et il renverra le numéro le plus élevé utilisé par toute table qui a la séquence par défaut ; ou vous pouvez l'exécuter avec une requête comme celle-ci, pour réinitialiser toutes les séquences dans votre base de données :

 select relname, setval(oid, sequence_max_value(oid))
   from pg_class
  where relkind = 'S';

En utilisant un qual différent, vous pouvez réinitialiser uniquement la séquence dans un certain schéma, et ainsi de suite. Par exemple, si vous voulez ajuster les séquences dans le schéma "public" :

select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
  from pg_class, pg_namespace
 where pg_class.relnamespace = pg_namespace.oid and
       nspname = 'public' and
       relkind = 'S';

Notez qu'en raison du fonctionnement de setval(), vous n'avez pas besoin d'ajouter 1 au résultat.

En guise de conclusion, je dois vous avertir que certaines bases de données semblent avoir des liens par défaut avec les séquences d'une manière qui ne permet pas aux catalogues du système d'en avoir une information complète. Cela se produit lorsque vous voyez des choses comme ceci dans le fichier psql's \d :

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |                 Modificadores                  
---------+---------+------------------------------------------------
 a       | integer | default nextval(('foo_a_seq'::text)::regclass)

Notez que l'appel nextval() dans cette clause par défaut a un cast ::text en plus du cast ::regclass. I pensez à Ceci est dû au fait que les bases de données sont pg_dumpées à partir d'anciennes versions de PostgreSQL. Ce qui se passe, c'est que la fonction sequence_max_value() ci-dessus ignorera une telle table. Pour résoudre le problème, vous pouvez redéfinir la clause DEFAULT pour faire référence à la séquence directement sans le cast :

alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE

Ensuite, psql l'affiche correctement :

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |             Modificadores              
---------+---------+----------------------------------------
 a       | integer | default nextval('foo_a_seq'::regclass)

Dès que vous avez corrigé cela, la fonction fonctionne correctement pour cette table ainsi que pour toutes les autres qui pourraient utiliser la même séquence.