information_schema

PostgreSQL テーブル構成カラム一覧

カラム一覧出力SQL

以下スキーマ、テーブル名を適宜変更してお使いください。

select 
--c.table_schema,c.table_name,
c.column_name,c.ordinal_position as "position"
,c.data_type,c.character_maximum_length as "length"
,c.numeric_precision as "precision"
,c.numeric_scale  as "scale"
,c.is_generated
,c.column_default
from information_schema.columns c 
where c.table_name='kokudo_address_chome'   -- テーブル名
and c.table_schema = 'sooni'  -- スキーマ名
order by c.ordinal_position
;
カラム一覧出力例

is_generatedにて通常の列(NEVER)もしくは生成列(ALWAYS)なのか判定できます。

     column_name      | position |        data_type         | length | precision | scale | is_generated |  column_default
----------------------+----------+--------------------------+--------+-----------+-------+--------------+-------------------
 prefectures_code     |        1 | character varying        |      2 |           |       | NEVER        |
 prefectures_name     |        2 | character varying        |     32 |           |       | NEVER        |
 city_code            |        3 | character varying        |      5 |           |       | NEVER        |
 city_name            |        4 | character varying        |     64 |           |       | NEVER        |
 oaza_town_chome_code |        5 | character varying        |     12 |           |       | NEVER        |
 oaza_town_chome_name |        6 | character varying        |     32 |           |       | NEVER        |
 latitude             |        7 | numeric                  |        |        11 |     8 | NEVER        |
 longitude            |        8 | numeric                  |        |        11 |     8 | NEVER        |
 document_code        |        9 | integer                  |        |        32 |     0 | NEVER        |
 chome_kubun_code     |       10 | integer                  |        |        32 |     0 | NEVER        |
 creatation_date      |       11 | timestamp with time zone |        |           |       | NEVER        | CURRENT_TIMESTAMP
(11 行)


myposdb=#
カラムコメントも表示
select
 n.nspname as schema_name
,s.relname  as table_name
,c.ordinal_position as position
,a.attname as column_name
,d.description
/*
,c.data_type
,c.character_maximum_length as "length"
,c.numeric_precision as "precision"
,c.numeric_scale as "scale"
,c.is_generated
,c.column_default
*/
from pg_class as s
inner join pg_namespace as n on s.relnamespace = n.oid
inner join pg_attribute as a on s.oid = a.attrelid
inner join information_schema.columns c on n.nspname = c.table_schema
      and s.relname = c.table_name
      and a.attname = c.column_name
left outer join pg_description as d on s.oid = d.objoid and a.attnum = d.objsubid
where
    n.nspname not like 'pg_%' and
    n.nspname != 'information_schema'
order by
    schema_name,
    table_name,
    c.ordinal_position
;
実行例
myposdb=# select
myposdb-#  n.nspname as schema_name
myposdb-# ,s.relname  as table_name
myposdb-# ,c.ordinal_position as position
myposdb-# ,a.attname as column_name
myposdb-# ,d.description
myposdb-# /*
myposdb*# ,c.data_type
myposdb*# ,c.character_maximum_length as "length"
myposdb*# ,c.numeric_precision as "precision"
myposdb*# ,c.numeric_scale as "scale"
myposdb*# ,c.is_generated
myposdb*# ,c.column_default
myposdb*# */
myposdb-# from pg_class as s
myposdb-# inner join pg_namespace as n on s.relnamespace = n.oid
myposdb-# inner join pg_attribute as a on s.oid = a.attrelid
myposdb-# inner join information_schema.columns c on n.nspname = c.table_schema
myposdb-#       and s.relname = c.table_name
myposdb-#       and a.attname = c.column_name
myposdb-# left outer join pg_description as d on s.oid = d.objoid and a.attnum = d.objsubid
myposdb-# where
myposdb-#     n.nspname not like 'pg_%' and
myposdb-#     n.nspname != 'information_schema'

myposdb-# order by
myposdb-#     schema_name,
myposdb-#     table_name,
myposdb-#     c.ordinal_position
myposdb-# ;
 schema_name |   table_name   | position |  column_name  |  description
-------------+----------------+----------+---------------+----------------
 sooni       | kokudo_address |        1 | prefectures   | 都道府県名
 sooni       | kokudo_address |        2 | city          | 市区町村名
 sooni       | kokudo_address |        3 | town          | 大字・丁目名
 sooni       | kokudo_address |        4 | town_sup      | 小字・通称名
 sooni       | kokudo_address |        5 | section_chome | 街区符号・地番
 sooni       | kokudo_address |        6 | concatenation | 結合住所
 sooni       | kokudo_address |        7 | latitude      | 緯度
 sooni       | kokudo_address |        8 | longitude     | 経度
(8 rows)


myposdb=#

SQLでテーブル一覧を出力する

スポンサーリンク