pgsql获取数据所有的表的字段和字段属性

select

DISTINCT ORDINAL_POSITION as rn,

table_name as "tb_name",

column_name as "en_name",

(case udt_name WHEN 'numeric' THEN 'number' WHEN 'decimal' THEN 'number' WHEN 'int4' THEN 'number' WHEN 'int8' THEN'number'WHEN 'timestamp' THEN 'datetime' WHEN 'date' THEN 'datetime' WHEN 'datetime' THEN 'datetime' ELSE 'string' END ) as "data_type",coalesce(character_maximum_length,numeric_precision,-1) as "data_length",

coalesce(numeric_scale,0) as "precision_length",

case when position('nextval' in column_default)>0 then '1' else '0' end as "is_identity",

case when b.pk_name is null then 0 else '1' end as "is_pk",

case is_nullable when 'NO' then '0' else '1' end as "is_null",

 

c.DeText as remarks,

b.description as description ,

COLUMN_DEFAULT as "default_value"

from information_schema.columns

left join (
--B.description as description,
SELECT DISTINCT A.table_name as pk_table_name,B.attname as colname,B.attnum as attnum,

B.description as description ,

CASE WHEN length(B.attname) >0 THEN 1 ELSE NULL END AS pk_name

FROM information_schema.columns A

LEFT JOIN(

SELECT pg_attribute.attname, pg_attribute.attnum,pg_description.description as description

FROM pg_index, pg_class, pg_attribute ,pg_description

WHERE 1=1

AND pg_index.indrelid = pg_class.oid

AND pg_attribute.attrelid = pg_class.oid

and pg_description.objoid=pg_attribute.attrelid and pg_description.objsubid=pg_attribute.attnum

AND pg_attribute.attnum = ANY (pg_index.indkey)

)B ON A.column_name = b.attname

WHERE A.table_schema = current_schema()

and is_nullable='NO'

) b on b.colname = information_schema.columns.column_name and b.pk_table_name = information_schema.columns.table_name
left join (

SELECT col_description(a.attrelid,a.attnum) as DeText,a.attname as attname, c.relname as tbname,
c.relname as relname

FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0

)c on c.attname = information_schema.columns. column_name and c.relname=information_schema.columns.table_name

where table_schema=current_schema()

order by table_name,rn asc