有没有办法显示用户定义的 postgresql 枚举类型定义？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9535937/
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
Is there a way to show a user-defined postgresql enumerated type definition?
提问by Stéphane
Let's say we've defined a postgresql type:
假设我们已经定义了一个 postgresql 类型：
CREATE TYPE my_type AS ENUM('foo', 'bar');
Is there any way to show the type definition after creation ?
有没有办法在创建后显示类型定义？
I would expect "\d my_type" to show me "ENUM('foo', 'bar')", but it says :
我希望“\d my_type”向我展示“ENUM('foo', 'bar')”，但它说：
Did not find any relation named "my_type"
The pg_type table doesn't seem to give enough information.
pg_type 表似乎没有提供足够的信息。
回答by Adam111p
Check this:
检查这个：
select enum_range(null::my_type)
I think this is a much simpler solution :).
我认为这是一个更简单的解决方案:)。
回答by Richard Huxton
It's \dT you're after, but it doesn't give it as a "CREATE" statement. You use \dD for domains.
它是您所追求的 \dT，但它并没有将其作为“CREATE”语句提供。您将 \dD 用于域。
\dT+ action.action_status
List of data types
Schema | Name | Internal name | Size | Elements | Description
--------+----------------------+---------------+------+----------+-------------
action | action.action_status | action_status | 4 | pending +|
| | | | live +|
| | | | done +|
| | | | notdone |
(1 row)
回答by Erwin Brandstetter
If you just want the full name (type name and schema) and a sorted list of all enumlabels, this query will do:
如果您只需要全名（类型名称和架构）和所有enum标签的排序列表，则此查询将执行以下操作：
SELECT n.nspname AS "schema", t.typname
, string_agg(e.enumlabel, '|' ORDER BY e.enumsortorder) AS enum_labels
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'my_enum_type'
GROUP BY 1,2;
Returns:
返回：
schema | typname | enum_labels
--------+--------------+-------------
public | my_enum_type | foo|bar
string_agg()requires Postgres 9.0 or later, replace with array_agg()for older versions.
string_agg()需要 Postgres 9.0 或更高版本，替换array_agg()为旧版本。
To get the SQL CREATEstatement, you could use pg_dumpand look at the dump file.
要获取 SQLCREATE语句，您可以使用pg_dump并查看转储文件。
Or, much more practically, use pgAdminwhich displays reverse engineered SQL create scripts for any object in the database. Select it in the object browserand its create script is displayed in the SQL pane. There is even an option to copy the script to a newly opened window of the SQL editorautomatically, where you can edit and execute it.
或者，更实际的是，使用pgAdmin显示数据库中任何对象的反向工程 SQL 创建脚本。在 中选择它，object browser它的创建脚本显示在SQL pane. 甚至还有一个选项可以将脚本SQL editor自动复制到新打开的窗口中，您可以在其中编辑和执行它。
回答by dezso
SELECT t.typname
FROM pg_class c JOIN pg_attribute a ON c.oid = a.attrelid JOIN pg_type t ON a.atttypid = t.oid
WHERE c.relname = 'your_type';
The tricky part was that simply selecting * from these views one does not get OIDs in the results.
棘手的部分是简单地从这些视图中选择 * 不会在结果中获得 OID。
