postgresql查询表元数据信息语句

梦里梦外; 2022-12-23 09:40 281阅读 0赞

### 1. 常用postgresql命令 ###

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

1.  进入数据库命令行终端：`psql`
2.  显示数据库：`\l`
3.  连接指定的数据库：`\c 数据库名`
4.  查看当前实例下的表：`\dt`
5.  用sql语句方式查询：`SELECT tablename FROM pg_tables;`
6.  查询某张表的表结构及字段信息：`\d tablename--实际表名`

### 2. 用到的postgresql系统表 ###

#### 2.1 `pg_class` ####

记录了数据库中的表，索引，序列，视图（“关系”）。  
其中比较重要字段有：

1.  `relname` 表，索引，视图等的名字。
2.  `relnamespace` 包含这个关系的名字空间（模式）的 OID，对应`pg_namespace.oid`
3.  `relkind` r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表

#### 2.2 `pg_namespace` ####

记录了数据库的名字空间（模式）  
其中比较重要的字段有：

1.  `nspname` 名字空间的名字
2.  `nspowner` 名字空间的所有者

#### 2.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。

#### 2.4 `pg_type` ####

记录了数据库有关数据类型的信息。  
其中比较重要的字段有：

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

#### 2.5 `pg_description` ####

记录了数据库中对象（表、字段等）的注释。  
其中比较重要的字段有：

1.  `objoid` 这条描述所描述的对象的 OID。如果这条注释是一个表或表中字段的注释，那么，该值对应于`pg_class.oid`
2.  `objsubid` 对于一个表字段的注释，它是字段号，对应于pg\_attribute.attnum。对于其它对象类型，它是零。
3.  `description` 作为对该对象的描述的任意文本

### 3. 查询指定模式下表字段信息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;

### 4. 参考 ###

1.  [https://www.cnblogs.com/hellojesson/p/12109312.html][https_www.cnblogs.com_hellojesson_p_12109312.html]
2.  [https://www.cnblogs.com/jxycn/p/5215822.html][https_www.cnblogs.com_jxycn_p_5215822.html]

[https_www.cnblogs.com_hellojesson_p_12109312.html]: https://www.cnblogs.com/hellojesson/p/12109312.html
[https_www.cnblogs.com_jxycn_p_5215822.html]: https://www.cnblogs.com/jxycn/p/5215822.html