Menu

PostgreSQL 枚举类型:CREATE TYPE AS ENUM

枚举类型是一组固定且有序的允许值,用 CREATE TYPE ... AS ENUM 创建。内容包括添加和重命名值、列出所有值、排序,以及什么时候 CHECK 约束更合适。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

枚举是一种数据类型,它的值来自你声明的一个固定列表:CREATE TYPE name AS ENUM ('a', 'b', 'c')。这种类型的列只接受这些标签,并且按声明的顺序排序,而不是按字母顺序。

返回的行按 new、paid、shipped、delivered 的顺序排列。插入不在列表中的标签,比如 'lost',会报错 invalid input value for enum order_status: "lost"。

按位置比较和筛选

因为值是有顺序的,<、> 和 BETWEEN 比较的是它们在列表中的位置。“已付款或之后的任何状态”只需一个条件:

订单 1、3 和 4 符合条件。min(status) 和 max(status) 也遵循声明的顺序。

列出枚举的所有值

enum_range(NULL::type) 以数组形式返回所有标签,unnest 把它展开成行:

enum_first(NULL::mood) 和 enum_last(NULL::mood) 返回第一个和最后一个标签。在 psql 中,\dT+ mood 会显示这个类型及其所有值。

添加或重命名值

ALTER TYPE ... ADD VALUE 在末尾追加一个标签,或者用 BEFORE、AFTER 指定位置。RENAME VALUE 修改标签,所有用到它的行都会跟着变,因为行中存的是对标签的引用,而不是标签文本。

现在的列表是 pending、paid、packed、shipped、delivered。加上 IF NOT EXISTS 后,添加已经存在的标签不会做任何事。

PostgreSQL 不能删除值,也不能调整已有值的顺序。替代做法是新建一个类型,把列转换过去,再删掉旧类型:

CREATE TYPE order_status_new AS ENUM ('pending', 'paid', 'shipped');
UPDATE orders SET status = 'shipped' WHERE status = 'delivered';
ALTER TABLE orders
    ALTER COLUMN status DROP DEFAULT,
    ALTER COLUMN status TYPE order_status_new USING status::text::order_status_new;
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

先删除默认值,是因为它的类型是旧的枚举。完成之后再重新设置默认值。

枚举和 CHECK 约束的对比

带 CHECK 约束的 text 列能提供同样的保护,而且它的列表用普通的 ALTER TABLE 就能修改:

这里是按字母顺序排序的:先 new,再 paid。

枚举text + CHECK
排序顺序声明的顺序字母顺序
存储空间4 字节文本的长度
删除一个值新建类型并转换列替换约束
在多张表中复用一个类型重复写约束

如果列表短小、稳定,而且顺序有意义,比如状态或尺码,就选枚举。如果列表经常变化或者会删除值,就选 CHECK,或者用带外键的查找表。其余细节见手册的枚举章节。

常见问题

怎么从 PostgreSQL 枚举中删除一个值?

没有 ALTER TYPE ... DROP VALUE。先更新用到这个值的行,再新建一个不含该值的枚举,用 ALTER TABLE ... ALTER COLUMN ... TYPE new_type USING col::text::new_type 转换列,最后删除旧类型。

一个枚举值占用多少空间?

无论标签多长,磁盘上每个值都占 4 个字节。标签区分大小写,最长 63 个字节。

事务中能用 ALTER TYPE ADD VALUE 吗?

能,从 PostgreSQL 12 开始支持。不过在该事务提交之前,新值还不能使用。

枚举列怎么和文本比较?

字符串字面量会自动转换成枚举,所以 WHERE status = 'paid' 可以直接用。文本列或参数则需要类型转换:status = $1::order_status,或者按文本比较:status::text = $1。

Coddy 编程语言插图

和 Coddy 一起学编程

开始