PostgreSQL：能否将枚举类型转换为整数？

Question

能否将枚举转换为整数？从第一个元素开始编号为1。

- Alberto

Answer 1

正如Catcall所解释的那样，您无法将枚举转换为整数，但是您可以使用PostgreSQL特定的、可能不兼容版本之间的pg_enum系统目录表来获得一个序号表示。

regress=# CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');

regress=# select enumsortorder, enumlabel from pg_catalog.pg_enum 
regress-# WHERE enumtypid = 'happiness'::regtype ORDER BY enumsortorder;
 enumsortorder | enumlabel  
---------------+------------
             1 | happy
             2 | very happy
             3 | ecstatic
(3 rows)

看起来很简单，但实际上并非如此。请注意：

regress=# ALTER TYPE happiness ADD VALUE 'sad' BEFORE 'happy';
regress=# ALTER TYPE happiness ADD VALUE 'miserable' BEFORE 'very happy';
regress=# SELECT * FROM pg_enum ;
 enumtypid | enumsortorder | enumlabel  
-----------+---------------+------------
    185300 |             1 | happy
    185300 |             2 | very happy
    185300 |             3 | ecstatic
    185300 |             0 | sad
    185300 |           1.5 | miserable
(5 rows)

通过这个可以看出，enumsortorder 提供了排序，但没有固定的“距离”。如果将来支持从枚举中删除值，也可能会在序列中创建 "空洞"。

要获取枚举位置，您需要使用 row_number() 窗口函数获取排序，以及使用 pg_typeof 获取枚举类型的 oid（regtype）。您需要这样做，以确保在有多个具有相同标签的枚举时返回正确的序数。

这个函数完成了工作：

CREATE OR REPLACE FUNCTION enum_to_position(anyenum) RETURNS integer AS $$
SELECT enumpos::integer FROM (
        SELECT row_number() OVER (order by enumsortorder) AS enumpos,
               enumsortorder,
               enumlabel
        FROM pg_catalog.pg_enum
        WHERE enumtypid = pg_typeof($1)
    ) enum_ordering
    WHERE enumlabel = ($1::text);
$$ LANGUAGE 'SQL' STABLE STRICT;

注意：

• 它是STABLE而不是IMMUTABLE，因为添加（或如果Pg中支持后来删除）枚举值将更改排序并破坏依赖于排序的索引; 所以
• 您不能在索引表达式中使用它; 和
• 它是STRICT，因为它应该对空输入返回null

现在您可以使用此函数为特定的枚举类型CREATE CAST到 integer 。 您不能为所有枚举类型创建通用转换为 integer ，因为 anyenum 伪类型无法用于转换。 例如，如果我想允许演示 happiness 转换为整数，那么我会写：

CREATE CAST (happiness AS integer) WITH FUNCTION enum_to_position(anyenum);

之后，我成功地执行了以下操作：

regress=# SELECT ('happy'::happiness)::integer;
 int4 
------
    2
(1 row)

请注意，这可能是一个疯狂的做法，不被支持，并且很可能是个可怕的想法。当你添加或删除枚举的值时，你的代码必须知道序数值将会改变。

如果基于此强制类型转换创建索引（仅在函数被定义为不可变时才可能），则会在更改枚举的定义（除了在其末尾添加新值时）时产生错误的结果。因为PostgreSQL相信你说一个函数是不可变的。不要这样做。

- Craig Ringer

---

越来越喜欢Postgres！ - Victor

这个解决方案仅适用于PostgreSQL 9.1及以上版本，enumsortorder自此版本开始可用。 - Skartt

@Skartt 嗯，9.1版本已经过时且不再受支持，如果你正在使用任何旧版本，那么你已经晚了几年进行升级... - Craig Ringer

Answer 2

如果您愿意使用函数enum_range()并对其结果进行计数，您就可以完成此操作。

如果将枚举值作为enum_range()函数的第二个参数传递，NULL作为第一个参数，则会得到一个包含该点之前枚举可以采用的所有值的数组。然后您只需要使用array_length计算它们的数量，即可获得表示枚举的整数。

这是一个例子。这是我的枚举：

content=# select enum_range(null::content_state);
                        enum_range                          
--------------------------------------------------------------
 {created,deleted,preview,draft,submitted,approved,published}

这是我算出“draft”值的整数：

content=# select array_length(enum_range(NULL, 'draft'::content_state), 1);
 array_length 
--------------
            4

注意：从枚举中删除值将使您的整数指向其他值，因此不要在可能需要更改的枚举上使用此功能。

- mpontes

---

你说“滥用”，但我很难看出这是什么样的滥用？整个事情都符合文档。(从枚举中删除值的问题只是一个问题，而不是你的解决方案的问题) - Chris F Carroll

我同意“滥用”这个词太过强烈，这个解决方案正如你所说的那样。唯一的问题是每次这样做时，你实际上都会取出整个枚举定义中可能很大的一部分并对其进行计数。这可能完全不是问题，也可能取决于PostgreSQL查询执行器的操作，我还没有查看源代码或对此解决方案进行基准测试。 - mpontes

Answer 3

你不能将枚举类型强制转换为整数。

你 可能 可以编写一个自定义运算符来提取与值相关联的数字，但我认为这并不值得费那个劲。

如果我需要那种信息，我会建立一个表，并设置一个外键引用，而不是使用枚举。

- Mike Sherrill 'Cat Recall'

Answer 4

我有一个枚举类型，需要用 FK（int4）替换它。以下方法适合我：

ALTER TABLE public."superhero" 
ALTER COLUMN "power" TYPE int4
USING "power"::text::int4;

- pink_demon