Drop a single enum value in postgres

Question

Say I have the following enum:

CREATE TYPE "my_enum" AS ENUM('value1', 'value2', 'value3');

I would like get remove value3. From what I can see in the documentation and in previous threads, I actually have to drop the whole enum and recreate it with the values I want. Something like this:

DROP TYPE IF EXISTS "my_enum";

CREATE TYPE "my_enum" AS ENUM('value1', 'value2');

The issue with that is I have other tables that depend on this enum, so it won't allow me to do so. I get the following message (rightfully so):

ERROR: cannot drop type "my_enum" because other objects depend on it

So my question is how can I remove one of the enums values without dropping the whole thing? I know I can easily add values by just altering:

ALTER TYPE "my_enum" ADD VALUE 'value4';

So I would think I could do something to the equivalent when removing.

Thanks!

Answer 1

You have to drop the type, however, you can temporarily alter table(s) containing columns of the type.

Example model:

create type my_enum as enum('apple', 'pear', 'banana');
create table my_table(id serial primary key, my_col my_enum);
insert into my_table (my_col) values
    ('apple'),
    ('pear');

Remove banana from the enum type:

alter table my_table alter my_col type text;
drop type my_enum;
create type my_enum as enum('apple', 'pear');
alter table my_table alter my_col type my_enum using my_col::my_enum;

Check:

select * from my_table;

 id | my_col 
----+--------
  1 | apple
  2 | pear
(2 rows)    

Answer 2

Hm, although it is really REALLY not recommended, there is a way to achieve this if you have the rights to do:

SELECT 
    t.typname,
    e.enumlabel,
    e.enumtypid
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typname = '<your enum name>'

This gives you the dbms internal id enumtypid of the enum type.

With this you can do the deletion:

DELETE FROM pg_enum
WHERE enumtypid = <your enumtypid>
    AND enumlabel = '<enum value to delete>'

You have to ensure that you are not using the value anymore before this. Otherwise you table could become corrupt!