pgsql表结构导出成excel格式

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 
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值