postgres中的describe table 实现方式

一种方式是进入psql控制台 ， 直接 /d tablename 就可查看表 tablename的结构信息了，但这种方式要在程序中用不太方便，所以就有下面的sql查询方式：

 

SELECT a.attnum, a.attname AS field, t.typname AS type, format_type(a.atttypid, a.atttypmod) AS complete_type, a.attnotnull AS isnotnull, ( SELECT 't' FROM pg_index WHERE c.oid = pg_index.indrelid AND pg_index.indkey[0] = a.attnum AND pg_index.indisprimary = 't') AS pri, (SELECT pg_attrdef.adsrc FROM pg_attrdef WHERE c.oid = pg_attrdef.adrelid AND pg_attrdef.adnum=a.attnum) AS default FROM pg_attribute a, pg_class c, pg_type t WHERE c.relname = '%s' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum

 

把%s替换为要查的表名即可