Re: Is it possible to delete a single value from an enum type?

On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Melvin Davidson <melvin6925(at)gmail(dot)com> writes:
> > On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik(at)mitev(dot)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
>

AS NOTED IN MY LAST LINE > "That being said "ENUMS are EVIL"! As others
have said, it is much better to just just Foreign Keys for value
integrity."

I have previous advised that enums are a holdover from before Foreign Keys
were available and should be avoided.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.