Comment réinitialiser une séquence dans postgres et remplir la colonne id avec de nouvelles données ?

Question

J'ai une table avec plus d'un million de lignes. Je dois réinitialiser la séquence et réaffecter la colonne id avec de nouvelles valeurs (1, 2, 3, 4... etc...). Existe-t-il un moyen simple de le faire ?

Answer 1

Si vous utilisez pgAdmin3, développez 'Séquences', cliquez avec le bouton droit de la souris sur une séquence, allez dans 'Propriétés', et dans l'onglet 'Définition', changez la 'Valeur actuelle' à la valeur que vous voulez. Il n'y a pas besoin d'une requête.

Answer 2

Inspiré par les autres réponses ici, j'ai créé une fonction SQL pour faire une migration de séquence. T

J'explique aquí comment j'ai utilisé cette fonction dans une migration de deux bases de données avec le même schéma mais des valeurs différentes dans une seule base de données.

Tout d'abord, la fonction (qui imprime les commandes SQL générées de manière à ce qu'elle soit ce qui se passe réellement) :

CREATE OR REPLACE FUNCTION migrate_pkey_sequence
  ( arg_table      text
  , arg_column     text
  , arg_sequence   text
  , arg_next_value bigint  -- Must be >= 1
  )
RETURNS int AS $$
DECLARE
  result int;
  curr_value bigint = arg_next_value - 1;
  update_column1 text := format
    ( 'UPDATE %I SET %I = nextval(%L) + %s'
    , arg_table
    , arg_column
    , arg_sequence
    , curr_value
    );
  alter_sequence text := format
    ( 'ALTER SEQUENCE %I RESTART WITH %s'
    , arg_sequence
    , arg_next_value
    );
  update_column2 text := format
    ( 'UPDATE %I SET %I = DEFAULT'
    , arg_table
    , arg_column
    );
  select_max_column text := format
    ( 'SELECT coalesce(max(%I), %s) + 1 AS nextval FROM %I'
    , arg_column
    , curr_value
    , arg_table
    );
BEGIN
  -- Print the SQL command before executing it.
  RAISE INFO '%', update_column1;
  EXECUTE update_column1;
  RAISE INFO '%', alter_sequence;
  EXECUTE alter_sequence;
  RAISE INFO '%', update_column2;
  EXECUTE update_column2;
  EXECUTE select_max_column INTO result;
  RETURN result;
END $$ LANGUAGE plpgsql;

La fonction migrate_pkey_sequence prend les arguments suivants :

1. arg_table : nom de la table (par exemple 'example' )
2. arg_column : nom de la colonne de la clé primaire (ex. 'id' )
3. arg_sequence : nom de la séquence (par exemple 'example_id_seq' )
4. arg_next_value Valeur suivante pour la colonne après la migration

Il effectue les opérations suivantes :

1. Déplacez les valeurs de la clé primaire vers une plage libre. Je suppose que nextval('example_id_seq') suit max(id) et que la séquence commence avec 1. Cela permet également de traiter le cas où arg_next_value > max(id) .
2. Déplacer les valeurs de la clé primaire vers la plage contiguë commençant par arg_next_value . L'ordre des valeurs des clés est préservé mais les trous dans la gamme ne sont pas préservés.
3. Imprimez la prochaine valeur qui suivrait dans la séquence. Ceci est utile si vous voulez migrer les colonnes d'une autre table et les fusionner avec celle-ci.

Pour démontrer, nous utilisons une séquence et un tableau définis comme suit (par exemple en utilisant psql ) :

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

# CREATE TABLE example
  ( id bigint NOT NULL DEFAULT nextval('example_id_seq'::regclass)
  );

Ensuite, nous insérons certaines valeurs (en commençant, par exemple, par 3) :

# ALTER SEQUENCE example_id_seq RESTART WITH 3;
# INSERT INTO example VALUES (DEFAULT), (DEFAULT), (DEFAULT);
-- id: 3, 4, 5

Enfin, nous migrons le example.id valeurs pour commencer par 1.

# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
INFO:  00000: UPDATE example SET id = nextval('example_id_seq') + 0
INFO:  00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
INFO:  00000: UPDATE example SET id = DEFAULT
 migrate_pkey_sequence
-----------------------
                     4
(1 row)

Le résultat :

# SELECT * FROM example;
 id
----
  1
  2
  3
(3 rows)

Answer 3

Même si la colonne d'auto-incrémentation n'est pas PK (dans cet exemple, elle s'appelle seq, c'est-à-dire séquence), vous pouvez y parvenir avec un déclencheur :

DROP TABLE IF EXISTS devops_guide CASCADE ;

SELECT 'create the "devops_guide" table'
;
   CREATE TABLE devops_guide (
      guid           UUID NOT NULL DEFAULT gen_random_uuid()
    , level          integer NULL
    , seq            integer NOT NULL DEFAULT 1
    , name           varchar (200) NOT NULL DEFAULT 'name ...'
    , description    text NULL
    , CONSTRAINT pk_devops_guide_guid PRIMARY KEY (guid)
    ) WITH (
      OIDS=FALSE
    );

-- START trg_devops_guide_set_all_seq
CREATE OR REPLACE FUNCTION fnc_devops_guide_set_all_seq()
    RETURNS TRIGGER
    AS $$
       BEGIN
         UPDATE devops_guide SET seq=col_serial FROM
         (SELECT guid, row_number() OVER ( ORDER BY seq) AS col_serial FROM devops_guide ORDER BY seq) AS tmp_devops_guide
         WHERE devops_guide.guid=tmp_devops_guide.guid;

         RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;

 CREATE TRIGGER trg_devops_guide_set_all_seq
  AFTER UPDATE OR DELETE ON devops_guide
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 1)
  EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();

Answer 4

Dans mon cas, les séquences de toutes les tables avaient été corrompues après avoir importé le mauvais fichier sql. SELECT nextval('table_name_id_seq'); retournait une valeur inférieure à la valeur maximale de la id colonne. J'ai donc créé sql script pour récupérer toutes les séquences pour chaque table :

DO
$$
DECLARE
   rec  record;
   table_seq text;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      table_seq := rec.tablename || '_id_seq';

      RAISE NOTICE '%', table_seq;
      EXECUTE format(E'SELECT setval(\'%I\', COALESCE((SELECT MAX(id)+1 FROM %I), 1), false);',
            table_seq, rec.tablename);

   END LOOP;
END
$$;

Remarque : Si vous n'avez pas le id dans l'une de vos tables, vous devez mettre à jour la logique ou les traiter séparément en fonction de la logique ci-dessus.