postgresql查询表元数据信息语句_pg元数据表

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

4. 参考

1. https://www.cnblogs.com/hellojesson/p/12109312.html
2. https://www.cnblogs.com/jxycn/p/5215822.html