Re: How to modify ENUM datatypes?

On Friday 25 April 2008 14:56, Merlin Moncure wrote:
> On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > Merlin Moncure wrote:
> > > I think you're being a little too hard on enums here. I was actually
> > > in the anti-enum camp until it was demonstrated to me (and in my own
> > > testing) that using enum for natural ordering vs. fielding the
> > > ordering of the type out to a join is can be a huge win in such cases
> > > where it is important. Relational theory is all well and good, but in
> > > practical terms things like record size, index size, and query
> > > performance are important.
> >
> > Uhm. Sorry what? Can you demonstrate this particular use?
> > When I first saw discussion about enumns I kinda hoped they
> > will be implemented as kind of macro to really map to a table.
> > But here you go. I'm still looking for a good example to
> > demonstrate the usefullness of enums (same for arrays for that
> > matter)
>
> You must not be aware that enums are naturally ordered to make that
> statement. Suppose your application needs to order a large table by
> a,b,c where b is the an 'enum' type of data. With an enum, the order
> is inlined into the key order, otherwise it's out of line, meaning
> your you key is larger (enum is 4 bytes, varchar is guaranteed to be
> larger), and you need to join out to get the ordering position, use a
> functional index, or cache it in the main table.
>

I think one of the best examples of this is the movie rating system (which I
blogged about at
http://people.planetpostgresql.org/xzilla/index.php?/archives/320-PostgreSQL-8.3-Features-Enum-Datatype.html
)

It's a good example of setting pre-defined values that really can leverage the
enum types custom ordering. It also showcases the idea of data definitions
that "should never change", but that do changes every half dozen years or so.
Now you can argue that since it is expected that the ratings might change in
some way every few years that an enum type is not a good choice for this, but
I feel like some type of counter-argument is that this is probably longer
than one would expect thier database software to last. :-)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL