枚举是一种数据类型,它的值来自你声明的一个固定列表: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。