postgresql 获取表属性结构信息 https://my.oschina.net/longtian/blog/741303
1. 获取表中普通信息：如字段名，字段类型等等。
|
1 2 3 |
SELECT column_name, data_type, ordinal_position, is_nullable FROM information_schema."columns" WHERE "table_name"='TABLE-NAME' -- 将 'TABLE-NAME' 换成自己的表。 |
2.需要获取如comment，主外键等。（获取的信息比较全面，得到自己想要的信息。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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; |
参考链接：
http://stackoverflow.com/questions/15928118/how-to-get-column-attributes-query-from-table-name-using-postgresql
