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:

    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)
    

The ordering of the values in an enum type is the order in which the values were listed when the type was created. All standard comparison operators and related aggregate functions are supported for 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 name
 FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name
-------
Larry
     (1 row)
     

Each enumerated data type is separate and cannot be compared with 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 either write 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)
    
    

枚举标签是大小写敏感的，因此'happy'与'HAPPY'是不同的。标签中的空格也是有意义的。

尽管枚举类型的主要目的是用于值的静态集合，但也有方法在现有枚举类型中增加新值和重命名值（见ALTER TYPE）。不能从枚举类型中去除现有的值，也不能更改这些值的排序顺序，如果要那样做可以删除并重建枚举类型。

一个枚举值在磁盘上占据4个字节。一个枚举值的文本标签的长度受限于NAMEDATALEN设置，该设置被编译在PostgreSQL中，在标准编译下它表示最多63字节。

从内部枚举值到文本标签的翻译被保存在系统目录pg_enum中。可以直接查询该目录。