postgresql 获取枚举可以具有的所有值的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1616123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to get all values a enum can have
提问by Wienczny
Postgresql got enum support some time ago.
Postgresql 前段时间得到了枚举支持。
CREATE TYPE myenum AS ENUM (
'value1',
'value2',
);
How do I get all values specified in the enum with a query?
如何通过查询获取枚举中指定的所有值？
回答by Chris L
If you want an array:
如果你想要一个数组：
SELECT enum_range(NULL::myenum)
If you want a separate record for each item in the enum:
如果您想为枚举中的每个项目单独记录：
SELECT unnest(enum_range(NULL::myenum))
Additional Information
附加信息
This solution works as expected even if your enum is not in the default schema. For example, replace myenumwith myschema.myenum.
即使您的枚举不在默认架构中，此解决方案也能按预期工作。例如，替换myenum为myschema.myenum.
The data type of the returned records in the above query will be myenum. Depending on what you are doing, you may need to cast to text. e.g.
上述查询中返回记录的数据类型将为myenum. 根据您在做什么，您可能需要转换为文本。例如
SELECT unnest(enum_range(NULL::myenum))::text
If you want to specify the column name, you can append AS my_col_name.
如果要指定列名，可以附加AS my_col_name.
Credit to Justin Ohms for pointing out some additional tips, which I incorporated into my answer.
感谢 Justin Ohms 指出了一些额外的提示，我将这些提示合并到了我的答案中。
回答by Kev
Try:
尝试：
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'myenum'
回答by Justin Ohms
SELECT unnest(enum_range(NULL::your_enum))::text AS your_column
This will return a single column result set of the contents of the enum "your_enum" with a column named "your_column" of type text.
这将返回枚举“your_enum”的内容的单列结果集，其中有一列名为“your_column”的文本类型。
回答by David Underhill
You can get all the enum values for an enum using the following query. The query lets you pick which namespace the enum lives in too (which is required if the enum is defined in multiple namespaces; otherwise you can omit that part of the query).
您可以使用以下查询获取枚举的所有枚举值。该查询还允许您选择枚举所在的命名空间（如果枚举在多个命名空间中定义，则这是必需的；否则您可以省略查询的那部分）。
SELECT enumlabel
FROM pg_enum
WHERE enumtypid=(SELECT typelem
FROM pg_type
WHERE typname='_myenum' AND
typnamespace=(SELECT oid
FROM pg_namespace
WHERE nspname='myschema'))
