在 PostgreSQL 中查询表的模式详细信息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4336259/
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
Query the schema details of a table in PostgreSQL?
提问by David
I need to know the column type in PostgreSQL (i.e. varchar(20)). I know that I could probably find this using \dsomething in psql, but I need it to be done with a select query.
Is this possible in PostgreSQL?
我需要知道 PostgreSQL 中的列类型（即varchar(20)）。我知道我可能会使用\dpsql 中的某些东西找到它，但是我需要使用选择查询来完成它。
这在 PostgreSQL 中可能吗？
回答by Erwin Brandstetter
There is a much simplerway in PostgreSQL to get the type of a column.
在 PostgreSQL 中有一种更简单的方法来获取列的类型。
SELECT pg_typeof(col)::text FROM tbl LIMIT 1
The table must hold at least one row, of course. And you only get the base type without type modifiers (if any). Use the alternative below if you need that, too.
You can use the function for constants as well. The manual on pg_typeof().
当然，该表必须至少容纳一行。并且您只能获得没有类型修饰符（如果有）的基本类型。如果您也需要，请使用下面的替代方法。
您也可以将该函数用于常量。上的手册pg_typeof()。
For an empty (or any) table you can use query the system catalog pg_attributeto get the full list of columns and their respective type in order:
对于空（或任何）表，您可以使用查询系统目录pg_attribute来按顺序获取列的完整列表及其各自的类型：
SELECT attnum, attname AS column, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = 'myschema.mytbl'::regclass -- optionally schema-qualified
AND NOT attisdropped
AND attnum > 0
ORDER BY attnum;
The manual on format_type()and on object identifier typeslike regclass.
format_type()有关对象标识符类型的手册，例如regclass.
回答by Alexandre GUIDET
You can fully describe a table using postgres with the following query:
您可以通过以下查询使用 postgres 完整描述表：
SELECT
a.attname as Column,
pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype
FROM
pg_catalog.pg_attribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(TABLENAME)$'
AND pg_catalog.pg_table_is_visible(c.oid)
)
Tith this you will retrieve column names and data type.
据此，您将检索列名和数据类型。
It is also possible to start psql client using the -Eoption
也可以使用-E选项启动 psql 客户端
$ psql -E
And then a simple \d mytablewill output the queries used by postgres to describe the table. It work for every psql describe commands.
然后一个简单的\d mytable将输出 postgres 用来描述表的查询。它适用于每个 psql 描述命令。
回答by mkb
Yes, look at the information_schema.
是的，看看information_schema。
