Dev Faq
0
votes

Comment bulk update Séquence ID PostgreSQL pour toutes les tables

J'ai importé le fichier Postgres SQL sur mon serveur à l'aide de Tableflus (client SQL), mais après que j'insère une nouvelle ligne, j'ai eu une erreur comme ceci:

sqlstate [23505]: Violation unique: 7 Erreur: La valeur clé en double enfreint la contrainte unique \ "users_pkey \" Détail: Touche (ID) = (1) existe déjà

Je sais que cela causé par la valeur de séquence est 0 et doit être mis à jour par code ci-dessous: xxx

mais il faut tellement de temps si je dois écrire à toutes les séquences de table (peut-être des centaines de séquences) une à une. Alors comment mettre à jour toutes les séquences à la fois?

0 commentaires

3 Réponses :

0
votes

Vous ne pouvez pas mettre à jour toute la séquence ensemble, chaque séquence peut contenir différentes valeurs relatives à chaque tableau. Vous devez prendre la valeur max de chaque table et la mettre à jour. 

SELECT setval(_sequence_name_, max(id)) FROM _table_name_;

0 commentaires
2
votes

En supposant que toutes les séquences utilisées appartiennent aux colonnes respectives, par ex. À travers un Serial ou Identity , vous pouvez l'utiliser pour réinitialiser toutes les séquences (possédées) de la base de données actuelle. xxx

La première partie sélectionne toutes les séquences appartenant à une colonne. La deuxième partie utilise ensuite Query_to_xml () pour obtenir la valeur max de la colonne associée à cette séquence. Et le choix final s'applique ensuite à la valeur maximale à chaque séquence à l'aide de setval () .

Vous voudrez peut-être exécuter cela sans le setval () appel d'abord pour voir si tout est comme si vous en avez besoin.

7 commentaires

J'ai testé sur ma base de données originale, vue fonctionnée, c'est une liste de spectacle de ma valeur de séquence et de la nouvelle valeur, mais j'ai essayé sur le serveur que j'ai importé, il affiche 0 rangées, pourquoi ??

Je pense que c'est causé par pg_get_serial_sessence n'est pas défini après l'importation dans le nouveau serveur, je exécute SELECT * de (Sélectionnez Table_Schema, Nom de table, Column_Name, PG_GET_Serial_Suence (Format (% I.% I ', Table_Schema, Nom de table), Noms de colonne) Comme Col_Sréence de l'information_schema.columns où table_schema non in ('pg_catalog', 'informations_schema')) t Où col_schequence n'est pas null renvoyer zéro dans nouveau serveur, mais il est renvoyé avec des données dans le serveur d'origine

pg_get_serial_Suence est une fonction intégrée qu'il ne peut pas "non défini". Si cette requête ne renvoie rien, vous avez perdu la propriété entre la colonne et la séquence pour une raison quelconque pendant votre "importation".

Je vois , je suis toujours en cours de contournement à l'aide de la requête colonne_default ilike 'nextval (%' , je veux modifier votre col_sreence de pg_get_serial_fuence fonction à colonne_default. Pouvez-vous maintenant extraire col_sessence à partir de colonne_default avec la valeur est "NextVal ('users_id_seq' :: regclass)". Donc, j'ai besoin d'obtenir users_id_seq à partir de NextVal ('users_id_seq' :: regclass)

Vous devriez plutôt corriger votre exportation / importation puis pirater avec la valeur par défaut. Si vous avez vraiment besoin de cela, veuillez poser une nouvelle question.

J'ai fait exporté / importation à l'aide de mon client SQL (Tableflus), alors je pense que rien n'est faux, mais ça va, merci. Votre réponse est utile. Je vais essayer d'abord pour vous défier, si je suis bloqué, je vais créer une nouvelle question.

Je dirais qu'il y a est Quelque chose ne va pas là-bas, apparemment TablePlus ne génère pas le nécessaire appartenant à pour les séquences. Utilisez pg_dump et comparez la sortie

0
votes

comme @a_horse_with_no_name La réponse ne fonctionne pas dans mon cas (peut-être que quelque chose ne va pas avec le fichier SQL), j'ai modifié la requête comme ci-dessous qui fonctionne dans mon cas. 

with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           replace(replace(replace(column_default, '::regclass)', ''), '''', ''), 'nextval(', 'public.') as col_sequence
    from information_schema.columns
    where table_schema not in ('pg_catalog', 'information_schema') and column_default ILIKE 'nextval(%'
  ) t
  where col_sequence is not null
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
) 
select table_schema, 
       table_name, 
       column_name, 
       col_sequence,
       coalesce(max_val, 0) as max_val,
       setval(col_sequence, coalesce(max_val, 1)) --<< this will change the sequence
from maxvals;

1 commentaires

Si vous devez utiliser le colonne_default au lieu de pg_get_serial_sessence () Ceci signifie que vos colonnes ne sont ni définies comme série ni comme identité . Je vous recommande vivement de rechercher pourquoi ce n'est pas le cas, car vous devriez vraiment utiliser cela.

Articles qui pourrait vous intéresser :

Laravel whereRaw - comment échapper à un '?'
Pgadmin ne se charge pas
Interroger les données de PostgreSQL à partir de deux tables
Erreur: impossible d'insérer plusieurs commandes dans une instruction préparée