Comment réinitialiser la séquence de clés primaires de postgres quand elle tombe désynchronisée?

J'ai rencontré le problème que ma séquence de clés primaires n'est pas synchronisée avec mes lignes de table.

C'est-à-dire que lorsque j'insère une nouvelle ligne, j'obtiens une erreur de clé en double car la séquence impliquée dans le type de données série renvoie un nombre qui existe déjà.

Il semble être causé par import / restaure ne pas maintenir la séquence correctement.

412
demandé sur a_horse_with_no_name 2008-10-28 21:08:21

25 réponses

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Source - Ruby Forum

588
répondu meleyal 2017-09-20 02:03:22

pg_get_serial_sequence peut être utilisé pour éviter toute erreur d'hypothèses sur le nom de la séquence. Cela réinitialise la séquence en un seul coup:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

Ou plus concise:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

Cependant, ce formulaire ne peut pas gérer correctement les tables vides, puisque max (id) est null, et vous ne pouvez pas non plus définir Val 0 car il serait hors de portée de la séquence. Une solution de contournement pour cela est de recourir à la syntaxe ALTER SEQUENCE, c'est-à-dire

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

Mais ALTER SEQUENCE est d'une utilité limitée car le nom la valeur de redémarrage ne peut pas être des expressions.

Il semble que la meilleure solution tout usage est d'appeler setval avec false comme 3ème paramètre, nous permettant de spécifier la "valeur suivante à utiliser":

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

Cela coche toutes mes cases:

  1. évite le codage en dur du nom de séquence réel
  2. gère correctement les tables vides
  3. gère les tables avec des données existantes et ne laisse pas trou dans la séquence

Enfin, notez que pg_get_serial_sequence ne fonctionne que si le la séquence appartient à la colonne. Ce sera le cas si la colonne incrémentée a été définie comme un type serial, mais si la séquence a été ajoutée manuellement, il est nécessaire de s'assurer que ALTER SEQUENCE .. OWNED BY est également effectuée.

C'est-à-dire si le type serial a été utilisé pour la création de la table, tout devrait fonctionner:

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);

SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

Mais si les séquences ont été ajoutées manuellement:

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);

CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);

ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence

SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
165
répondu tardate 2015-07-10 17:51:30

Cela réinitialisera toutes les séquences du public en ne faisant aucune hypothèse sur les noms de table ou de colonne. Testé sur la version 8.4

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS 

    $body$  
      DECLARE 
      BEGIN 

      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';



      END;  

    $body$  LANGUAGE 'plpgsql';


    select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';
47
répondu djsnowsill 2010-11-04 21:03:13

Le la plus courte et la plus rapide façon:

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id être le serial colonne de la table tbl, dessin de la séquence tbl_tbl_id_seq (qui est le nom automatique par défaut).

Si vous ne connaissez pas le nom de la séquence (qui n'ont pas à être en forme par défaut), utilisez pg_get_serial_sequence():

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

Il n'y a pas de hors-par-une erreur ici. par documentation:

La forme à deux paramètres définit le champ last_value de la séquence valeur spécifiée et définit son champ is_called sur true, ce qui suivant nextval avancera la séquence avant de renvoyer une valeur.

Gras emphase mine.

Concurrence

Il n'y a pas encore de défense contre l'activité de séquence simultanée ou les Écritures dans la table dans les requêtes ci-dessus. Si c'est pertinent, vous pourriez verrouillage de la table en mode exclusif. Il garde les transactions simultanées de écrire un nombre plus élevé pendant que vous essayez de vous synchroniser. (Il bloque également temporairement les Écritures inoffensives ne pas jouer avec le nombre maximum.)

Mais il ne prend pas en compte les clients qui peuvent avoir récupéré des numéros de séquence à l'avance sans aucun verrou sur la table principale, encore (ce qui peut arriver). Pour permettre cela aussi, seulement augmente la valeur actuelle de la séquence, ne la diminue jamais. Cela peut sembler paranoïaque, mais c'est en accord avec la nature des séquences et de la défense contre les problèmes de concurrence.

BEGIN;

LOCK TABLE tbl IN EXCLUSIVE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq);

COMMIT;
39
répondu Erwin Brandstetter 2017-12-22 04:46:13

Modifier la séquence sequence_name redémarrer avec (SELECT max (id) FROM table_name); Ne fonctionne pas.

Copié à partir de la réponse @ tardate:

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
35
répondu tardate 2014-12-14 00:23:46

Cette commande ne modifie que la valeur de la séquence de clés générée automatiquement dans postgresql

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

À la place de zéro, vous pouvez mettre n'importe quel numéro à partir duquel vous souhaitez redémarrer la séquence.

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

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

16
répondu Haider Ali Wajihi 2015-11-20 13:40:35

Réinitialise toutes les séquences, pas d'hypothèses 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%';
15
répondu EB. 2013-01-31 19:00:43

Ces fonctions sont dangereuses lorsque les noms de séquence, de colonne, de table ou de schéma ont des caractères amusants 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 retournera le nombre 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 avertir que certaines bases de données semblent avoir des liens par défaut vers des séquences de manière à ne pas laisser les catalogues système en avoir toutes les informations. Cela arrive quand vous voyez des choses comme ça dans \ d de psql:

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:: text cast en plus du:: regclass cast. Je pense que cela est dû au fait que les bases de données sont pg_dump'ed à partir d'anciennes versions de PostgreSQL. Ce qui se passera, 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 la distribution:

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

Alors 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, le fonction fonctionne correctement pour cette table ainsi que tous les autres qui pourraient utiliser la même séquence.

12
répondu alvherre 2011-05-09 22:56:08

Réinitialiser toutes les séquences du public

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
$body$  
  DECLARE 
  BEGIN 
  EXECUTE 'SELECT setval( ''' 
  || tablename  
  || '_id_seq'', ' 
  || '(SELECT id + 1 FROM "' 
  || tablename  
  || '" ORDER BY id DESC LIMIT 1), false)';  
  END;  
$body$  LANGUAGE 'plpgsql';

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';
6
répondu user457226 2010-09-24 11:40:30

MA version utilise la première, avec une vérification d'erreur...

BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
 PERFORM 1
 FROM information_schema.sequences
 WHERE
  sequence_schema = _table_schema AND
  sequence_name = _sequence_name;
 IF FOUND THEN
  EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
 ELSE
  RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
 END IF;
END; 
$BODY$
 LANGUAGE 'plpgsql';

SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;
5
répondu Daniel Cristian Cruz 2011-09-13 18:22:39

Mettre tout cela ensemble

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

Corrigera la séquence ' id' de la table donnée (comme généralement nécessaire avec django par exemple).

5
répondu Antony Hatchkins 2012-11-09 12:26:09

Quelques réponses vraiment hardcore ici, je suppose que c'était vraiment mauvais au moment où cela a été demandé, car beaucoup de réponses d'ici ne fonctionnent pas pour la version 9.3. La documentation depuis la version 8.0 fournit une réponse à cette question:

SELECT setval('serial', max(id)) FROM distributors;

Aussi, si vous devez vous occuper des noms de séquence sensibles à la casse, c'est comme ça que vous le faites:

SELECT setval('"Serial"', max(id)) FROM distributors;
5
répondu Ian Bytchek 2014-11-07 12:48:07

Je suggère cette solution trouvée sur le wiki postgres. Il 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, dites ' 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 correctif pour la propriété de la séquence) ici

5
répondu Pietro 2016-11-28 13:54:34

Avant je n'avais pas encore essayé le code: dans ce qui suit je poste la version pour le code sql pour les solutions Klaus et user457226 qui a fonctionné sur mon pc [Postgres 8.3], avec juste quelques petits ajustements pour celui de Klaus et de ma version pour celui de l'utilisateur457226.

Solution Klaus:

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      constraint_column_usage.table_name as tablename,
      constraint_column_usage.table_name as tablename, 
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

User457226 solution:

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
  DECLARE seqname character varying;
          c integer;
  BEGIN
    select tablename || '_' || columnname || '_seq' into seqname;
    EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
    if c is null then c = 0; end if;
    c = c+1; --because of substitution of setval with "alter sequence"
    --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
    EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
    RETURN nextval(seqname)-1;
  END;
$body$ LANGUAGE 'plpgsql';

select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
       reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
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 || '_seq'
where sequence_schema='public';
4
répondu mauro 2012-01-03 17:45:31

Revérifier toute la séquence dans la fonction de schéma public

CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
  _table_name VARCHAR;
  _column_name VARCHAR;  
  _sequence_name VARCHAR;
BEGIN
  FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
    FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
        SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
        IF _sequence_name IS NOT NULL THEN 
            EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
        END IF;
    END LOOP;   
  END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
4
répondu anydasa 2015-06-02 10:15:43

Encore un autre plpgsql - réinitialise uniquement si max(att) > then lastval

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

Commentant également la ligne {[2] } donnera la liste, ne réinitialisant pas réellement la valeur

4
répondu Vao Tsun 2017-10-23 09:37:41

Pour redémarrer toute la séquence à 1 Utilisez:

-- 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';
3
répondu Stanislav Yanev 2016-09-01 14:53:11

Ce problème se produit avec moi lorsque vous utilisez entity framework pour créer la base de données, puis semez la base de données avec des données initiales, ce qui rend la séquence non appariée.

Je L'ai résolu en créant un script à exécuter après l'ensemencement de la base de données:

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$
3
répondu Yehia Amer 2018-04-08 15:22:44

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),

Supprime le id (clé primaire) des données initiales

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

Cela permet de synchroniser la séquence Postgres !

2
répondu user 2014-05-23 13:52:00

Cette réponse est une copie de mauro.

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null 
      ORDER BY sequencename
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();
2
répondu Baldiry 2016-06-16 19:05:14

J'ai passé une heure à essayer d'obtenir la réponse de djsnowsill pour travailler avec une base de données en utilisant des tables et des colonnes de cas mixtes, puis j'ai finalement trébuché sur la solution grâce à un commentaire de Manuel Darveau, mais je pensais que je pourrais le rendre un peu plus clair pour tout le monde:

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

SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
FROM information_schema.columns WHERE column_default like 'nextval%';

Cela a l'avantage de:

  • ne pas supposer que la colonne ID est orthographiée d'une manière particulière.
  • ne pas supposer que toutes les tables ont une séquence.
  • Fonctionne pour les noms de table/colonne à casse mixte.
  • en utilisant format pour être plus concis.

À expliquer, le problème était que pg_get_serial_sequence prend chaînes à quoi vous faites allusion, donc si vous ne:

"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!

Ceci est réalisé en utilisant ''%1$I'' dans la chaîne de format, '' fait une apostrophe 1$ signifie premier arg, et I signifie entre guillemets

2
répondu Nintynuts 2017-07-05 17:00:21

La réponse Klaus est la plus utile, execpt pour un peu manquer: vous avoir à 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 user457226 pour le cas où un nom de colonne intéressé n'est pas 'ID'.

1
répondu mauro 2011-03-09 21:05:52

Hack laid pour le réparer en utilisant de la magie shell, pas une excellente solution mais pourrait inspirer les autres avec des problèmes similaires:)

pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -
1
répondu Wolph 2013-05-27 22:38:33

Essayez reindex.

Mise à jour: comme indiqué dans les commentaires, c'était en réponse à la question initiale.

0
répondu Hank Gay 2011-02-10 17:22:27

SELECT setval... fait JDBC bork, alors voici une façon compatible Java de le faire:

-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';
0
répondu mcandre 2015-05-21 17:47:31