2007-10-19

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
コメントを投稿