PostgreSQL 导出表结构信息

项目用了Postgresql 数据库,项目组要出表结构的文档,手写太麻烦,想用slq脚本导出一份。查了一番资料,似乎没有多好的方法。dump方式导出的脚本太乱,没法直接写在word文档里。只能自己写sql查询出表结构,然后利用pgadmin的导出查询结果的功能,能最快的获取一份整个数据库的表结构信息。虽然不能实现全自动的导出文档,但是对整理文档来说,还是节省了不少时间。

--查询所有的表字段信息(带表名)

select

(select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name,

a.attname as column_name,

format_type(a.atttypid,a.atttypmod) as data_type,

(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,

(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,

col_description(a.attrelid,a.attnum) as comment

from pg_attribute a

where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' and relname like 'exg_%'))

order by table_name,a.attnum;

执行sql语句,然后将查询结果导出

PostgreSQL 导出表结构信息

使用英文逗号做分隔符,导出csv文件,可以直接在excle中编辑

使用本地字符集可以防止乱码

PostgreSQL 导出表结构信息

用excle打开导出的csv文件,因为导出的是所有的表结构信息,所以要先分表,拷贝第一行表头信息,分别插入到每张表的前面(插入行的快捷键自己网上找吧)。

PostgreSQL 导出表结构信息

做完上面的步骤,就可以把表结构信息粘帖到word文档里了;

PostgreSQL 导出表结构信息

在粘贴完毕后,要选择使用目标格式

PostgreSQL 导出表结构信息

表格出来了

PostgreSQL 导出表结构信息

这个时候所有的表结构是一张大表格,可以用Ctrl+shift+enter把表格分开

附:其它sql脚本

--查询表名和描述

select relname as table_name,(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where relkind ='r' and relname like 'exg_%' order by table_name;

--查询表字段信息

select

a.attname as column_name,

format_type(a.atttypid,a.atttypmod) as data_type,

(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,

(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,

col_description(a.attrelid,a.attnum) as comment

from pg_attribute a

where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='exg_ms_alarm');--表名

------------------------------------华丽丽的分割线------------------------------------

CentOS 6.3环境下yum安装PostgreSQL 9.3

PostgreSQL缓存详述

Windows平台编译 PostgreSQL

Ubuntu下LAPP(Linux+Apache+PostgreSQL+PHP)环境的配置与安装

Ubuntu上的phppgAdmin安装及配置

CentOS平台下安装PostgreSQL9.3

PostgreSQL配置Streaming Replication集群

如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin 

------------------------------------华丽丽的分割线------------------------------------

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:http://www.heiqu.com/f955dfe77bf72aecb7c70e4da644e078.html