Postgresql 中查询表格列名和类型的sql语句

SELECT a.attname as col_name,a.atttypid as col_type FROM  pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(tableName )$' AND pg_catalog.pg_table_is_visible(c.oid))

 

可以得到所有列的列名以及列类型，列类型对应的值如下：

 

bigint            = 20;
smallint            = 21;
integer            = 23;
character        = 1042;
character_var    = 1043;
timestamp        = 1114;
double            = 701;