[postgres] table, column info

스키마 정보 조회

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

<code>select   a.relname , b.description , b.description , d.attname , c.description --, e.data_type , e.udt_name , case     when e.data_type = 'character varying' then text(e.character_maximum_length)     when e.data_type = 'numeric' then text(e.numeric_precision) || ',' || text(e.numeric_scale)     when e.data_type = 'bigint' then  '8' --text(e.numeric_precision)     when e.data_type = 'integer' then '4' --text(e.numeric_precision)     when e.data_type = 'bit' then text(e.character_maximum_length)     when e.data_type = 'timestamp without time zone' then '8' --text(e.datetime_precision)     when e.data_type = 'date' then '8'     when e.data_type = 'text' then '65535' end as type_size , case when f.column_name is not null then 'Y' else 'N' end , case when e.is_nullable = 'YES' then 'Y' else 'N' end --, case when d.attnotnull = true then 'Y' else 'N' end , e.column_default , case when e.udt_name = 'date' then 'YYYYMMDD'        when e.udt_name = 'timestamp' then 'YYYYMMDDHH24MISS'        else '' end as format , c.description from  PG_STAT_ALL_TABLES a left outer join PG_DESCRIPTION b on b.objoid = a.relid and b.objsubid = 0 left outer join PG_DESCRIPTION c on c.objoid = a.relid left outer join PG_ATTRIBUTE d on d.attrelid = c.objoid and d.attnum = c.objsubid left outer join information_schema.columns e on e.table_name = a.relname and  e.column_name = d.attname left outer join ( -- pk find                 select a.constraint_schema          , a.constraint_name          , b.table_name          , b.column_name           from    information_schema.table_constraints a   left outer join information_schema.key_column_usage b on b.constraint_name = a.constraint_name           and b.constraint_schema = a.constraint_schema           and b.constraint_name = a.constraint_name   where a.constraint_type = 'PRIMARY KEY' ) f on f.table_name = a.relname and f.column_name = d.attname where a.SCHEMANAME = 'public' and d.attname is not null order by a.relname, d.attname asc       </code>