Adding a new value to an existing ENUM Type

If you are using Postgres 12 (or later) 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';

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';"

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.

DB::statement("ALTER TABLE users DROP CONSTRAINT    users_user_type_check");
$types = ['old_type1', 'old_type1', 'new_type3'];
$result = join( ', ', array_map(function ($value){
           return sprintf("'%s'::character varying", $value);
       }, $types));
DB::statement("ALTER TABLE users ADD CONSTRAINT users_user_type_check CHECK (user_type::text = ANY    (ARRAY[$result]::text[]))");