Thread: Enumerated (enum) types

In article <5ac667b80805270729x4f93cc14n4a868d3d3f624d7b@mail.gmail.com>,
"Michael Lourant" <lourant@gmail.com> writes:

> Type Safety

> Enumerated types are completely separate data types and may not be compared
> with each other.

...

> An Alternative Way To Do The Same

> Instead of using an enum type we can set up a CHECK CONSTRAINT - this tells
> postgresql to make sure that the value we are entering is valid.

> CREATE TABLE person (
>  personid int not null primary key,
>  favourite_colour varchar(255) NOT NULL,
>  CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))

> );

> INSERT INTO person(personid, favourite_colour) VALUES (1, 'red');
> INSERT 0 1

> Now for something not in the list:

> INSERT INTO person(personid, favourite_colour) VALUES (2, 'green');
> ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

Type safety is the thing you lose by replacing an ENUM by a CHECK
constraint - you can still do something nonsensical like

SELECT * FROM person WHERE favourite_colour = 'green'

<p><font size="2">Thank you!<br /><br /> Very educational and started a few wheels turning for<br /> an application.<br
/><br/> Would you please comment on enum's sql compatibility<br /> and portability?<br /><br /> Regards,<br /><br />
TenaSakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /> -----Original Message-----<br /> From:
pgsql-sql-owner@postgresql.orgon behalf of Michael Lourant<br /> Sent: Tue 5/27/2008 7:29 AM<br /> To:
pgsql-sql@postgresql.org<br/> Subject: [SQL] Enumerated (enum) types<br /><br /> Enumerated (enum) types are data types
thatare comprised of a static,<br /> predefined set of values with a specific order. They are equivalent to the<br />
enumtypes in a number of programming languages. An example of an enum type<br /> might be the days of the week, or a
setof status values for a piece of<br /> data.<br /><br /> *Declaration of Enumerated Types*<br /><br /> Enum types are
createdusing the CREATE TYPE command, for example:<br /><br /> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');<br
/><br/> Once created, the enum type can be used in table and function definitions<br /> much like any other type:<br
/><br/> *Example. Basic Enum Usage*<br /><br /> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');<br /> CREATE TABLE
person(<br />    name text,<br />    current_mood mood<br /> );<br /><br /> INSERT INTO person VALUES ('Moe',
'happy');<br/> SELECT * FROM person WHERE current_mood = 'happy';<br />  name | current_mood<br />
------+--------------<br/>  Moe  | happy<br /> (1 row)<br /><br /> *Ordering*<br /><br /> The ordering of the values in
anenum type is the order in which the values<br /> were listed when the type was declared. All standard comparison
operators<br/> and related aggregate functions are supported for enums. For example:<br /><br /> *Example. Enum
Ordering*<br/><br /> INSERT INTO person VALUES ('Larry', 'sad');<br /> INSERT INTO person VALUES ('Curly', 'ok');<br />
SELECT* FROM person WHERE current_mood > 'sad';<br /> name  | current_mood<br /> -------+--------------<br /> Moe  
|happy<br /> Curly | ok<br /> (2 rows)<br /><br /> SELECT * FROM person WHERE current_mood > 'sad' ORDER BY
current_mood;<br/>  name  | current_mood<br /> -------+--------------<br />  Curly | ok<br />  Moe   | happy<br /> (2
rows)<br/><br /> SELECT name FROM person<br />  WHERE current_mood = (SELECT MIN(current_mood) FROM person);<br />
 name<br/> -------<br />  Larry<br /> (1 row)<br /><br /> *Type Safety*<br /><br /> Enumerated types are completely
separatedata types and may not be compared<br /> with each other.<br /><br /> *Example. Lack of Casting*<br /><br />
CREATETYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');<br /> CREATE TABLE holidays (<br />    num_weeks
int,<br/>    happiness happiness<br /> );<br /> INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');<br />
INSERTINTO holidays(num_weeks,happiness) VALUES (6, 'very happy');<br /> INSERT INTO holidays(num_weeks,happiness)
VALUES(8, 'ecstatic');<br /> INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');<br /> ERROR:  invalid input
valuefor enum happiness: "sad"<br /> SELECT person.name, holidays.num_weeks FROM person, holidays<br />  WHERE
person.current_mood= holidays.happiness;<br /> ERROR:  operator does not exist: mood = happiness<br /><br /> If you
reallyneed to do something like that, you can either write a custom<br /> operator or add explicit casts to your
query:<br/><br /> *Example. Comparing Different Enums by Casting to Text*<br /><br /> SELECT person.name,
holidays.num_weeksFROM person, holidays<br />  WHERE person.current_mood::text = holidays.happiness::text;<br />  name
|num_weeks<br /> ------+-----------<br />  Moe  |         4<br /> (1 row)<br /><br /> *Implementation Details*<br /><br
/>An enum value occupies four bytes on disk. The length of an enum value's<br /> textual label is limited by the
NAMEDATALENsetting compiled into<br /> PostgreSQL; in standard builds this means at most 63 bytes.<br /><br /> Enum
labelsare case sensitive, so 'happy' is not the same as 'HAPPY'.<br /> Spaces in the labels are significant, too.<br
/><br/> *An Alternative Way To Do The Same*<br /><br /> Instead of using an enum type we can set up a CHECK CONSTRAINT
-this tells<br /> postgresql to make sure that the value we are entering is valid.<br /><br /> CREATE TABLE person (<br
/> personid int not null primary key,<br />  favourite_colour varchar(255) NOT NULL,<br />  CHECK (favourite_colour IN
('red','blue', 'yellow', 'purple'))<br /> );<br /><br /> INSERT INTO person(personid, favourite_colour) VALUES (1,
'red');<br/> INSERT 0 1<br /><br /> Now for something not in the list:<br /><br /> INSERT INTO person(personid,
favourite_colour)VALUES (2, 'green');<br /> ERROR: new row for relation "person" violates check constraint<br />
"person_favourite_colour_check"<br/><br /><br /> --<br /> Michael Lourant<br /> "All you need is love"<br /><br
/></font>