PostgreSQL exports table structure information

PostgreSQL exports table structure information

The project uses a Postgresql database. The project team needs to write a table structure document, which is too troublesome. I want to use the slq script to export a document. I checked some information and it seems that there are no good methods. The script exported in dump mode is too messy and cannot be directly written into Word documents. You can only write SQL statements to query the table structure, and then use pgadmin's export query result function to obtain the table structure information of the entire database as quickly as possible. Although automatic export of documents cannot be implemented, it still saves a lot of time for organizing documents.

-- Query all table field information (with table name)

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 =. attrelid and conkey [1] = attnum and contype = 'P')> 0 then 'y' else 'n' end) as primary key constraint,

(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'U')> 0 then 'y' else 'n' end) as unique constraint,

(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'F')> 0 then 'y' else 'n' end) as 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

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 the SQL statement and export the query result

Use commas as separators to export csv files and edit them directly in excle.

Use the local character set to prevent garbled characters

Use excle to open the exported csv file. Because all table structure information is exported, you must first split the table and copy the header information of the first row, insert them to the front of each table (you can find the shortcut key of the inserted row on the Internet ).

After completing the above steps, you can paste the table structure information into the word document;

After pasting, select the target format.

The table is displayed.

At this time, all the table structures are a large table. You can use Ctrl + shift + enter to separate the tables.

Appendix: Other SQL scripts

-- Query the 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 table 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 =. attrelid and conkey [1] = attnum and contype = 'P')> 0 then 'y' else 'n' end) as primary key constraint,

(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'U')> 0 then 'y' else 'n' end) as unique constraint,

(Case when (select count (*) from pg_constraint where conrelid =. attrelid and conkey [1] = attnum and contype = 'F')> 0 then 'y' else 'n' end) as 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

Where attstattarget =-1 and attrelid = (select oid from pg_class where relname = 'exg _ ms_alarm '); -- table name

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: