SQL 如何使用 Postgres information_schema 列出自定义类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3660787/
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
How to list custom types using Postgres information_schema
提问by Collin Peters
I am trying to find the equivalent SQL of \dT using the information_schema and can't seem to find anything. Does such a thing exist?
我正在尝试使用 information_schema 找到 \dT 的等效 SQL，但似乎找不到任何东西。这样的事情存在吗？
Example: If I add the following custom type enum, how can I see it in the information_schema?
示例：如果我添加了以下自定义类型枚举，如何在 information_schema 中看到它？
CREATE TYPE communication.channels AS ENUM
('text_message',
'email',
'phone_call',
'broadcast');
NOTE: I do have the exact SQL used by \dT (retrieved by turning up the logging) but I am looking specifically for a cleaner implementation using the information_schema
注意：我确实有 \dT 使用的确切 SQL（通过打开日志记录检索），但我正在寻找使用 information_schema 的更清晰的实现
采纳答案by Peter Eisentraut
Enums are not in the SQL standard and therefore not represented in the information schema. Other user-defined types would normally be in the view user_defined_types, but that's not implemented. So at the moment, you can't use the information schema to list user-defined types in PostgreSQL.
枚举不在 SQL 标准中，因此不在信息模式中表示。其他用户定义的类型通常会在 view 中user_defined_types，但并未实现。所以目前，你不能使用信息模式在 PostgreSQL 中列出用户定义的类型。
回答by Collin Peters
For reference, here is the SQL from \dT (pgAdmin uses the same or similar)
作为参考，这里是来自 \dT 的 SQL（pgAdmin 使用相同或相似的）
SELECT n.nspname as schema, t.typname as type
FROM pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
AND n.nspname NOT IN ('pg_catalog', 'information_schema');
回答by Guido
This is a simple way to list all the enum defined types in the current database. The query result returns two columns, the first show the name of every enum types, the second show the name of every value for each enum type:
这是列出当前数据库中所有枚举定义类型的简单方法。查询结果返回两列，第一列显示每个枚举类型的名称，第二列显示每个枚举类型的每个值的名称：
SELECT pg_type.typname AS enumtype,
pg_enum.enumlabel AS enumlabel
FROM pg_type
JOIN pg_enum
ON pg_enum.enumtypid = pg_type.oid;
回答by Vadim Shvetsov
List all db types:
列出所有数据库类型：
test=# \dT
List of data types
Schema | Name | Description
--------+---------------------+-------------
public | gender |
public | status |
List all db types with additional information like values:
列出所有带有附加信息（如值）的数据库类型：
test=# \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+---------------------+---------------------+------+-------------------+-------+-------------------+-------------
public | gender | gender | 4 | male +| Vadim | |
| | | | female | | |
public | status | status | 4 | processing +| Vadim | |
| | | | passed +| | |
| | | | failed | | |
Get certain type with additional information:
获取特定类型的附加信息：
leps=# \dT+ gender
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+------------+---------------+------+-------------------+-------+-------------------+-------------
public | gender | gender | 4 | male +| Vadim | |
| | | | female +| | |
回答by andrew
List of all defined by Your self types:
由您的自我类型定义的所有列表：
\dT
testDB=> \dT
List of data types
Schema | Name | Description
--------+-------------------------+-------------
public | myType |
(1 row)
回答by MikeM
I use a view to show my enum names. The data from that view can consequently be used in an application to provide a list of available options for an enum field.
我使用视图来显示我的枚举名称。因此，可以在应用程序中使用该视图中的数据为枚举字段提供可用选项列表。
CREATE OR REPLACE VIEW vw_enums AS
SELECT t.typname, e.enumlabel, e.enumsortorder
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid;回答by DrColossos
Have a look here: http://www.postgresql.org/docs/current/static/catalog-pg-enum.html
看看这里：http: //www.postgresql.org/docs/current/static/catalog-pg-enum.html
The pg_enum catalogue should have the data you are looking fore
pg_enum 目录应该有你要找的数据
