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!

Casebook

Possible duplicate of How to delete an enum type value in postgres? – Banneret

Not possible, that's the price you pay for not normalizing – Fleetwood

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)    

Artis

I wonder if the USING ("my_column_name"::text::my_new_enum_name) approach at https://mcmap.net/q/117311/-how-to-delete-an-enum-type-value-in-postgres is better for any reason. – Obregon

@Obregon - The parentheses are redundant, though you can put any Postgres expression in them without any difference. Casting to text is also unnecessary, but it doesn't bother anything. In short, both expressions are the same. – Artis

Thanks for your response. I meant more about the fact that your approach changes the column type to text as a separate earlier step. The other approach casts as text in the same operation as then casts to the enum, if I'm understanding it correctly. And I wonder whether that matters. I.e. are there any downsides of changing the column type to text like you do. – Obregon

@Obregon - Okay, I didn't catch it. My approach seems a bit simpler to me, but both are very similar. In theory, there is a brief moment where someone could insert an arbitrary string into the column in a concurrent transaction. However, Craig's solution is also not 100% secure, someone may try to insert the label you just want to remove. In practice, it's up to a developer/administrator that modifications to data structures should only be made when it is safe to do so. – Artis

Thanks so much for these thoughts! – Obregon

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!

Tropine