Postgresql 中文操作指南

8.7. Enumerated Types #

枚举 (enum) 类型是包括静态有序值集的数据类型。它们等效于许多编程语言中支持的 enum 类型。枚举类型的一个示例可能是星期天，或是一组数据的状态值。

Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported 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.

8.7.1. Declaration of Enumerated Types #

枚举类型是使用 CREATE TYPE 命令创建的，例如：

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)

8.7.2. Ordering #

枚举类型中的值的排序是创建类型时列出这些值时的顺序。枚举支持所有标准比较运算符和相关的聚合函数。例如：

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)

8.7.3. Type Safety #

每个枚举数据类型都是独立的，不能与其他枚举类型进行比较。请参阅此示例：

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)

8.7.4. Implementation Details #

枚举标签区分大小写，因此 'happy' 与 'HAPPY' 不同。标签中的空白字符也很重要。

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

虽然枚举类型主要用于静态值集，但支持向现有枚举类型添加新值，以及重命名值（请参阅 ALTER TYPE ）。现有的值不能从枚举类型中移除，此类值的排序顺序也不能更改，除非删除并重新创建枚举类型。

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

枚举值在磁盘上占用四个字节。枚举值的文本标签的长度受已编译到 PostgreSQL 中的 NAMEDATALEN 设置的限制；在标准构建中，这意味着最多为 63 个字节。

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.

从内部枚举值到文本标签的转换保存在系统目录 pg_enum 中。直接查询此目录可能很有用。

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