我有一个项目,我需要一个查询来获取列的所有属性(列名,位置,数据类型,不空和注释)所有这些使用表名.
我获得了列名,位置数据类型和非空？用这个查询：
SELECT column_name,data_type,ordinal_position,is_nullable FROM information_schema."columns" WHERE "table_name"='TABLE-NAME'
但是,我需要评论！
这是针对
system catalog的查询,应该会获取所需的一切(免费提供奖金主键字段).
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
> num：列号> name：列名> typ：数据类型> notnull：列定义为NOT NULL> comment：为列定义的任何COMMENT> primary_key：列定义为PRIMARY KEY> default：用于默认值的命令
