pgsql表结构导出成excel格式:
第一种 实测可行:
SELECT
concat(A.attnum,'.' )AS "序号",
d.description AS "注释" ,
---C.relname AS "表名",
---CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",
A.attname AS "字段名",
concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '(.*)' ) ) AS "数据类型" ,
-- A.attnotnull as 是否为空,
'N' as "是否索引",
'Y' as "是否空"
-- if(A.='f','是','否') AS '必填',
--T.typname,
-- (case when A.attlen > 0 then A.attlen else A.atttypmod - 4 end) as 长度,
FROM
pg_class C,
pg_attribute A,
pg_type T,
pg_description d
WHERE
C.relname = '表名'
AND A.attnum > 0
AND A.attrelid = C.oid
AND A.atttypid = T.oid
AND d.objoid = A.attrelid
AND d.objsubid = A.attnum
ORDER BY
C.relname DESC,
A.attnum asc
——————————————————————————————————————————————————
select * from pg_type
——————————————————————————————————————————————————
第二种:
select a.attname as 列名,
format_type(a.atttypid, a.atttypmod) as 类型,
(case when a.attlen > 0 then a.attlen else a.atttypmod - 4 end) as 长度,
a.attnotnull as 是否可为空,
-- d.adsrc as 默认值,
col_description(a.attrelid, a.attnum) as 备注
from pg_class c,
pg_attribute a
left join (select a.attname --, 0 as adsrc
from pg_class c,
pg_attribute a,
pg_attrdef ad
where relname = '表名'
and ad.adrelid = c.oid
and adnum = a.attnum
and attrelid = c.oid) as d on a.attname = d.attname
where c.relname = '表名'
and a.attrelid = c.oid
and a.attnum > 0;
————————————————————————————————————————
select * from pg_attribute