Re: How to modify ENUM datatypes? (The solution)

Here is the solution about "on the fly" ALTER ENUM:
http://en.dklab.ru/lib/dklab_postgresql_enum/

Usage:

*-- Add a new element to the ENUM "on the fly".
SELECT enum.enum_add('my_enum', 'third');*

*-- Remove an element from the ENUM "on the fly".
SELECT enum.enum_del('my_enum', 'first');*

Possibly future versions of PostgreSQL will include built-in ALTER TYPE for
ENUM, all the more its implementation is not impossible, as you see above.
Hope this will be helpful.

On Wed, Apr 23, 2008 at 4:25 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Apr 22, 2008 at 6:11 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > If you store an integer reference instead, joins are not necessarily
> > expensive. If the number of distinct values is small (which is the
> > normal use case for ENUM), I would expect the joins to be quite cheap.
> > Beware of running into bad plans however, or making the optimizer work
> > too hard (if you have a lot of other joins, too).
>
> Necessarily being the operative word here. Think about an enum as
> part of a composite key for example. It's a lot nicer to rely on enum
> for natural ordering than doing something like a functional index.
>
> Anyways, it's pretty easy to extend an enum...you can manually insert
> an entry into pg_enum (see the relevent docs). Just watch out for oid
> overlap. One thing currently that is very difficult currently to do
> is to alter the order of the enum elements. The current state of
> things is pretty workable though.
>
> Scott's color/mystuff example is generally preferred for a lot of
> cases. I _really_ prefer this to surrogate style enums where you have
> color_id...this approach makes your database unreadable IMO. A decent
> hybrid approach which I have been using lately is "char" (not char)
> where the choices set is reasonably small, well represented by a
> single character, and the intrinsic ordering property is not too
> important (where an enum might be better). In many cases though, the
> pure natural approach is simply the best. The enum though with is
> intrinsic ordering and more efficient indexing has an important niche
> however.
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>