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

From Melvin Davidson
Subject Re: Is it possible to delete a single value from an enum type?
Date
Msg-id CANu8FiyFvy8O21pjHp4SJ3zjV6eoAxiXehjHLJpmN_Z115G6mg@mail.gmail.com
Whole thread Raw
In response to Re: Is it possible to delete a single value from an enum type?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general


On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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


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.

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Is it possible to delete a single value from an enum type?
Next
From: "Pavlov, Vladimir"
Date:
Subject: Re: Multixacts wraparound monitoring