postgresql查询表元数据信息语句

一. 常用postgresql命令

连接数据库服务器，切换至postgres(或其他数据库用户)下

1、进入数据库命令行终端：psql

2、显示数据库：\l

3、连接指定的数据库：\c 数据库名

4、查看当前实例下的表：\dt

5、用sql语句方式查询：SELECT tablename FROM pg_tables;

6、查询某张表的表结构及字段信息：\d tablename--实际表名

二. 用到的postgresql系统表

1、 pg_class

记录了数据库中的表，索引，序列，视图（“关系”）。

其中比较重要字段有：

(1)relname 表，索引，视图等的名字。

(2)relnamespace 包含这个关系的名字空间（模式）的 OID，对应pg_namespace.oid

(3)relkind r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表

2、 pg_namespace

记录了数据库的名字空间（模式）

其中比较重要的字段有：

（1）nspname 名字空间的名字

（2）nspowner 名字空间的所有者

3、 pg_attribute

记录了数据库关于表的字段的信息。

其中比较重要的字段有：

（1）attrelid 此列/字段所属的表，对应于pg_class.oid

（2）attname 字段名字

（3）atttypid 这个字段的数据类型,对应于pg_type.oid

（4）attlen 对于定长类型，typlen是该类型内部表现形式的字节数目。对于变长类型，typlen 是负数。-1 表示一种"变长"类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。是本字段类型 pg_type.typlen 的拷贝。

（5）attnum 字段数目。普通字段是从 1 开始计数的。系统字段， 比如 oid， 有（任意）正数。

（6）atttypmod atttypmod 元组在创建表的时候 提供的类型相关的数据（比如，一个 varchar 字段的最大长度）。它传递给类型相关的输入和长度转换函数当做第三个参数。其值对那些不需要 atttypmod 的类型而言通常为 -1。

（7）attnotnull 这代表一个非空约束。我们可以改变这个字段以打开或者关闭这个约束。

（8）attisdropped 这个字段已经被删除了，不再有效。

注意：

如果字段类型为变长类型（如varchar）,那么在atttypmod中存储的长度比实际长度多4。可见参考文档1。

如果字段类型为numeric，那么可通过atttypmod获得长度、精度等信息，具体方式可见参考文档2。

4、 pg_type

记录了数据库有关数据类型的信息。

其中比较重要的字段有：

4.1 typname 数据类型名字

4.2 typlen 对于定长类型，typlen是该类型内部表现形式的字节数目。对于变长类型，typlen 是负数。-1 表示一种"变长"类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。

5、 pg_description

记录了数据库中对象（表、字段等）的注释。

其中比较重要的字段有：

5.1objoid 这条描述所描述的对象的 OID。如果这条注释是一个表或表中字段的注释，那么，该值对应于pg_class.oid

5.2objsubid 对于一个表字段的注释，它是字段号，对应于pg_attribute.attnum。对于其它对象类型，它是零。

5.3description 作为对该对象的描述的任意文本

三. 查询指定模式下表字段信息SQL语句

# 方式1：（支持查询指定模式schema下某张表的字段信息）
  SELECT  a.attname AS field, t.typname AS type
  FROM 
    (select c.relname,c.oid,n.nspname from pg_class c left join pg_catalog.pg_namespace n on c.relnamespace = n.oid) u,
    pg_attribute a,
      pg_type t
  WHERE 
    u.relname = '实际的表名'
    AND u.nspname = '实际的schema名称'
      AND a.attnum > 0
      AND a.attrelid = u.oid
      AND a.atttypid = t.oid
  ORDER BY 
    a.attnum;


# 方式:2：
  SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar
      , a.attnotnull AS notnull, b.description AS comment
  FROM pg_class c, pg_attribute a
      LEFT JOIN pg_description b
      ON a.attrelid = b.objoid
          AND a.attnum = b.objsubid, pg_type t
  WHERE c.relname = '实际的表名'
      AND a.attnum > 0
      AND a.attrelid = c.oid
      AND a.atttypid = t.oid
  ORDER BY a.attnum;