Re: How to modify ENUM datatypes?

Hi,

Merlin Moncure wrote:
> On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <tino@wildenhain.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 see, but couldn't you just use int in this case? And map only when
you need the values for display (usually you want it localized anyway)

> I agree with disagree with you on arrays.  I think they are generally
> a bad idea in terms of using them as a column type.  However they are
> useful passing data to/from functions and back/forth from the client.

Yes of course, I thought of that (wondering why we can't use value
expressions everywhere)

Tino