Skip site navigation (1) Skip section navigation (2)

ENUM type

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Jeff Davis <jdavis-pgsql(at)empires(dot)org>
Cc: Chris Travers <chris(at)travelamericas(dot)com>,PostgreSQL advocacy <pgsql-advocacy(at)postgresql(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: ENUM type
Date: 2005-07-26 20:22:35
Message-ID: 20050726202235.GA29346@decibel.org (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
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(at)decibel(dot)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?"

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2005-07-26 20:30:20
Subject: VACUUM DATABASE
Previous:From: Chris TraversDate: 2005-07-26 20:20:58
Subject: Re: [HACKERS] Enticing interns to PostgreSQL

pgsql-advocacy by date

Next:From: Jim C. NasbyDate: 2005-07-26 20:34:09
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Chris TraversDate: 2005-07-26 20:20:58
Subject: Re: [HACKERS] Enticing interns to PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group