PostgreSQL“描述表”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/109325/
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
PostgreSQL "DESCRIBE TABLE"
提问by Mr. Muskrat
How do you perform the equivalent of Oracle's DESCRIBE TABLEin PostgreSQL (using the psql command)?
您如何DESCRIBE TABLE在 PostgreSQL 中执行相当于 Oracle 的操作（使用 psql 命令）？
回答by Chris Bunch
Try this (in the psqlcommand-line tool):
试试这个（在psql命令行工具中）：
\d+ tablename
See the manualfor more info.
有关更多信息，请参阅手册。
回答by Vinko Vrsalovic
In addition to the PostgreSQL way (\d 'something' or \dt 'table' or \ds 'sequence' and so on)
除了PostgreSQL的方式（\d 'something' or \dt 'table' or \ds 'sequence' 等等）
The SQL standard way, as shown here:
SQL标准的方式，如图所示在这里：
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';
It's supported by many db engines.
许多数据库引擎都支持它。
回答by Gavin M. Roy
If you want to obtain it from query instead of psql, you can query the catalog schema. Here's a complex query that does that:
如果你想从 query 而不是 psql 中获取它，你可以查询目录模式。这是一个复杂的查询：
SELECT
f.attnum AS number,
f.attname AS name,
f.attnum,
f.attnotnull AS notnull,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.confkey
END AS foreignkey_fieldnum,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.conkey
END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 't' THEN d.adsrc
END AS default
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = '%s' -- Replace with Schema name
AND c.relname = '%s' -- Replace with table name
AND f.attnum > 0 ORDER BY number
;
It's pretty complex but it does show you the power and flexibility of the PostgreSQL system catalog and should get you on your way to pg_catalog mastery ;-). Be sure to change out the %s's in the query. The first is Schema and the second is the table name.
它非常复杂，但它确实向您展示了 PostgreSQL 系统目录的强大功能和灵活性，并且应该可以帮助您掌握 pg_catalog ;-)。请务必更改查询中的 %s。第一个是Schema，第二个是表名。
回答by devinmoore
回答by Mr. Muskrat
The psql equivalent of DESCRIBE TABLEis \d table.
psql 的等价物DESCRIBE TABLE是\d table.
See the psql portion of the PostgreSQL manual for more details.
有关更多详细信息，请参阅 PostgreSQL 手册的 psql 部分。
回答by LeYAUable
This should be the solution:
这应该是解决方案：
SELECT * FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND table_name = 'your_table'
回答by Ryan
You may do a \d *search pattern *with asterisksto find tables that match the search pattern you're interested in.
您可以\d *search pattern *使用星号来查找与您感兴趣的搜索模式匹配的表格。
回答by Mushahid Khan
In addition to the command line \d+ <table_name>you already found, you could also use the information-schemato look up the column data, using info_schema.columns
除了\d+ <table_name>您已经找到的命令行之外，您还可以使用信息模式查找列数据，使用info_schema.columns
SELECT *
FROM info_schema.columns
WHERE table_schema = 'your_schema'
AND table_name = 'your_table'
回答by Mr.Tananki
Use the following SQL statement
使用以下 SQL 语句
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
AND COLUMN_NAME = 'col_name'
If you replace tbl_name and col_name, it displays data type of the particular coloumn that you looking for.
如果替换 tbl_name 和 col_name，它会显示您要查找的特定列的数据类型。
回答by YATK
You can use this :
你可以使用这个：
SELECT attname
FROM pg_attribute,pg_class
WHERE attrelid=pg_class.oid
AND relname='TableName'
AND attstattarget <>0;
