postgresql 列出 Postgres ENUM 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9540681/
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
list Postgres ENUM type
提问by punkish
The suggested query to list ENUM typesis great. But, it merely lists of the schemaand the typname. How do I list out the actual ENUM values? For example, in the linked answer above, I would want the following result
列出 ENUM 类型的建议查询很棒。但是，它只是列出了schema和typname。如何列出实际的 ENUM 值？例如，在上面的链接答案中，我想要以下结果
schema type values
------------- -------- -------
communication channels 'text_message','email','phone_call','broadcast'
回答by a_horse_with_no_name
select n.nspname as enum_schema,
t.typname as enum_name,
e.enumlabel as enum_value
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
回答by cesar
You can list the data type via
您可以通过列出数据类型
\dT+ channels
https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS
https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS
回答by mxc
select enum_range(enum_first(null::province),null::province);
回答by Michael
I always forget how to do this. As per the other answer and the comment, here it is a comma-separated list. I like copy-paste snippets. Thanks for the help:
我总是忘记如何做到这一点。根据其他答案和评论，这里是一个逗号分隔的列表。我喜欢复制粘贴片段。谢谢您的帮助：
select n.nspname as enum_schema,
t.typname as enum_name,
string_agg(e.enumlabel, ', ') as enum_value
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name;
回答by 3piece
@dpb:
@dpb：
If you want to create a permanent easy access method for this, you could always create a view
如果您想为此创建一个永久的轻松访问方法，您可以随时创建一个视图
CREATE OR REPLACE VIEW oublic.enumz AS
SELECT n.nspname AS enum_schema,
t.typname AS enum_name,
e.enumlabel AS enum_value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON n.oid = t.typnamespace;
You could then create a trigger for the insert command.
然后，您可以为插入命令创建触发器。
The above will store this in the database for future reference purposes.
以上将把它存储在数据库中以备将来参考。
回答by iugo
Add order
添加订单
SELECT
n.nspname AS enum_schema,
t.typname AS enum_name,
e.enumlabel AS enum_value
FROM
pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
ORDER BY
enum_name,
e.enumsortorder;
回答by blubb
This lists all enum-typed columnsand their potential values:
这列出了所有枚举类型的列及其潜在值：
SELECT
table_schema || '.' || table_name || '.' || column_name as field_name,
pg_enum.enumlabel as value
FROM pg_type
JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid
JOIN pg_namespace on pg_type.typnamespace = pg_namespace.oid
JOIN information_schema.columns ON (information_schema.columns.udt_name = pg_type.typname AND information_schema.columns.udt_schema = pg_namespace.nspname)
WHERE pg_type.typtype = 'e'
ORDER BY field_name, pg_enum.enumsortorder;
回答by Ariel
If you have the table and column name, (but not the type name) use this:
如果您有表名和列名（但没有类型名），请使用以下命令：
SELECT pg_enum.enumlabel
FROM pg_type
JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid
JOIN information_schema.columns ON information_schema.columns.udt_name =
pg_type.typname
WHERE pg_type.typtype = 'e' AND
table_name = AND column_name = ORDER BY pg_enum.enumsortorder
If you use enum_rangeon a column (in contrast to the other answers which used it on a type) it will return data for each row that exists, which is not what you want. So use the above query instead.
如果您enum_range在列上使用（与在类型上使用它的其他答案相反），它将为存在的每一行返回数据，这不是您想要的。所以改用上面的查询。
