如何获得列使用PostgreSQL的表名属性查询？ [英] How to get column attributes query from table name using 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.

我实现获得列名称，位置数据类型和NOT NULL？与此查询：

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!

推荐答案

下面是对系统目录查询应该获取你需要的一切（有免费奉送奖金主键字段）。

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：列号


• 名称：列名


• 典型值：数据类型


• NOTNULL：是列定义为 NOT NULL


• 注释：任何注释为列定义


• primary_key：是列定义为 PRIMARY KEY


• 默认：使用默认值命令


• num: The column number
• name: The column name
• typ: the data type
• notnull: Is the column defined as NOT NULL
• comment: Any COMMENT defined for the column
• primary_key: Is the column defined as PRIMARY KEY
• default: The command used for the default value

这篇关于如何获得列使用PostgreSQL的表名属性查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！