Modifier un champs integer en auto increment avec PostgresQL

Ce n'est pas tous les jours que l'on a besoin de modifier un champs en base de donnée qui est simplement de type integer pour qu'il devienne un champs auto incrémenté. Cela nécessite quelques manipulations dans une base de donnée PostgresQL.

Pour commencer, voici le schéma de la table d'exemple de départ.

CREATE TABLE test (
   id uuid NOT NULL,
   legacy_id int,
   value string NOT NULL
);

Dans notre exemple, imaginons que le legacy_id provient d'une autre BDD et qu'il est injecté par le code après coup afin de permettre la synchronisation des données. Mais avec l'autre BDD qui est décommissionnée on choisi de garder ce legacy_id et on souhaite qu'il devienne un champs auto incrémenté.

La première chose à faire est de rendre ce champs non null.

ALTER TABLE test ALTER COLUMN legacy_id SET NOT NULL;

Ensuite, il faut créer une séquence qui va servir de registre pour l'auto incrément.

CREATE SEQUENCE test_legacy_id_seq
  AS integer
  START WITH XXX
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

START WITH permet de préciser à quel numéro la séquence commence. Si la table contient déjà des données, il faut donc chercher la valeur la plus grande dans la colonne et l'incrémenter de 1 pour avoir le numéro de départ de la séquence.

Ensuite, il faut attribuer la séquence aux champs puis modifier le champs pour qu'il utilise la séquence.

ALTER SEQUENCE test_legacy_id_seq OWNED BY test.legacy_id;
ALTER TABLE test ALTER COLUMN legacy_id SET DEFAULT nextval('test_legacy_id_seq'::regclass);
 

Le champs legacy_id est désormais prêt pour s'auto incrémenter à chaque nouvelle insertion dans la table.

En cas de besoin de retour en arrière, c'est assez simple:

ALTER TABLE test ALTER COLUMN legacy_id DROP DEFAULT;
ALTER TABLE test ALTER COLUMN legacy_id DROP NOT NULL;
DROP SEQUENCE test_legacy_id_seq;