select
table_catalog ,
table_schema ,
table_name ,
ordinal_position as sort,
column_name ,
data_type as TypeName,
(case
when (is_nullable = 'no' and contype !='p' ) then '1'
else null
end) as is_required,
(case
when contype = 'p' then '1'
else '0'
end) as is_pk,
coalesce(character_maximum_length, numeric_precision,-1) as Length,
numeric_scale as scale,
case
is_nullable when 'NO' then 0
else 1
end as canNull,
column_default as defaultval,
case
when position('nextval' in column_default)>0 then 1
else 0
end as IsIdentity,
(case
when position('nextval' in column_default)>0 then 1
else 0
end) as is_increment,
c.DeText as column_comment,
c.typname as column_type,
ordinal_position
from
information_schema.columns
left join (select
datname,pg_get_userbyid(relowner) AS tableowner,nspname,relname,attname
, col_description(pg_attr.attrelid, pg_attr.attnum) as DeText,typname,pg_cons.contype
from
pg_class
left join pg_attribute pg_attr on
pg_attr.attrelid = pg_class.oid
left join pg_description pg_desc on
pg_desc.objoid = pg_attr.attrelid
and pg_desc.objsubid = pg_attr.attnum
left join pg_namespace pg_ns on
pg_ns."oid" = pg_class.relnamespace
left join pg_database on relowner = datdba
left join pg_type on pg_attr.atttypid = pg_type."oid"
left join (select pg_con.*,unnest(conkey) conkey_new from pg_constraint pg_con) pg_cons on
pg_attr.attrelid = pg_class.oid
and pg_attr.attnum = pg_cons.conkey_new and pg_cons.conrelid = pg_class.oid
where
pg_attr.attnum>0
and pg_attr.attrelid = pg_class.oid
and nspname='public'
) c
on table_schema = nspname and table_name = relname and column_name = attname
where table_schema='public' and c.DeText like '%状态%'
order by table_name