我有一个项目，我需要一个查询以使用表名来获取所有列的所有属性（列名，位置，数据类型，非空值和注释）。
我实现了获取列名，位置数据类型和不为空？与此查询：
SELECT column_name, data_type, ordinal_position, is_nullable FROM information_schema."columns" WHERE "table_name"='TABLE-NAME'
但是，我需要评论！
这是针对系统目录的查询，该系统目录应获取您需要的所有内容（免费提供额外的主键字段）。
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;
这将返回如下结果：
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
NOT NULL
COMMENT
PRIMARY KEY
