Re: ENUM type

On 7/26/05, Jim C. Nasby wrote:
> On Tue, Jul 26, 2005 at 01:09:11PM -0700, Jeff Davis wrote:
>>
>> Ultimately to do it in a general way I think we'd need functions that
>> return a type that can be used in a table definition. Aside from the
>> many problems I don't know about, there are two other problems:
>> (1) After the table (or column?) is dropped, we need to drop the type.
>> (2) Functions currently don't support variable numbers of arguments, so
>> enum still wouldn't be simple. We could do something kinda dumb-looking
>> like:
>> CREATE TABLE mytable (
>> color ENUM("red,green,blue,orange,purple,yellow");
>> );
>> And have the hypothetical ENUM function then parse the single argument
>> and return a type that could be used by that table.

Wouldn't the following work already:
CREATE DOMAIN colors AS TEXT CHECK ( VALUE IN ('red', 'green', 'blue',
'orange', 'purple', 'yellow'));

CREATE TABLE mytable (
color COLORS
);

And this has all the advantages of having a single definition for your
domain in one place, while you can reuse the resulting domain in many
tables. I can't remember when I last deployed a PostgreSQL app without
domains for common data like email addresses, phone numbers and ZIP
codes.

> In this case, it
> might be much easier to have an enum that doesn't allow you to define
> what can go into it at creation time; ie:
>
> CREATE TABLE ...
> blah ENUM NOT NULL ...
> ...
>
> ALTER TABLE SET ENUM blah ALLOWED VALUES(1, 2, 4);

What you are proposing is something PostgreSQL already has:
CREATE TABLE ...
blah TEXT NOT NULL ...
...;

ALTER TABLE ... ADD CONSTRAINT CHECK (blah IN (1,2,4));

ENUM is a braindead idea implemented because MySQL lacked the
infrastructure to let its users do the right thing. (Lets face it:
what percentage of the use of ENUM in MySQL would simply evaporate if
MySQL implemented a proper BOOLEAN datatype?) PostgreSQL has the
infrastructure to allow its users to do the right thing.

Working around ENUMs belongs in a migration guide and maybe in a
migration tool with examples of using a lookup table, a check
contraint and a domain. Working around ENUMs does not belong in the
source.

Jochem