用SQL获取postgreSQL表结构

最新推荐文章于 2025-06-20 17:44:47 发布

转载最新推荐文章于 2025-06-20 17:44:47 发布 · 9.1k 阅读

文章标签：

#postgresql #sql #join #class #user #c

本文介绍两种用于查询特定表结构的SQL语句，包括字段名称、类型、长度等详细信息，适用于PostgreSQL数据库环境。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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