SQL 如何使用PostgreSQL从表名中获取列属性查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15928118/
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 get column attributes query from table name using PostgreSQL?
提问by Carlos Aviles
I have a project and I need a query to get all attributes of the columns (Column Name, Position, Data Type, Not Null? and Comments) all this using table name.
我有一个项目，我需要一个查询来使用表名获取列的所有属性（列名、位置、数据类型、非空？和注释）。
I achieved get Column Name, Position Data Type and Not Null? with this query:
我实现了获取列名、位置数据类型和非空？使用此查询：
SELECT column_name, data_type, ordinal_position, is_nullable
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME'
But, I need the Comments!
但是，我需要评论！
回答by Chris Farmiloe
Here's query against the system catalogthat should fetch everything you need (with a bonus primary-key field thrown in for free).
这是对系统目录的查询，它应该获取您需要的所有内容（免费提供一个额外的主键字段）。
SELECT DISTINCT
a.attnum as num,
a.attname as name,
format_type(a.atttypid, a.atttypmod) as typ,
a.attnotnull as notnull,
com.description as comment,
coalesce(i.indisprimary,false) as primary_key,
def.adsrc as default
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
(pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on
(pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
(a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname = 'TABLE_NAME' -- Your table name here
ORDER BY a.attnum;
Which would return results like:
这将返回如下结果：
num | name | typ | notnull | comment | primary_key
-----+-------------+-----------------------------+---------+---------------------+-------------
1 | id | integer | t | a primary key thing | t
2 | ref | text | f | | f
3 | created | timestamp without time zone | t | | f
4 | modified | timestamp without time zone | t | | f
5 | name | text | t | | f
- num: The column number
- name: The column name
- typ: the data type
- notnull: Is the column defined as
NOT NULL - comment: Any
COMMENTdefined for the column - primary_key: Is the column defined as
PRIMARY KEY - default: The command used for the default value
- num：列号
- 名称：列名称
- typ：数据类型
- notnull：列是否定义为
NOT NULL - 评论：
COMMENT为列定义的任何 - primary_key：列是否定义为
PRIMARY KEY - default：用于默认值的命令
回答by Erwin Brandstetter
Built on the answer by @Chris:
SELECT a.attnum
,a.attname AS name
,format_type(a.atttypid, a.atttypmod) AS typ
,a.attnotnull AS notnull
,coalesce(p.indisprimary, FALSE) AS primary_key
,f.adsrc AS default_val
,d.description AS col_comment
FROM pg_attribute a
LEFT JOIN pg_index p ON p.indrelid = a.attrelid AND a.attnum = ANY(p.indkey)
LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum
LEFT JOIN pg_attrdef f ON f.adrelid = a.attrelid AND f.adnum = a.attnum
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = 'schema.tbl'::regclass -- table may be schema-qualified
ORDER BY a.attnum;
But:
但：
Table names are not uniquein a database and hence also not in the system catalog. You may have to schema-qualify the name.
Use a.attrelid = 'tbl'::regclassas condition. This way you can pass myschema.mytblas name and disambiguate. Then there is no need to join to pg_classat all in this case.
Also, visibility is checked automatically for regclassand there is no need for pg_table_is_visible().
表名在数据库中不是唯一的，因此在系统目录中也不是唯一的。您可能必须对名称进行模式限定。作为条件
使用a.attrelid = 'tbl'::regclass。这样您就可以myschema.mytbl作为名称传递并消除歧义。那么pg_class在这种情况下根本不需要加入。
此外，会自动检查可见性regclass，不需要pg_table_is_visible().
A primary key can span multiple columns. I take care of this by joining to pg_indexon a.attnum = ANY(p.indkey).indkeyis of type int2vecor, which is a special case of int2[], only used in the catalogs.
一个主键可以跨越多个列。我通过加入pg_indexon来解决这个问题a.attnum = ANY(p.indkey)。indkey属于 类型int2vecor，这是 的特例int2[]，仅在目录中使用。
I find psql -Ehelpful for this class of problems.
我发现psql -E对这类问题很有帮助。
Compatibility
兼容性
A specialized query like this might break after a major version update. Postgres does not guarantee that catalog tables remain stable. It is extremely unlikely that basic elements change, but the more complex and specialized your query gets, the bigger the chance. You could use the information schemainstead, which is standardized, but also comparatively slow.
像这样的专门查询可能会在主要版本更新后中断。Postgres 不保证目录表保持稳定。基本元素发生变化的可能性极小，但您的查询越复杂、越专业，机会就越大。您可以改用标准化的信息模式，但速度也相对较慢。
回答by Dang Thach Hai
I think you can use this:
我认为你可以使用这个：
select ordinal_position AS num, column_name as name, data_type as typ, character_maximum_length as lenth,
is_nullable as nullable, column_default as default
from INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog='DatabaseName' AND table_name='TableName'
