Re: How to drop a value from an ENUM?

On Mon, May 28, 2018 at 11:08 PM, Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
wrote:

> Hi,
>
> I am absolutely sure a certain value of one of my ENUM types is not used
> in the entire database. Now I am asked to drop that value. Unfortunately,
> there is no ALTER TYPE DROP VALUE.
>
> On my development box I tried
>
> delete from pg_enum
> where enumtypid='my_type_name'::regtype
> and enumlabel='my_label'
>
> It worked and I could not find any adverse effects.
>
> Given the value is not used anywhere, is this a save command?
>
> Thanks,
> Torsten
>

*Well, imho, you should avoid enums at all cost. As you have discovered,
enums are hard to maintain and have long been replaced by Foreign Keys.With
that being said, apparently your command was safe. However, the best way is
to do the following.to <http://following.to> drop/delete an enum.*

*1. Determine that the particular enum value is NOT referenced by any
column of any table in the database.2. As a superuser, use the following
queries:SELECT t.typname, e.enumlabel, e.enumsortorder,
e.enumtypid FROM pg_type t JOIN pg_enum e ON e.enumtypid = t.oid WHERE
t.typtype = 'e' AND e.enumlabel = 'your_enum_value' ORDER BY 1,
e.enumsortorder; DELETE FROM pg_enum WHERE enumtypid = <e.enumtypid from
previous query> AND enumlabel = 'your_enum_value';-- *

*Melvin DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXC*
Employment by invitation only!