用SQL获取postgreSQL表结构

 

SELECT
attname,typname,adsrc
     FROM
           pg_attribute
           INNER JOIN pg_class  ON pg_attribute.attrelid = pg_class.oid
           INNER JOIN pg_type   ON pg_attribute.atttypid = pg_type.oid
           LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum
           LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum
     WHERE
           pg_attribute.attnum > 0
          AND attisdropped <> 't'
           AND pg_class.relname= 'testexecute'  --testexecute为表名
     ORDER BY pg_attribute.attnum ;

第二种SQL

SELECT
                a.attnum,
                a.attname AS field,
                t.typname AS type,
                a.attlen AS length,
                a.atttypmod AS lengthvar,
                a.attnotnull AS notnull
        FROM
                pg_class c,
                pg_attribute a,
                pg_type t
        WHERE
                c.relname = 't_user'
                and a.attnum > 0
                and a.attrelid = c.oid
                and a.atttypid = t.oid
                and a.attname = 'passport'
        ORDER BY a.attnum