开发笔记--Navicat导出postgresql表结构数据成excel文件

场景描述：

     项目开发中，模型设计经常用powerdesigner来操作，当我们需要给别人介绍表结构或者一些文档交付的时候，需要Excel表格样式的数据，

     这个时候，可以用工具Navicat来实现。

操作步骤：

     注：本文针对postgresql数据库

     1. 执行如下sql语句，查看表结构

SELECT 
  A.attnum AS "序号",
    ---C.relname AS "表名",
    ---CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",
    A.attname AS "字段名",
    A.attnotnull as 是否为空,
    ---IF(A.attnotnull='f','是','否') AS '必填',
    concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS "数据类型",
    d.description 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

2. 点击运行，小三角，切换到查询结果界面

3. 如果执行正常，确认结果后，点击上边的“导出结果”按钮，

弹出如下窗口：

点击“Export Current Result”，勾选Excel文件，Continue继续，按着提示继续保存就可以。

 

--OK

参考：

Navicat导出mysql表结构：https://www.cnblogs.com/xianxiaobo/p/10254737.html

SELECT
COLUMN_NAME 字段名称,
COLUMN_TYPE 数据类型,
IF(IS_NULLABLE='NO','是','否') AS '必填',
COLUMN_COMMENT 注释
FROM
INFORMATION_SCHEMA.COLUMNS
where
-- Finance为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
table_schema ='Finance'
AND
-- user为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了
table_name = 'user'

https://blog.csdn.net/u010189683/article/details/84947193

查询Postgresql系统表，显示列名...: https://blog.csdn.net/weixin_43236610/article/details/88077484

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, ad.adsrc
                  from pg_class c,
                       pg_attribute a,
                       pg_attrdef ad
                  where relname = 'table_name'
                    and ad.adrelid = c.oid
                    and adnum = a.attnum
                    and attrelid = c.oid) as d on a.attname = d.attname
where c.relname = 'table_name'
  and a.attrelid = c.oid
  and a.attnum > 0;