| |
Postgresql database project with the project team to the table structure of the document, handwritten too much trouble, with slq want to export a script. Check a lot of information, it seems that there is no more good method. Export dump script way too messy, can not be directly written in the word document. You can only write sql query the table structure, and then use the function pgadmin export query results, and can obtain a copy of the fastest table structure information for the entire database. Although it can not achieve automatic export documents, but to organize documents, or saving a lot of time.
- Discover all field information table (with table)
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 the primary key constraint,
(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 a unique constraint,
(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 a foreign key constraint,
(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;
Execute sql statement, and then export the query results
Comma delimiters, exported csv file that can be edited directly in the excle
Use local character set can prevent distortion
Open the exported csv file using excle, because of all the export table structure information, so the first sub-table, a copy of the first row header information, are inserted into each table in front of (the shortcut key to insert the line to find it yourself online ).
Done the above steps, you can paste the information into the table structure in a word document
After the paste is completed, you want to choose the target format
All this time table structure is a large table, you can use Ctrl + shift + enter to form separate
Appendix: Other sql script
- Query table name and description
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;
- Query Field Information
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 the primary key constraint,
(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 a unique constraint,
(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 a foreign key constraint,
(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'); - table |
|
|
|