Re: ENUM like data type

On 6/21/05, MRB <nomail(at)example(dot)com> wrote:
> Hi All,
>
> I have something in mind I'm not certain is do-able.
>
> I'm working with a lot of data from MySQL where the MySQL ENUM type is used.
>

MySQL's ENUM is basically a wrapper for CHECK. You can use a CHECK
constraint like this:

CREATE TABLE test(
testfield TEXT CHECK (testfield IN( 'Bits', 'of', 'data'))
);

> This is not a big problem per se but creating the proper lookup tables
> is becoming a bit tedious so I was hoping to make something better of it.
>
> Here is where I get uncertain as to if this is possible. My idea is to
> create a pseudo type that triggers the creation of it's lookup tables
> the same way the SERIAL type triggers creation of a sequence and returns
> an int with the right default value.

Although you can't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:

CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat (
food fruit
);

http://www.postgresql.org/docs/8.0/interactive/sql-createdomain.html

Hope that helps.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org