Re: How to modify ENUM datatypes?

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