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