カラムサイズの取得

Question

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

chukenkenkou · Accepted Answer

#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 · Answer

列の定義長は、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

カラムサイズの取得

#1回答者です。

列の定義長は、intやdateなど固定もものは、pg_attribute表のattlen列に入っています。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング