8.7.1. Declarationof Enumerated Types

Enum types are created using theCREATE TYPE command, for example:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once created, the enum type can be used in table and functiondefinitions much like any other type:

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)

8.7.2.Ordering

The ordering of the values in an enum type is the order in whichthe values were listed when the type was created. All standardcomparison operators and related aggregate functions are supportedfor enums. For example:

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 nameFROM personWHERE current_mood = (SELECT MIN(current_mood) FROM person); name  ------- Larry(1 row)

8.7.3. TypeSafety

Each enumerated data type is separate and cannot be comparedwith other enumerated types. See this example:

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');CREATE TABLE holidays (    num_weeks integer,    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 eitherwrite a custom operator or add explicit casts to your query:

SELECT person.name, holidays.num_weeks FROM person, holidays  WHERE person.current_mood::text = holidays.happiness::text; name | num_weeks ------+----------- Moe  |         4(1 row)

8.7.4.Implementation Details

Enum labels are case sensitive, so'happy'is not the same as'HAPPY'. White space inthe labels is significant too.

Although enum types are primarily intended for static sets ofvalues, there is support for adding new values to an existing enumtype, and for renaming values (seeALTER TYPE). Existing values cannot beremoved from an enum type, nor can the sort ordering of such valuesbe changed, short of dropping and re-creating the enum type.

An enum value occupies four bytes on disk. The length of an enumvalue's textual label is limited by theNAMEDATALEN setting compiled intoPostgreSQL; in standard builds this means atmost 63 bytes.

The translations from internal enum values to textual labels arekept in the system catalogpg_enum.Querying this catalog directly can be useful.