Re: enums

Jim C. Nasby wrote:

>On Thu, Oct 27, 2005 at 09:45:05PM -0400, Andrew Dunstan wrote:
>  
>
>>Jim C. Nasby wrote:
>>
>>    
>>
>>>Andrew, you mentioned that if you want to change the ordering you should
>>>just create a new type. What about if you need to change the values that
>>>are in the enum? MySQL does (or at least did, it's been some time since
>>>I've messed with this) a horrible job at that. There's no way to rename
>>>anything; you have to add the new names you want, then do a bulk update,
>>>then delete the (now old) names. IMO this is broken.
>>>
>>>
>>>      
>>>
>>It would just be a standard "ALTER TABLE foo ALTER COLUMN bar TYPE 
>>newtype USING expression" operation. You would write a function that 
>>took a value of the old type and returned a value of the new type and 
>>use a cll to that function in the expression. Since these would be named 
>>types, unlike the case in mysql where they are anonymously defined 
>>inline, this would present no difficulties at all.
>>    
>>
>
>But why force a re-write of the entire table just to change the name of
>something?
>  
>

Because you are not just changing the name of something.

You can't decide you want to insert a new digit between 3 and 4 for an 
integer column and just affect a few rows, rather than change the whole 
set of rows for that field. Nor change the order of the integers. An 
enumerated type is just like that, except that the values are labels 
instead of digit sequences.

>>
>>You keep saying that we are using the internal representation as the 
>>ordering. This is simply the wrong way to look at it. The internal 
>>representation REFLECTS the ordering; it doesn't impose it. The user has 
>>imposed the ordering when defining the type. In my enumkit I did provide 
>>a function that gave back the internal representation, but I am not by 
>>any means certain that that's a good idea.
>>    
>>
>
>Well, someone was arguing that enum should be used as a convenient way
>to map human labels on a set of values. To me, that means you should be
>able to define exactly what that set of values is.
>  
>


Well, that's not my conception at all. Then it is not an enumeration in 
my view.

>Personally, I don't see why enum can't just be syntactic sugar on top of
>a side-table of values and a foreign key. And I guess a view to hide the
>internals from normal viewing. That would certainly allow the most
>flexibility, although it probably wouldn't perform as well as what you
>wrote.
>  
>

The other issue is ease of use.

We used lookup tables in bugzilla when it was converted to work with 
Postgres. But many users will find having to do that annoying, to say 
the least. I think there's a very good case for providing true enums. 
There is a technical part of the puzzle I can't quite see yet,  though :-)

cheers

andrew