On Tue, Apr 22, 2008 at 6:11 PM, Jeff Davis <pgsql@xxxxxxxxxxx> 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
