Ajout d'une nouvelle valeur à un type ENUM existant

Question

J'ai une colonne de table qui utilise un enum type. Je souhaite mettre à jour ce enum pour avoir une valeur supplémentaire possible. Je ne veux pas supprimer les valeurs existantes, mais simplement ajouter la nouvelle valeur. Quelle est la façon la plus simple de procéder ?

Answer 1

Juste au cas où, si vous utilisez Rails et que vous avez plusieurs déclarations que vous devrez exécuter une par une, comme :

execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'YYY';"
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'ZZZ';"

Answer 2

Depuis Postgres 9.1 Documentation :

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

Exemple :

ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'

Answer 3

Avis de non-responsabilité : Je n'ai pas essayé cette solution, elle peut donc ne pas fonctionner ;-)

Vous devriez regarder pg_enum . Si vous voulez seulement changer l'étiquette d'un ENUM existant, un simple UPDATE fera l'affaire.

Pour ajouter une nouvelle valeur ENUM :

• Insérez d'abord la nouvelle valeur dans pg_enum . Si la nouvelle valeur doit être la dernière, c'est fini.
• Si ce n'est pas le cas (vous avez besoin d'une nouvelle valeur ENUM entre les valeurs existantes), vous devrez mettre à jour chaque valeur distincte dans votre table, en allant de la plus haute à la plus basse...
• Il vous suffira alors de les renommer dans le fichier pg_enum dans l'ordre inverse.

Illustration
Vous disposez de l'ensemble d'étiquettes suivant :

ENUM ('enum1', 'enum2', 'enum3')

et que vous voulez obtenir :

ENUM ('enum1', 'enum1b', 'enum2', 'enum3')

alors :

INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';

alors :

UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;

Et ainsi de suite...

Answer 4

Je n'arrive pas à poster un commentaire, donc je vais juste dire que la mise à jour de pg_enum fonctionne dans Postgres 8.4 . Pour la façon dont nos enums sont configurés, j'ai ajouté de nouvelles valeurs aux types d'enum existants via :

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE
    typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';

C'est un peu effrayant, mais c'est logique compte tenu de la façon dont Postgres stocke ses données.

Answer 5

La mise à jour de pg_enum fonctionne, tout comme l'astuce de la colonne intermédiaire soulignée ci-dessus. On peut également utiliser la magie USING pour changer directement le type de la colonne :

CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');

ALTER TABLE foo ALTER COLUMN bar TYPE varchar;

DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');

ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;

Tant que vous n'avez pas de fonctions qui requièrent ou retournent explicitement cet enum, vous êtes bon. (pgsql se plaindra lorsque vous abandonnerez le type s'il y en a).

Notez également que PG9.1 introduit une instruction ALTER TYPE, qui fonctionnera sur les enums :

http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html