ENUM type - Mailing list pgsql-advocacy

On Tue, Jul 26, 2005 at 01:09:11PM -0700, Jeff Davis wrote:
> Chris Travers wrote:
> >>
> > How hard would it be to automatically create enum_ tables in the back
> > ground to emulate MySQL's enum type?  Sort of like we do with SERIAL
> > datatypes...  Part of the problem is that MySQL's enum type is so
> > braindead from a database design perspective that most of us would not
> > be interested in using it.  Emulating an int foreign key for another
> > created table might make it ok, though.
> >
>
> The thing that occurs to me is that if you really want the enum type in
> PostgreSQL (assuming that there exists a real need), a PostgreSQL person
> would create their own type. Or, if not, just create a wrapper function
> that handles the input/output display and call it explicitly.

OK, but compare the amount of work you just described to the simplicity
of using an enum. Enum is much easier and simpler for a developer. Of
course in most cases the MySQL way of doing it is (as has been
mentioned) stupid, but done in the normal, normalized way it would
remove a fair amount of additional work on the part of a developer:

- no need to manually define seperate table
- no need to define RI
- no need to manually map between ID and real values (though of course
  we should make it easy to get the ID too)

> So to me, the need seems very weak. However, if your goal is
> compatibility, I guess we need it. The problem is it's very difficult to
> do in a general way. We'd probably have to do it specifically for enum,
> and have it generate the types automatically on the fly. Someone would
> have to do some interesting things with the parser, too. Right now even
> the varchar() type, for instance, is kind of a hack.
>
> 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.
>
> Is this achievable with a reasonable amount of effort? Is this
> function-returning-a-type a reasonable behavior?
>
> If nothing else it would clean up the clutter of varchar() and the like,
> that currently use the hacked-in catalog entry "atttypmod" or something
> like that.

Hopefully someone on -hackers can shed light on what's required to clean
up the parsing. One thing worth noting though, is that table definition
is a relatively small part of doing a migration. Generally, it's
application code that causes the most issues. Because of this, I think
there would still be a lot of benefit to an enum type that didn't
strictly follow the mysql naming/definition convention. 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);
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"