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.

Answer 1

The programmatic way to do this is as follows. The same general steps as given in https://stackoverflow.com/a/47305844/629272 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;
   

Answer 2

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

Answer 3

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

Answer 4

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;

Answer 5

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;

Answer 6

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.