カラムサイズの取得

質問

度々の質問で少々気恥ずかしいのですが・・・

先日カラム一覧を取得する方法として、多くのRDBMSではシステムカタログを
参照するのが一般的な方法ということを教えて頂きました。
現在pgAdminIIIを使って、システムカタログの中がどうなっているのか、
色々勉強しているのですが、カラムサイズを取得するにはどこを参照すれば
良いのでしょうか？　たとえばvarchar(40)のカラムがあった場合に「40」
という数値を取得したいのですが・・・

どなたかご存知であれば、ご教授下さい。

回答No.2

chukenkenkou · ベストアンサー

#1回答者です。

PostgreSQLはデータ型が豊富なためか、システムカタログの参照方法が分かりにくいですね。
表中にシステム用の列も作っており、そのまま定義情報を検索すると、ユーザの定義列と一緒に情報を拾ってしまいます。

#1のSQLから、対応するデータ型の追加、「_bpchar」→「char」といった表示変更をするようにしました。

#1と併せて実行してもらえると、違いが分かりやすいと思います。

select
relname as 表名,
attname as 列名,
attnum as 列番,
case typname
when '_bpchar' then 'char'
when '_varchar' then 'varchar'
when '_date' then 'date'
when '_float8' then 'float8'
when '_int4' then 'integer'
when '_interval' then 'interval'
when '_numeric' then 'numeric'
when '_float4' then 'float4'
when '_int2' then 'smallint'
when '_text' then 'text'
when '_time' then 'time'
when '_timestamp' then 'timestamp'
end as 型,
case typname
when '_bpchar' then atttypmod - 4
when '_varchar' then atttypmod - 4
when '_numeric' then (atttypmod - 4) / 65536
else attlen
end as 長さ,
case typname
when '_numeric' then (atttypmod - 4) % 65536
else 0
end as 小数

from pg_stat_user_tables as a,
pg_attribute as b,
pg_type as c
where schemaname='u1'
and relname='t1'
and a.relid=b.attrelid
and b.attnum>0
and b.atttypid=c.typelem
and substr(typname,1,1)='_'
order by schemaname,relname,attnum;

お礼

chukenkenkouさん、こんばんは。二度も助けて頂いて恐縮です。
ちょっとバタバタとしており、お返事が遅くなったこと、また、せっかく
ご回答を戴いたのに、未だ自分自身で確認ができておりませんことを
お詫び致しますm(_ _)m　
近日中に自分自身の手で確認してみようと思います。
いつも丁寧なご回答を戴き、本当にありがとうございます。

回答No.1

chukenkenkou

列の定義長は、intやdateなど固定もものは、pg_attribute表のattlen列に入っています。
charやvarchar,decimalなどは、pg_attribute表のatttypmod列に入っています。
（長さを管理するための4バイトが、付加されています）

データ型の種類(char,varchar等）は、pg_type表のtypname列に入っています。
ただ、格納形式はcharの場合、「_bpchar」のように入っています。

【試作したSQL】
select
relname as 表名,
attname as 列名,
attnum as 列番,
typname as 型,
case typname
when '_bpchar' then atttypmod - 4
when '_varchar' then atttypmod - 4
when '_numeric' then (atttypmod - 4) / 65536
when '_decimal' then (atttypmod - 4) / 65536
else attlen
end,
case typname
when '_numeric' then (atttypmod - 4) % 65536
when '_decimal' then (atttypmod - 4) % 65536
else 0
end
from pg_stat_user_tables as a,
pg_attribute as b,
pg_type as c
where schemaname='u1'
and a.relid=b.attrelid
and b.attnum>0
and b.atttypid=c.typelem
order by schemaname,relname,attnum;

●他の方が作成していたSQL
http://www.shonan.ne.jp/~nkon/cslpg.a.semi/mgp00029.txt

●pg_attribute表
http://www.postgresql.jp/document/pg801doc/html/catalog-pg-attribute.html

●pg_type表
http://www.postgresql.jp/document/pg801doc/html/catalog-pg-type.html

お礼

このコミュニティーを上手く使いこなせていないので、お礼のやり方が変に
なりましたが、どうしてもお礼が言いたかったので、ここに記入しました。
chukenkenkouさん、いつも的確なご返答を戴き大変感謝しております。
#2の方法で、必要としている情報が一目瞭然でした。本当にありがとうございました。