On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Melvin Davidson <melvin6...@gmail.com> writes: > > On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <n...@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 > 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.
