Obtain information about the fields in the PostgreSQL database table (Name, Type, Len, PK, AutoIncrease, AllowNullable)

In order to find this information, I can work hard to find a possible Bug in PostgreSQL, and check the source code of PostgreSQL to guess how to obtain fields with the auto-growth attribute. But the overall feeling is much more reasonable than the data structure of ms SQL SERVER. You can look for how to get the primary key attributes of the ms SQL statement. I have formatted the statement to help you read it. The post is a microcontribution to free software. Although the statement does not obtain all the information, it is difficult to obtain all the information. It is not a problem to follow this idea.

Select tbl. relname as TableName,
Col. attname as ColumnName,
Pg_type.typname as ColumnType,
(Case when col. attlen <0 then col. atttypmod else col. attlen end) as ColumnLen,
(Select count (*) from pg_constraint ct where ct. contype = p: char and col. attnum = any (conkey) and ct. conrelid = tbl. oid) as IsPk,
(Case when seq. oid is null then 0 else 1 end) as IsAutoIncrease,
(Case when col. attnotnull = false then 1 else 0 end) as AllowNullable
From pg_attribute col
Inner join pg_class tbl on col. attrelid = tbl. oid and tbl. relkind = r: char
Left join pg_depend dp on tbl. oid = dp. refobjid and col. attnum = dp. refobjsubid and deptype = I: char
Left join pg_class seq on dp. objid = seq. oid and seq. relkind = S: char
Inner join pg_namespace space on tbl. relnamespace = space. oid and space. nspname <> pg_catalog: name and space. nspname <> information_schema: name and space. nspname <> pg_toast: name
Left join pg_type on col. atttypid = pg_type.oid
Where col. attnum> 0
Order by tbl. relname, col. attnum

Links
PostgreSQL Official Site
PostgreSQL Chinese siteIn SQL SERVER2000:

SELECT
(Case when a. colorder = 1 then d. name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' 'end) n' ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a. id) AND (name = a. name) AND
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. name n' type ',
A. length N 'number of bytes occupied ',
COLUMNPROPERTY (a. id, a. name, 'precision ') as N 'length ',
Isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (e. text, '') n' default value ',
Isnull (g. [value], '') AS n' field description'
-- Into # tx

FROM syscolumns a left join policypes B
On a. xtype = B. xusertype
Inner join sysobjects d
On a. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties'
Left join syscomments e
On a. cdefault = e. id
Left join sysproperties g
On a. id = g. id AND a. colid = g. smallid
Order by object_name (a. id), a. colorder

 

Replace sysproperties with extended_properties in SQL Server2005.