Adding a new value to an existing ENUM Type

Question

I have a table column that uses an enum type  I wish to update that enum type to have an additional possible value  I don t want to delete any existing values  just add the new value  What is the simplest way to do this

User · Answer

When using Navicat you can go to types  under view -  others -  types  - get the design view of the type - and click the  add label  button

User · Answer

PostgreSQL 9 1 introduces ability to ALTER Enum types   ALTER TYPE enum type ADD VALUE  new value   -- appends to list ALTER TYPE enum type ADD VALUE  new value  BEFORE  old value   ALTER TYPE enum type ADD VALUE  new value  AFTER  old value

User · Answer

For those looking for an in-transaction solution  the following seems to work   Instead of an ENUM  a DOMAIN shall be used on type TEXT with a constraint checking that the value is within the specified list of allowed values  as suggested by some comments   The only problem is that no constraint can be added  and thus neither modified  to a domain if it is used by any composite type  the docs merely says this  should eventually be improved    Such a restriction may be worked around  however  using a constraint calling a function  as follows   START TRANSACTION   CREATE FUNCTION test is allowed label lbl TEXT  RETURNS BOOL AS  function      SELECT lbl IN   one    two    three     function  LANGUAGE SQL IMMUTABLE   CREATE DOMAIN test domain AS TEXT CONSTRAINT val check CHECK  test is allowed label value     CREATE TYPE test composite AS  num INT  word test domain    CREATE TABLE test table  val test composite   INSERT INTO test table  val  VALUES   1   one    test composite     3   three    test composite   -- INSERT INTO test table  val  VALUES   4   four    test composite   -- restricted by the CHECK constraint  CREATE VIEW test view AS SELECT   FROM test table  -- just to show that the views using the type work as expected  CREATE OR REPLACE FUNCTION test is allowed label lbl TEXT  RETURNS BOOL AS  function      SELECT lbl IN   one    two    three    four     function  LANGUAGE SQL IMMUTABLE   INSERT INTO test table  val  VALUES   4   four    test composite   -- allowed by the new effective definition of the constraint  SELECT   FROM test view   CREATE OR REPLACE FUNCTION test is allowed label lbl TEXT  RETURNS BOOL AS  function      SELECT lbl IN   one    two    three     function  LANGUAGE SQL IMMUTABLE   -- INSERT INTO test table  val  VALUES   4   four    test composite   -- restricted by the CHECK constraint  again  SELECT   FROM test view  -- note the view lists the restricted value  four  as no checks are made on existing data  DROP VIEW test view  DROP TABLE test table  DROP TYPE test composite  DROP DOMAIN test domain  DROP FUNCTION test is allowed label TEXT    COMMIT    Previously  I used a solution similar to the accepted answer  but it is far from being good once views or functions or composite types  and especially views using other views using the modified ENUMs     are considered  The solution proposed in this answer seems to work under any conditions   The only disadvantage is that no checks are performed on existing data when some allowed values are removed  which might be acceptable  especially for this question    A call to ALTER DOMAIN test domain VALIDATE CONSTRAINT val check ends up with the same error as adding a new constraint to the domain used by a composite type  unfortunately    Note that a slight modification such as CHECK  value   ANY get allowed values      where get allowed values   function returned the list of allowed values  would not work - which is quite strange  so I hope the solution proposed above works reliably  it does for me  so far       it works  actually - it was my error

User · Answer

A possible solution is the following   precondition is  that there are not conflicts in the used enum values   e g  when removing an enum value  be sure that this value is not used anymore    -- rename the old enum alter type my enum rename to my enum    -- create the new enum create type my enum as enum   value1    value2    value3     -- alter all you enum columns alter table my table   alter column my column type my enum using my column  text  my enum   -- drop the old enum drop type my enum      Also in this way the column order will not be changed

User · Answer

If you are using Postgres 12 you can just run ALTER TYPE     ADD VALUE inside of transaction  documentation    If ALTER TYPE     ADD VALUE  the form that adds a new value to an enum type  is executed inside a transaction block  the new value cannot be used until after the transaction has been committed   So no hacks needed in migrations  UPD  here is an example  thanks to Nick for it  ALTER TYPE enum type ADD VALUE  new value

User · Answer

Complementing  Dariusz 1  For Rails 4 2 1  there s this doc section      Transactional Migrations  If the database adapter supports DDL transactions  all migrations will     automatically be wrapped in a transaction  There are queries that you     can t execute inside a transaction though  and for these situations     you can turn the automatic transactions off   class ChangeEnum  lt  ActiveRecord  Migration   disable ddl transaction     def up     execute  ALTER TYPE model size ADD VALUE  new value     end end

User · Answer

I don t know if have other option but we can drop the value using   select oid from pg type where typname    fase    select   from pg enum where enumtypid   24773   select   from pg enum where enumtypid   24773 and enumsortorder   6  delete from pg enum where enumtypid   24773 and enumsortorder   6

User · Answer

I can t seem to post a comment  so I ll just say that updating pg enum works in Postgres 8 4    For the way our enums are set up  I ve added new values to existing enum types via   INSERT INTO pg enum  enumtypid  enumlabel    SELECT typelem   NEWENUM  FROM pg type WHERE     typname     ENUMNAME WITH LEADING UNDERSCORE     It s a little scary  but it makes sense given the way Postgres actually stores its data

User · Answer

Updating pg enum works  as does the intermediary column trick highlighted above  One can also use USING magic to change the column s type directly   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    As long as you ve no functions that explicitly require or return that enum  you re good   pgsql will complain when you drop the type if there are    Also  note that PG9 1 is introducing an ALTER TYPE statement  which will work on enums   http   developer postgresql org pgdocs postgres release-9-1-alpha html

User · Answer

As discussed above  ALTER command cannot be written inside a transaction  The suggested way is to insert into the pg enum table directly  by retrieving the typelem from pg type table and calculating the next enumsortorder number   Following is the code that I use   Checks if duplicate value exists before inserting  constraint between enumtypid and enumlabel name   INSERT INTO pg enum  enumtypid  enumlabel  enumsortorder      SELECT typelem       NEW ENUM VALUE        SELECT MAX enumsortorder    1          FROM pg enum e         JOIN pg type p         ON p typelem   e enumtypid         WHERE p typname     mytypename            FROM pg type p     WHERE p typname     mytypename      AND NOT EXISTS           SELECT   FROM          pg enum e         JOIN pg type p         ON p typelem   e enumtypid         WHERE e enumlabel    NEW ENUM VALUE          AND p typname     mytypename          Note that your type name is prepended with an underscore in the pg type table  Also  the typname needs to be all lowercase in the where clause   Now this can be written safely into your db migrate script

User · Answer

Here is a more general but a rather fast-working solution  which apart from changing the type itself updates all columns in the database using it  The method can be applied even if a new version of ENUM is different by more than one label or misses some of the original ones  The code below replaces my schema my type AS ENUM   a    b    c   with ENUM   a    b    d    e     CREATE OR REPLACE FUNCTION tmp   RETURNS BOOLEAN AS  BODY   DECLARE     item RECORD   BEGIN      -- 1  create new type in replacement to my type     CREATE TYPE my schema my type NEW         AS ENUM   a    b    d    e         -- 2  select all columns in the db that have type my type     FOR item IN         SELECT table schema  table name  column name  udt schema  udt name             FROM information schema columns             WHERE                 udt schema      my schema              AND udt name        my type      LOOP         -- 3  Change the type of every column using my type to my type NEW         EXECUTE               ALTER TABLE      item table schema           item table name               ALTER COLUMN      item column name               TYPE my schema my type NEW                USING      item column name       text  my schema my type NEW        END LOOP       -- 4  Delete an old version of the type     DROP TYPE my schema my type       -- 5  Remove  NEW suffix from the new type     ALTER TYPE my schema my type NEW         RENAME TO my type       RETURN true   END  BODY  LANGUAGE  plpgsql    SELECT   FROM tmp    DROP FUNCTION tmp      The whole process will run fairly quickly  because if the order of labels persists  no actual change of data will happen  I applied the method on 5 tables using my type and having 50 000-70 000 rows in each  and the whole process took just 10 seconds   Of course  the function will return an exception in case if labels that are missing in the new version of the ENUM are used somewhere in the data  but in such situation something should be done beforehand anyway

User · Answer

NOTE if you re using PostgreSQL 9 1 or later  and you are ok with making changes outside of a transaction  see this answer for a simpler approach     I had the same problem few days ago and found this post  So my answer can be helpful for someone who is looking for solution      If you have only one or two columns which use the enum type you want to change  you can try this  Also you can change the order of values in the new type   -- 1  rename the enum type you want to change alter type some enum type rename to  some enum type  -- 2  create new type create type some enum type as enum   old    values    and    new    ones    -- 3  rename column s  which uses our enum type alter table some table rename column some column to  some column  -- 4  add new column of new type alter table some table add some column some enum type not null default  new   -- 5  copy values to the new column update some table set some column    some column  text  some enum type  -- 6  remove old column and type alter table some table drop column  some column  drop type  some enum type    3-6 should be repeated if there is more than 1 column

User · Answer

just in case  if you are using Rails and you have several statements you will need to execute one by one  like   execute  ALTER TYPE XXX ADD VALUE IF NOT EXISTS  YYY    execute  ALTER TYPE XXX ADD VALUE IF NOT EXISTS  ZZZ

User · Answer

Simplest  get rid of enums  They are not easily modifiable  and thus should very rarely be used

User · Answer

If you fall into situation when you should add enum values in transaction  f e  execute it in flyway migration on ALTER TYPE statement you will be get error ERROR  ALTER TYPE     ADD cannot run inside a transaction block  see flyway issue  350  you could add such values into pg enum directly as workaround  type egais units is name of target enum    INSERT INTO pg enum  enumtypid  enumlabel  enumsortorder      SELECT  type egais units   regtype  oid   NEW ENUM VALUE     SELECT MAX enumsortorder    1 FROM pg enum WHERE enumtypid    type egais units   regtype

User · Answer

Can t add a comment to the appropriate place  but ALTER TABLE foo ALTER COLUMN bar TYPE new enum type USING bar  text  new enum type with a default on the column failed   I had to   ALTER table ALTER COLUMN bar DROP DEFAULT   and then it worked

User · Answer

From Postgres 9 1 Documentation   ALTER TYPE name ADD VALUE new enum value     BEFORE   AFTER   existing enum value     Example   ALTER TYPE user status ADD VALUE  PROVISIONAL  AFTER  NORMAL

User · Answer

Disclaimer  I haven t tried this solution  so it might not work  -   You should be looking at pg enum  If you only want to change the label of an existing ENUM  a simple UPDATE will do it   To add a new ENUM values    First insert the new value into pg enum  If the new value has to be the last  you re done  If not  you need to a new ENUM value in between existing ones   you ll have to update each distinct value in your table  going from the uppermost to the lowest    Then you ll just have to rename them in pg enum in the opposite order    Illustration You have the following set of labels   ENUM   enum1    enum2    enum3     and you want to obtain   ENUM   enum1    enum1b    enum2    enum3     then   INSERT INTO pg enum  OID   newenum3    UPDATE TABLE SET enumvalue TO  newenum3  WHERE enumvalue  enum3   UPDATE TABLE SET enumvalue TO  enum3  WHERE enumvalue  enum2     then   UPDATE TABLE pg enum SET name  enum1b  WHERE name  enum2  AND enumtypid OID    And so on

[database] Adding a new value to an existing ENUM Type

Examples related to database

Examples related to postgresql

Examples related to enums