Re: Is it possible to delete a single value from an enum type? - Mailing list pgsql-general

Melvin Davidson <melvin6925@gmail.com> writes:
> On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:
>>> In summary, I am looking for the opposite functionality to 'ALTER TYPE
>>> typename ADD VALUE IF NOT EXISTS new_value'
>>> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'.

> It is not easy to delete values from enums, but it can be done.

No, it is NOT SAFE TO DO THAT.  At least not unless you also drop or
reindex every index on columns of the enum type.  Even if you've deleted
every occurrence of the target value appearing in table rows, and vacuumed
away those rows so that their leaf index entries are gone, the target
value could still exist in upper index pages (as a page boundary value,
for example).  Delete the pg_enum entry and you'll break the index,
because enum_cmp() won't know what to do when visiting that index entry.

Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
and probably never will be.  If you need a non-fixed set of key values,
you're much better off using a foreign key instead of an enum type.

                        regards, tom lane