要查pg 的表結構，除了登陸psql 客戶端執行\d table_name,另一種方法就是通關SQL 查詢，
如下：看起來好像很複雜。
SELECT b.table_name,a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type ,col_description(a.attrelid,a.attnum) as comment
FROM  pg_catalog.pg_attribute a,
       (SELECT  c.oid,c.relname as table_name
          FROM  pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n  ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'
         and n.nspname = 'cbs') b
WHERE a.attrelid = b.oid
   AND a.attnum > 0
   AND NOT a.attisdropped
ORDER BY b.table_name,a.attnum

PG 資料庫查詢欄位屬性的指令碼

其中
SELECT  c.oid,c.relname as table_name
          FROM  pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n  ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'   -- 型別是表
         and n.nspname = 'cbs_app'  --schema_name

這部分查了某個schema 下面所有的表
根據表的oid 再到 pg_attribute  查對應屬性

pg_catalog.format_type(atttypid,atttypmod) 查表的欄位型別
pg_catalog.col_description(a.attrelid,a.attnum) 查欄位的comment
attnum 指定了該列在表中的位置從上到下按1...n 排序

PG 資料庫查詢欄位屬性的指令碼

相關文章