PostgreSQLでテーブル構造等を取得するSQL

自分用メモ

PostgreSQLでテーブル構造を取得したい場合に使用するSQL文

select         n.nspname       ,c.relkind       ,c.relname       ,a.attnum       ,obj_description(c.relfilenode, 'pg_class' ::name) as tablecomment       ,a.attname       ,format_type(a.atttypid, a.atttypmod) AS dataname       ,col_description(a.attrelid, a.attnum ::integer) as columncomment       ,(select distinct case                  when a.attnum = any(i1.indkey) and i1.indisprimary = true then                   true                  else                   null                end           from pg_index as i1          where c.relfilenode =i1.indrelid            and a.attnum = any(i1.indkey)        ) as primaryindex       ,(select distinct case                  when a.attnum = any(i2.indkey) then                   true                  else                   false                end           from pg_index as i2          where c.relfilenode = i2.indrelid            and a.attnum = any(i2.indkey)        ) as indexflg        ,(select adsrc from pg_attrdef as d where d.adrelid=c.oid and d.adnum = a.attnum) as defaultvalue  from pg_class     as c      ,pg_attribute as a      ,pg_namespace as n where c.relfilenode = a.attrelid    and c.relnamespace = n.oid   and a.attnum > 0   --↑(システム列を見せたくない場合)   and c.relkind = 'r'    --↑(オブジェクトの種類を指定したい場合：r=テーブル,S(大文字)=シーケンス,i=インデックス,v=ビュー,c=複合型,s(小文字)=特別,t=TOASTテーブル)   and n.nspname = 'ownername'    --↑ 表示したい所有者を指定したい場合 order by n.nspname, c.relkind, c.relname, a.attnum