如何使用PostgreSQL从表名中获取列属性？

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!

但是，我需要评论！

2 个解决方案

#1

Here's query against the system catalog that 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
• num：列号
• name: The column name
• name：列名
• typ: the data type
• typ：数据类型
• notnull: Is the column defined as NOT NULL
• notnull：列是否定义为NOT NULL
• comment: Any COMMENT defined for the column
• 注释：为列定义的任何COMMENT
• primary_key: Is the column defined as PRIMARY KEY
• primary_key：列是否定义为PRIMARY KEY
• default: The command used for the default value
• default：用于默认值的命令

#2

Built on the answer by @Chris:

建立在@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 unique in a database and hence also not in the system catalog. You may have to schema-qualify the name.
Use a.attrelid = 'tbl'::regclass as condition. This way you can pass myschema.mytbl as name and disambiguate. Then there is no need to join to pg_class at all in this case.
Also, visibility is checked automatically for regclass and 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_index on a.attnum = ANY(p.indkey).
indkey is of type int2vecor, which is a special case of int2[], only used in the catalogs.

主键可以跨多个列。我通过在a.attnum = ANY（p.indkey）上加入pg_index来处理这个问题。 indkey的类型为int2vecor，这是int2 []的特例，仅用于目录。

I find psql -E helpful 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 schema instead, which is standardized, but also comparatively slow.

在主要版本更新后，这样的专用查询可能会中断。 Postgres不保证目录表保持稳定。基本元素变化的可能性极小，但查询越复杂和专业化，机会就越大。您可以使用信息模式，这是标准化的，但也相对较慢。