PGSQL 导出数据库表结构

之前要将数据库的表结构给做成markdow来写开发设计文档或是接口文档,去找各种开源工具、组件。

整理了一个SQL语句可以查询出表结构,样式如下
在这里插入图片描述
SQL语句,里面的jiahui表示数据库的schema,默认是public

SELECT CASE
           WHEN t.attnum = -2 THEN
            NULL
           WHEN t.attnum = -1 THEN
            '表名'
       
           ELSE
            t.relname
       END 表名,
       t.attname 字段名,
       t.atttype 字段类型或表约束,
       t.isnotnull 是否可为空,
       t.attrdef 默认值,
       t.description 注释
  FROM (SELECT b.relname,
               a.attnum,
               a.attname,
               format_type(a.atttypid, a.atttypmod) atttype,
               CASE
                   WHEN a.attnotnull = TRUE THEN
                    '不可空'
                   ELSE
                    '可空'
               END isnotnull,
               (SELECT 
                       pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) 
                  FROM pg_attrdef d
                 WHERE d.adrelid = a.attrelid
                   AND d.adnum = a.attnum
                   AND a.atthasdef) attrdef,
               c.description
          FROM pg_attribute a
          JOIN pg_class b
            ON a.attrelid = b.oid
          LEFT JOIN pg_description c
            ON a.attrelid = c.objoid
           AND c.objsubid = a.attnum
         WHERE a.attnum > 0
           AND a.attisdropped = 'f'
           AND b.relnamespace = 'jiahui'::regnamespace
           AND b.relkind = 'r'
           AND NOT a.attisdropped
        UNION ALL
        SELECT b.relname,
               -2,
               NULL,
               NULL,
               NULL,
               NULL,
               NULL
          FROM pg_class b
         WHERE b.relnamespace = 'jiahui'::regnamespace
           AND b.relkind = 'r'
        UNION ALL
        SELECT b.relname,
               -1,
               '字段名',
               '字段类型或表约束',
               '是否可为空',
               '默认值',
               '注释'
          FROM pg_class b
         WHERE b.relnamespace = 'jiahui'::regnamespace
           AND b.relkind = 'r'
        UNION ALL
        SELECT b.relname,
               0,
               NULL,
               string_agg(CASE
                              WHEN contype = 'p' THEN
                               '主键约束'
                              WHEN contype = 'c' THEN
                               '检查约束'
                              WHEN contype = 'f' THEN
                               '外键约束'
                              WHEN contype = 'u' THEN
                               '唯一约束'
                              WHEN contype = 't' THEN
                               '约束触发器'
                              WHEN contype = 'x' THEN
                               '排除约束'
                          END || ':' || conname || ' ' ||
                          pg_get_constraintdef(a.oid, 't'),
                          ';'),
               NULL,
               NULL,
               (SELECT description
                  FROM pg_description
                 WHERE a.conrelid = objoid
                   AND objsubid = 0)
          FROM pg_constraint a
          JOIN pg_class b
            ON a.conrelid = b.oid
         WHERE a.connamespace = 'jiahui'::regnamespace
           AND b.relnamespace = 'jiahui'::regnamespace
           AND b.relkind = 'r'
         GROUP BY conrelid,
                  b.relname) t
 ORDER BY t.relname,
          t.attnum offset 1;
  • 5
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值