postgresql 获取表属性结构信息.-CSDN博客

如何获取用户自定义表结构信息

1. 获取表中普通信息：如字段名，字段类型等等。

SELECT column_name, data_type, ordinal_position, is_nullable 
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME' -- 将 'TABLE-NAME' 换成自己的表。

2.需要获取如comment，主外键等。（获取的信息比较全面，得到自己想要的信息。）

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.

转载于:https://my.oschina.net/longtian/blog/741303