Re: enum problem

I took a look at the MySQL documentation and it would
appear that there isn't a datatype with quite the
exact properties of enum.

However, depending on what you need there are several
ways to create something very enum like.

CREATE TABLE foo (
    bar text
        check (bar IN ('baz', 'qux', 'quz'))
);

The check construct will allow you to limit entries to
one of these values plus NULL (so the above example
would allow you to insert 'baz', 'qux', 'quz' or
NULL).  In fact, the major difference between this an
an ENUM type in MySQL is the fact that PostgreSQL
fails to insert records if they don't match the check
constraint.  The query raises an error and the
transaction is aborted.  In MySQL the insert will
happen, but it will insert an empty text string.

Depending on what you want, this may or may not be a
good thing.

Another handy trick is to use PostgreSQL's referential
integrity and foreign key constraints.  For example,
you could create too tables:

CREATE TABLE foo_lookup (
    value    text PRIMARY KEY
);

And fill it with your lookup values:

INSERT INTO foo_lookup (value) VALUES ('baz');
INSERT INTO foo_lookup (value) VALUES ('quz');
INSERT INTO foo_lookup (value) VALUES ('qux');

And then create your main table:

CREATE TABLE foo (
    bar REFERENCES foo_lookup
);

This would guarantee that foo.bar was either 'baz',
'quz', 'qux' or NULL and it would also make it
possible to later add new values to your table (by
inserting them in foo_lookup).  You could even add an
empty string to foo_lookup and create a simple trigger
that would change foo.bar to an empty string if a
value is inserted that isn't in foo_lookup.  That
would give you a field that worked just like MySQL's
enum.

I hope this is helpful,
Jason

--- victor <victor@work.ro> wrote:
> Hi,
> is there any data type that suply enum type from
> mysql?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1