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

On Tue, Apr 22, 2008 at 6:11 PM, Jeff Davis <pgsql@j-davis.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Aug 12, 2008 at 5:40 PM, Dmitry Koterov <dmitry@koterov.ru> wrote:
> 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.

Decent user space solution...it's easy enough.  IMO 'real' solution is
through alter type as you suggest.  It's worth noting there there is
no handling for the unlikely but still possible event of oid
wraparound.  Also, there is no 'enum_insert', which is not so pleasant
with how enums are implemented.

Also, is lgpl compatible with bsd licnese? Not that it matters, but I'm curious.

merlin

On Wed, Aug 13, 2008 at 5:20 AM, Dmitry Koterov <dmitry@koterov.ru> wrote:
> About LGPL - I don't know.
> But the license is not a problem, this code is totally freeware (because too
> simple).
> LGPL is just my favorite license type for years. :-)
>
> I'll change this if you prefer another license and explain, why (why BSD?
> BSD is the PostgreSQL license?)

yup.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general