カラム一覧出力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=#
