PostgreSQL 匯出表結構資訊

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語句，然後將查詢結果匯出

使用英文逗號做分隔字元，匯出csv檔案，可以直接在excle中編輯

使用本地字元集可以防止亂碼

 用excle開啟匯出的csv檔案，因為匯出的是所有的表結構資訊，所以要先分表，拷貝第一行表頭資訊，分別插入到每張表的前面（插入行的快速鍵自己網上找吧）。

做完上面的步驟，就可以把表結構資訊粘帖到word文檔裡了；

在粘貼完畢後，要選擇使用目標格式

表格出來了

這個時候所有的表結構是一張大表格，可以用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 

------------------------------------華麗麗的分割線------------------------------------

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡

本文永久更新連結地址：