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

Re: ENUM type

From: Jochem van Dieten <jochemd(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ENUM type
Date: 2005-07-26 22:11:47
Message-ID: f96a9b830507261511227ae8f1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
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

In response to

  • ENUM type at 2005-07-26 20:22:35 from Jim C. Nasby

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-07-26 22:14:57
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Chris BrowneDate: 2005-07-26 21:56:42
Subject: Interesting COPY edge case...

pgsql-advocacy by date

Next:From: Jim C. NasbyDate: 2005-07-26 22:14:57
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Merlin MoncureDate: 2005-07-26 21:30:49
Subject: Re: ENUM type

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