How to delete an enum type value in postgres?

Question

How do I delete an enum type value that I created in postgresql?

create type admin_level1 as enum('classifier', 'moderator', 'god');

E.g. I want to remove moderator from the list.

I can't seem to find anything on the docs.

I'm using Postgresql 9.3.4.

Gerstein

Answer 1

You delete (drop) enum types like any other type, with DROP TYPE:

DROP TYPE admin_level1;

---

Is it possible you're actually asking about how to remove an individual value from an enum type? If so, you can't. It's not supported:

Although enum types are primarily intended for static sets of values, there is support for adding new values to an existing enum type, and for renaming values (see ALTER TYPE). Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum type.

You must create a new type without the value, convert all existing uses of the old type to use the new type, then drop the old type.

E.g.

CREATE TYPE admin_level1 AS ENUM ('classifier', 'moderator');

CREATE TABLE blah (
    user_id integer primary key,
    power admin_level1 not null
);

INSERT INTO blah(user_id, power) VALUES (1, 'moderator'), (10, 'classifier');

ALTER TYPE admin_level1 ADD VALUE 'god';

INSERT INTO blah(user_id, power) VALUES (42, 'god');

-- .... oops, maybe that was a bad idea

CREATE TYPE admin_level1_new AS ENUM ('classifier', 'moderator');

-- Remove values that won't be compatible with new definition
-- You don't have to delete, you might update instead
DELETE FROM blah WHERE power = 'god';

-- Convert to new type, casting via text representation
ALTER TABLE blah 
  ALTER COLUMN power TYPE admin_level1_new 
    USING (power::text::admin_level1_new);

-- and swap the types
DROP TYPE admin_level1;

ALTER TYPE admin_level1_new RENAME TO admin_level1;

Retuse

This is brilliant! With this I managed to solve Alembic migration problem. I couldn't add new enum type because of (psycopg2.InternalError) ALTER TYPE ... ADD cannot run inside a transaction block – Evelinevelina

add disable_ddl_transaction! to the top of the migration file. – Mullock

DELETE FROM blah WHERE power = 'god'; not working in my case – Commentate

TBH I do not understand why this answer was selected. This answer is not correct! You can delete value from pg_enum with specified label. – Gensler

@RomanPoelinov direct catalog manipulation I'd at your own risk. There are reasons postgres doesn't support deleting enum values natively. How is this "not correct" compared to an unsupported and unsafe catalog hack? – Retuse

still postgres is not supporting to delete value from enum type? – Vargas

+1 because this was helpful. At first, it wasn't working for me. Then I realized I needed to surround my type names in double-quotes. This works for me dropping an enum value in Postgres using Prisma migration: CREATE TYPE "X_new" AS ENUM( 'val1', 'val2' ); ALTER TABLE "MyTbl" ALTER COLUMN "my_col" TYPE "X_new" USING ("my_col"::text::"X_new"); DROP TYPE "X"; ALTER TYPE "X_new" RENAME TO "X"; – Lepper

I can't see any reason why deleting an enum value should be a problem as long as it's not in use. If you accidentally added one with the wrong value as I just did, the stupid thing should just let you fix it. Just like it will let you delete a row as long as there is no foreign key dependency. – Kata

@Kata Postgres would need to go out and scan all existing uses of that enum in all tables to see if the value is in fact in use. Which is something that could be implemented but is ugly. Or it'd need to have a way to report unrecognised enum values when they're encountered in values. The best way would probably be a way to 'soft delete' enum values so they can still be reported for existing uses of the type, but not used in new inputs; however, this would break things like REINDEX unless done very carefully. – Retuse

Answer 2

Very well written here:

http://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/

rename the existing type

ALTER TYPE status_enum RENAME TO status_enum_old;

create the new type

CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');

update the columns to use the new type

ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;

remove the old type

DROP TYPE status_enum_old;

---

Possible Errors and Troubleshooting:

• invalid input value for enum {enum name}: "{some value}" - One or more rows have a value ("{some value}") that is not in your new type. You must handle these rows before you can update the column type.
• default for column "{column_name}" cannot be cast automatically to type {enum_name} - The default value for the column is not in your new type. You must change or remove the default value for the column before you can update the column type. Thanks to Philipp for this addition.
• cannot alter type of a column used by a view or rule - All views and rules using the column have to be dropped before the ALTER can be executed and then recreated afterwards. There are ways to do that automatically.

Cochran

Notice though: ERROR 25001: ALTER TYPE ... ADD cannot run inside a transaction block – Overijssel

Be aware that if the column is used in a constraint you may get an operator does not exist error on <>. I had to drop the check constraints using the column, modify the field, and re-add the constraints. – Uroscopy

Looks good, but I am getting ERROR: cannot cast type unit_enum_old to unit_enum. Probably some cast is required additionally. I am 146% sure that there are no values with removed enum values. UPDATE: My fault. I was missing ::text:: in the using statement. – Wavelet

Answer 3

This is a very dangerous operation. You must make sure no table is using the enum value you want to remove. If you don't, you will badly break all tables that reference the enum value

If you want delete item of enum type, you must operate on system table of PostgreSQL.

With this command, you can display all the items enum type

SELECT * FROM pg_enum;

Then check that searched the value is unique. To increase the uniqueness during the removal of rekoru must be passed 'enumtypid' in addition to 'enumlabel'.

This command removes the entry in enum type, where 'unique' is your value.

DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unique';

Note: This command must not be used if 'unique' is used anywhere in the database.

Southerner

This is a very dangerous operation, but it's very quick and succinct in removing a value from an enum type if you know what you're doing. First, make sure no table is using the enum value you want to remove. If you don't, you will badly break all tables that reference the enum value (e.g. selecting from such a table will return ERROR: invalid internal value for enum and yield NO results.) – Saied

That's right, this is the most important aspect that should be taken into account. The example that I described must be used, when by chance we add new value to enum type, and yet we have not used it anywhere in database. – Southerner

Given how dangerous this command is DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unigue'; The NOTE should be in BOLD, not the command. If you have used the value in some table, you cannot recover from it. You cannot update the rows that contain the value, you cannot convert. Only way is to delete the entire row. – Lumpkin

Please do not scare people too much. Just make sure they do their best to cleanup the values in all related tables and they do a full database dump (with pg_dumpall) before an enum delete operation. So if a table breaks they can recover it manually from the dump. – Frame

Also, you can temporary put back a dummy enum value with insert into pg_enum (oid, enumtypid, enumsortorder, enumlabel) values (myoid, myenumtypid, 88888888, '*dummy*'); then update the breaking table with update mytable set myenumcolumn='mynewvalue' where myenumcolumn='*dummy*'; and finally delete the enum again with delete from pg_enum where oid=myoid;. Here myoid is the oid of the deleted enum value which you will get from the error message. – Frame

To add to what @ClintPachl says, note that per postgresql.org/message-id/21012.1459434338%40sss.pgh.pa.us, it's not enough to simply ensure you've eliminated all references to the enum value in table rows before deleting it from pg_enum, because even after you've done that, the value may appear in indexes. – Virnelli

@SzieberthAdam "notice that table is broken" and "restore from dump" may be no big deal, or may be a major, expensive outage that loses data that was inserted in other tables between the time of the breakage and the restore. It depends on the situation. I think the warning is justified, especially in light of the additional note Mark Amery makes about indexes. – Grilse

I don't see why this is such a super scary dangerous operation. How is it any more dangerous than deleting rows or dropping tables? Postgres happily lets you do those things so why can't it just do that with enums? – Kata

Answer 4

For those who wish to modify the enum values, recreating it seems to be the only viable and safe solution.

It consists in temporarely convert the enum column to a string format, recreate the enum and then reconverting the string column back to the enum type.

Here is an example:

ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE varchar(255);
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
DROP TYPE your_schema.your_enum_name;
CREATE TYPE your_schema.your_enum_name AS ENUM ('enum1', 'enum2', 'enum3');
ALTER TABLE your_schema.your_table ALTER your_column DROP DEFAULT;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');

Reisch

ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column; should be ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_schema.your_column::your_enum_name; – Teresitateressa

I went with: select * from pg_enum where enumlabel='Manager';update pg_enum set enumlabel = 'FacilityManager' where oid=489188; – Retroactive

Answer 5

Use following query to Delete ENUM value from Postgresql type

DELETE FROM pg_enum
WHERE enumlabel = 'moderator'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'admin_level1');

Just info for what's type and what's value

DELETE FROM pg_enum
WHERE enumlabel = 'ENUM_VALUE'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'ENUM_TYPE')

You should change existing values to other. For that if you need to add new value, then use:

ALTER TYPE **ENUM_TYPE** ADD VALUE '**ENUM_VALUE2**'; 

Before deleting, update type value to new type value or existing value.

Chancellor

The only issue is the typname in pg_type is lower case. so its not working, unless using the lower case enum_type in SELECT oid FROM pg_type WHERE typname = 'enum_type' – Archeology

As mentioned above (#25811517) this is very dangerous. – Chrysoberyl

Answer 6

The programmatic way to do this is as follows. The same general steps as given in https://mcmap.net/q/117311/-how-to-delete-an-enum-type-value-in-postgres are appropriate, but those are more manual than made sense for my purposes (writing an alembic down migration). my_type, my_type_old, and value_to_delete, should, of course, be changed as appropriate.

1. Rename your type.

   ALTER TYPE my_type RENAME TO my_type_old;
   

2. Create a new type with the values from your old type, excluding the one you want to delete.

   DO $$
   BEGIN
       EXECUTE format(
           'CREATE TYPE my_type AS ENUM (%s)',
           (
               SELECT string_agg(quote_literal(value), ',')
               FROM unnest(enum_range(NULL::my_type_old)) value
               WHERE value <> 'value_to_delete'
           )
       );
   END $$;
   

3. Change all existing columns which use the old type to use the new one.

   DO $$
   DECLARE
       column_data record;
       table_name varchar(255);
       column_name varchar(255);
   BEGIN
       FOR column_data IN
           SELECT cols.table_name, cols.column_name
               FROM information_schema.columns cols
               WHERE udt_name = 'my_type_old'
       LOOP
           table_name := column_data.table_name;
           column_name := column_data.column_name;
           EXECUTE format(
               '
                   ALTER TABLE %s
                   ALTER COLUMN %s
                   TYPE my_type
                   USING %s::text::my_type;
               ',
               table_name, column_name, column_name
           );
       END LOOP;
   END $$;
   

4. Delete the old type.

   DROP TYPE my_type_old;
   

Bondon

Answer 7

It's not possible to delete individual value from ENUM, the only possible solution is to DROP and recreate ENUM with needed values.

Lagoon

It's very possible, what you probably meant is "not officially supported". – Culdesac

@Culdesac would you mind to provide a code which can remove one exact value from ENUM? – Lagoon

Answer 8

My solution starts from the fact that I don't have permissions in my scenario to perform DELETE FROM pg_enum as I'm getting a permissions error.

Starting from other replies, I created a generic function that can be used to remove a single value from a enum, supporting the update value to release the use of given value

-- https://stackoverflow.com/a/62444685
-- https://stackoverflow.com/a/51073579
create or replace function remove_enum_value(
    type_name text,         -- Name of the type where you need to remove a value from
    value_to_delete text,   -- Specific value of the given type you want to remove
    value_fallback text,    -- Which new value columns will have instead of the value deleted
    column_default text     -- DEFAULT value for the column after type alteration (DEFAULT need to be disabled before changing type, https://stackoverflow.com/a/41149789)
)
RETURNS VOID AS $body$

declare 
    -- Used as temporary type
    _type_name_tmp text := type_name || '_tmp_' || floor(extract(epoch from now()) * 1000);

    -- Used to store statements to execute
    _sql text;

    -- Used to loop tables and switch type from current to temporary
    _column_data record;
    _table_name varchar(255);
    _column_name varchar(255);
   
begin   
    
    --------------------------------------------------------------------------------------------------------------
    
    -- Check: required inputs
    if type_name is null
    then
        raise exception 'Parameter type_name is null';
    end if;
    if value_to_delete is null
    then
        raise exception 'Parameter value_to_delete is null';
    end if;
    
    -- Check: type exists
    IF not EXISTS (SELECT 1 FROM pg_type WHERE typname = type_name) THEN
        raise info 'Type %s does not exists', type_name;
        return;
    END IF;
    
    -- Check: value to delete exists
    if not exists(
        select * 
        FROM pg_enum -- check existing of value to delete
        WHERE enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1) and enumlabel=cast(value_to_delete as varchar)
    )
    then 
        raise info 'Value to delete % does not exists in type %s', value_to_delete, type_name;
        return;
    end if;
        
    -- Check: fallback value is provided and exists
    if value_fallback is not null and not exists(
        select * 
        FROM pg_enum -- check existing of value to delete
        where
            enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1)
            and enumlabel=cast(value_fallback as varchar)
    )
    then 
        raise info 'Fallback value % does not exists in type %s', value_fallback, type_name;
        return;
    end if;

    -- Check values are different
    if value_fallback = value_to_delete
    then
        raise info 'Value to delete %s is the same as fallback value %', value_to_delete, value_fallback;
        return;
    end if;

    raise info 'Checks passed, ready to process!';

    --------------------------------------------------------------------------------------------------------------
        
    -- Retrieve current values of type
    _sql := format('
        SELECT string_agg(quote_literal(value), '','')
        FROM unnest(enum_range(NULL::%s)) value
        WHERE value <> ''%s''
    ', type_name, value_to_delete);
    raise info '%', _sql;
    execute _sql into _sql;

    -- Create temporary enum
    _sql := format(
        'CREATE TYPE %s AS ENUM (%s)',
        _type_name_tmp,
        _sql
    );
    raise info '%', _sql;
    execute _sql;

    -- Rename all values from value that need to be deleted to new value (selecting all tables with schemas which has column with enum relation)
    for _column_data in (
        select
            concat(c.table_schema,'.',c.table_name ) as table_name,
            c.column_name
        FROM information_schema.columns c
        where
            c.udt_name = cast(type_name as varchar)
            and c.table_schema=c.udt_schema 
            and data_type = 'USER-DEFINED'
    )
    LOOP
        _sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L', _column_data.table_name, _column_data.column_name, value_fallback, value_to_delete);
        raise info 'Update by looping: %', _sql;
        EXECUTE _sql;
    END LOOP;

    -- Switch type from current to temporary
    FOR _column_data in (
        SELECT cols.table_name, cols.column_name
        FROM information_schema.columns cols
        WHERE udt_name = type_name
    )
    LOOP
        _table_name := _column_data.table_name;
        _column_name := _column_data.column_name;
        _sql := format(
            '
                ALTER TABLE %s
                    ALTER COLUMN %s DROP DEFAULT,
                    ALTER COLUMN %s TYPE %s USING %s::text::%s,
                    ALTER COLUMN %s SET DEFAULT %s;
            ',
            _table_name, 
            _column_name, 
            _column_name, _type_name_tmp, _column_name, _type_name_tmp,
            _column_name, (case when column_default is null then null else '''' || column_default || '''::' || _type_name_tmp end)
        );
       
        raise info '%', _sql;
        execute _sql;
    END LOOP;
   
    -- Drop previous type
    _sql := format('DROP TYPE %s;', type_name);
    raise info '%', _sql;
    execute _sql;
    
    -- Rename type to previous name
    _sql := format('ALTER TYPE %s RENAME TO %s;', _type_name_tmp, type_name);
    raise info '%', _sql;
    execute _sql;
       
END $body$
LANGUAGE plpgsql;

Impetus

Answer 9

Had the same problem in v.10. postgres. Deletion requires certain procedures, and if the sequence is not correct, then there will even be a chance of the table being locked for reading.

Wrote a convenient script to delete. Already proven several times its performance. However, this procedure involves replacing the deleted value with a new one (it can be NULL if the table field allows this).

To use, you just need to fill in 3 values.

DO $$
DECLARE
    enumTypeName VARCHAR := 'enum_name'; -- VALUE #1, set yor value!
    enumOldFieldValue varchar := 'old_enum_value'; -- VALUE #2, enum value which have to be deleted
    enumNewFieldValue varchar := null; -- VALUE #3, which new value must be instead of deleted
    sql varchar:='';
    rec record;
BEGIN
    raise info 'Check on old and new enum values.';
    IF exists(select * FROM pg_enum -- check existing of OLD enum value
              WHERE enumtypid = (select oid from pg_type where typName=cast(enumTypeName as varchar) limit 1) and enumlabel=cast(enumOldFieldValue as varchar))
      AND
       (exists(select *
               FROM pg_enum -- check existing of NEW enum value
               WHERE enumtypid = (select oid from pg_type where typName = cast(enumTypeName as varchar) limit 1)
                 and enumlabel = cast(enumNewFieldValue as varchar))
           OR
        enumNewFieldValue IS NULL)
        THEN
            raise info 'Check passed!';

            -- selecting all tables with schemas which has column with enum relation
            create temporary table tmp_table_names
             as SELECT concat(c.table_schema,'.',c.table_name ) as table_name, c.column_name
                FROM information_schema.columns c
                WHERE c.udt_name = cast(enumTypeName as varchar)
                  and c.table_schema=c.udt_schema and data_type = 'USER-DEFINED';

            -- if we have table(s) that uses such enum
            if exists(select * from tmp_table_names)
                then
                    FOR rec in (select table_name, column_name from tmp_table_names) LOOP
                        sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L',rec.table_name, rec.column_name, enumNewFieldValue, enumOldFieldValue);
                        raise info 'Update by looping: %', sql;
                        EXECUTE sql;
                    END LOOP;
            end if;

            -- just after changing all old values in all tables we can delete old enum value
            sql := format('DELETE FROM pg_enum WHERE enumtypid = (select oid from pg_type where typName=%1$L limit 1) and enumlabel=%2$L',enumTypeName,enumOldFieldValue);
            raise info 'Delete enum value: %', sql;
            EXECUTE sql;

            drop table  tmp_table_names;
        ELSE
            raise info 'Old or new enum values is missing.';
    end if;
END $$;

1. List item

Fission

Answer 10

if your dataset is not so big you can dump with --column-inserts edit the dump with a text editor, remove the value and re-import the dump

Rutland