Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL export table structure information     - Using IPFilter bridge filter in the FreeBSD system (Linux)

- Install Open vSwitch under CentOS 6.5 (Linux)

- MySQL IO SSD attempt at optimization (Database)

- Radius server setup under CentOS (Server)

- Handle large data problems Bit-map method (Programming)

- Linux compiler installation Redis (Database)

- VMware virtual machine to use bridged mode fast Internet access (Linux)

- How to install Unbound and DNSCrypt in Archlinux (Server)

- MongoDB common optimization settings in Linux (Database)

- Linux Nginx installation and configuration instructions (Server)

- How to install OpenOffice Ubuntu or Linux Mint (Linux)

- The simple multi-threaded Python (Programming)

- Safe learning through self-extracting package to achieve invasion (Linux)

- 30 minutes with your Quick Start MySQL Tutorial (Database)

- RedHat Linux 6.4 install Oracle 10g error (Database)

- CentOS 6.3 compile and install LNMP environment (Server)

- Linux 101 hack book reading notes (Linux)

- Install Jetty on CentOS / RHEL 6.X (Server)

- JavaScript subarray Deduplication (Programming)

- CentOS 6.5 install Firefox (Linux)

 
         
  PostgreSQL export table structure information
     
  Add Date : 2018-11-21      
         
         
         
  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
     
         
         
         
  More:      
 
- Struts2 form of non-use component tags (Programming)
- The Java utility, JavaMail (Programming)
- Difference in MySQL VARCHAR and CHAR data format (Database)
- How do you know your public IP terminal in Linux (Linux)
- Examples 14 grep command (Linux)
- To modify the existing user ID and comments GPG key (Linux)
- Linux User Management (Linux)
- C # / iOS / Android Universal Encryption and decryption (Programming)
- Linux file compression and file system packaged with instructions (Linux)
- Under Ubuntu on how to use iptables firewall (Linux)
- Android Studio and Git Git configuration file status (Linux)
- VMware6 achieve nat Internet (Linux)
- Use small network command to check whether PC Security (Linux)
- Detailed Linux network security policies and protection measures (Linux)
- Ubuntu 15.04 installation Powercommands 2.0 (Linux)
- Iscsi package is installed on RHEL 6.3 x86-64 systems (Linux)
- A list of the basics of Python, Ganso, Dictionary (Programming)
- Use Vagrant up a local development environment tutorials (Server)
- osprofiler use OpenStack Cinder Lane (Server)
- Iptables principle (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.