在PostgreSQL中查询表的架构详细信息? [英] Query the schema details of a table in PostgreSQL?
问题描述
我需要知道PostgreSQL中的列类型(即 varchar(20)
)。我知道我可能可以在psql中使用 \d
来找到它,但是我需要通过选择查询来完成。
这是吗?可能在PostgreSQL中使用?
I need to know the column type in PostgreSQL (i.e. varchar(20)
). I know that I could probably find this using \d
something in psql, but I need it to be done with a select query.
Is this possible in PostgreSQL?
推荐答案
您可以使用带有以下查询的postgres来完整描述表:
You can fully describe a table using postgres with the following query:
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.
也可以启动使用 -E
选项的psql客户端
It is also possible to start psql client using the -E
option
$ psql -E
然后将输出一个简单的 \d mytable
postgres用于描述表的查询。它适用于每个psql describe命令。
And then a simple \d mytable
will output the queries used by postgres to describe the table. It work for every psql describe commands.
这篇关于在PostgreSQL中查询表的架构详细信息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!