Обсуждение: Enumerated (enum) types
Enumerated (enum) types are data types that are comprised of a static, predefined set of values with a specific order. They are equivalent to the enum types in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.
Declaration of Enumerated Types
Enum types are created using the CREATE TYPE command, for example:
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
Once created, the enum type can be used in table and function definitions much like any other type:
Example. Basic Enum Usage
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe | happy
(1 row)
Ordering
The ordering of the values in an enum type is the order in which the values were listed when the type was declared. All standard comparison operators and related aggregate functions are supported for enums. For example:
Example. Enum Ordering
INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
name | current_mood
-------+--------------
Moe | happy
Curly | ok
(2 rows)
SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
name | current_mood
-------+--------------
Curly | ok
Moe | happy
(2 rows)
SELECT name FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
name
-------
Larry
(1 row)
Type Safety
Enumerated types are completely separate data types and may not be compared with each other.
Example. Lack of Casting
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
num_weeks int,
happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR: invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
WHERE person.current_mood = holidays.happiness;
ERROR: operator does not exist: mood = happiness
If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:
Example. Comparing Different Enums by Casting to Text
SELECT person.name, holidays.num_weeks FROM person, holidays
WHERE person.current_mood::text = holidays.happiness::text;
name | num_weeks
------+-----------
Moe | 4
(1 row)
Implementation Details
An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.
Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'. Spaces in the labels are significant, too.
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"
--
Michael Lourant
"All you need is love"
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>