POSTGRESQL Export Table structure information

Project team to the table structure of the document, handwriting is too cumbersome to use the SLQ script to export a copy.

--Queries 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 if Atttypmod-4>0 then atttypmod-4 else 0 end) Data_length,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' P ') >0 then ' Y ' Else ' N ' end) as PRIMARY KEY constraint,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' u ') >0 Then ' Y ' Else ' N ' end) as the only constraint,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' F ') >0 then ' Y ' Else ' N ' end) as foreign KEY constraint,

(case is 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 OIDs from Pg_class where Relname in (select Relname from Pg_class where Relki nd = ' r ' and relname like ' exg_% '))

Order BY Table_name,a.attnum;

Execute the SQL statement and export the query results

Use comma delimiter, export CSV file, can be edited directly in Excle

Use local character set to prevent garbled characters

?

Attached: Other SQL scripts

--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_c Lass 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 if Atttypmod-4>0 then atttypmod-4 else 0 end) Data_length,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' P ') >0 then ' Y ' Else ' N ' end) as PRIMARY KEY constraint,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' u ') >0 Then ' Y ' Else ' N ' end) as the only constraint,

(Case if (select COUNT (*) from pg_constraint where conrelid = A.attrelid and Conkey[1]=attnum and contype= ' F ') >0 then ' Y ' Else ' N ' end) as foreign KEY constraint,

(case is 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 name

POSTGRESQL Export Table structure information