合 PG中的系统表和系统视图（数据字典）
👉 本文共约20590个字，系统预计阅读时间或需78分钟。
目录
系统表
大多数系统表都是在数据库创建的过程中从模版数据库中拷贝过来的，因此都是数据库相关的。少数表是在整个安装中物理上所有数据库共享的；这些表在独立的表的描述中用指明了。
查看数据库系统表命令
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | lhrdb=# \dt pg_* List of relations Schema | Name | Type | Owner ------------+-------------------------+-------+---------- pg_catalog | pg_aggregate | table | postgres pg_catalog | pg_am | table | postgres pg_catalog | pg_amop | table | postgres pg_catalog | pg_amproc | table | postgres pg_catalog | pg_attrdef | table | postgres pg_catalog | pg_attribute | table | postgres pg_catalog | pg_auth_members | table | postgres pg_catalog | pg_authid | table | postgres pg_catalog | pg_cast | table | postgres pg_catalog | pg_class | table | postgres pg_catalog | pg_collation | table | postgres pg_catalog | pg_constraint | table | postgres pg_catalog | pg_conversion | table | postgres pg_catalog | pg_database | table | postgres pg_catalog | pg_db_role_setting | table | postgres pg_catalog | pg_default_acl | table | postgres pg_catalog | pg_depend | table | postgres pg_catalog | pg_description | table | postgres pg_catalog | pg_enum | table | postgres pg_catalog | pg_event_trigger | table | postgres pg_catalog | pg_extension | table | postgres pg_catalog | pg_foreign_data_wrapper | table | postgres pg_catalog | pg_foreign_server | table | postgres pg_catalog | pg_foreign_table | table | postgres pg_catalog | pg_index | table | postgres pg_catalog | pg_inherits | table | postgres pg_catalog | pg_init_privs | table | postgres pg_catalog | pg_language | table | postgres pg_catalog | pg_largeobject | table | postgres pg_catalog | pg_largeobject_metadata | table | postgres pg_catalog | pg_namespace | table | postgres pg_catalog | pg_opclass | table | postgres pg_catalog | pg_operator | table | postgres pg_catalog | pg_opfamily | table | postgres pg_catalog | pg_partitioned_table | table | postgres pg_catalog | pg_policy | table | postgres pg_catalog | pg_proc | table | postgres pg_catalog | pg_publication | table | postgres pg_catalog | pg_publication_rel | table | postgres pg_catalog | pg_range | table | postgres pg_catalog | pg_replication_origin | table | postgres pg_catalog | pg_rewrite | table | postgres pg_catalog | pg_seclabel | table | postgres pg_catalog | pg_sequence | table | postgres pg_catalog | pg_shdepend | table | postgres pg_catalog | pg_shdescription | table | postgres pg_catalog | pg_shseclabel | table | postgres pg_catalog | pg_statistic | table | postgres pg_catalog | pg_statistic_ext | table | postgres pg_catalog | pg_statistic_ext_data | table | postgres pg_catalog | pg_subscription | table | postgres pg_catalog | pg_subscription_rel | table | postgres pg_catalog | pg_tablespace | table | postgres pg_catalog | pg_transform | table | postgres pg_catalog | pg_trigger | table | postgres pg_catalog | pg_ts_config | table | postgres pg_catalog | pg_ts_config_map | table | postgres pg_catalog | pg_ts_dict | table | postgres pg_catalog | pg_ts_parser | table | postgres pg_catalog | pg_ts_template | table | postgres pg_catalog | pg_type | table | postgres pg_catalog | pg_user_mapping | table | postgres (62 rows) |
表名字 用途
- pg_aggregate 聚集函数
- pg_am 索引访问方法
- pg_amop 访问方法操作符
- pg_amproc 访问方法支持过程
- pg_attrdef 字段缺省值
- pg_attribute 表的列(也称为”属性”或”字段”)
- pg_authid 认证标识符(角色)
- pg_auth_members 认证标识符成员关系
- pg_autovacuum 每个关系一个的自动清理配置参数
- pg_cast 转换(数据类型转换)
- pg_class 表、索引、序列、视图(“关系”)
- pg_constraint 检查约束、唯一约束、主键约束、外键约束
- pg_conversion 编码转换信息
- pg_database 本集群内的数据库
- pg_depend 数据库对象之间的依赖性
- pg_description 数据库对象的描述或注释
- pg_index 附加的索引信息
- pg_inherits 表继承层次
- pg_language 用于写函数的语言
- pg_largeobject 大对象
- pg_listener 异步通知
- pg_namespace 模式
- pg_opclass 索引访问方法操作符类
- pg_operator 操作符
- pg_pltemplate 过程语言使用的模板数据
- pg_proc 函数和过程
- pg_rewrite 查询重写规则
- pg_shdepend 在共享对象上的依赖性
- pg_shdescription 共享对象上的注释
- pg_statistic 优化器统计
- pg_tablespace 这个数据库集群里面的表空间
- pg_trigger 触发器
- pg_type 数据类型
pg_class
查询所有业务表信息
1 | select * from pg_tables where tablename not like 'pg%' and tablename not like 'sql_%' order by tablename; |
查询所有业务表名称及表描述
1 2 | select tablename,obj_description(relfilenode,'pg_class') from pg_tables a, pg_class b where a.tablename = b.relname and a.tablename not like 'pg%' and a.tablename not like 'sql_%' order by a.tablename; |
查询某表的字段信息
1 2 3 | select a.attname as fieldname, col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type, a.attnotnull as notnull from pg_class as c,pg_attribute as a where c.relname = '表名称' and a.attrelid = c.oid and a.attnum > 0; |
pg_class （系统表：对象）是一个对象表，表的每个字段都是‘rel’开头，分明就是 ‘relation’这个单词的缩写，意思就是‘关系’。表中relkind字段决定对象类型：r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表。对象所属的relnamespace（模式名称）和relowner（所有者）都是用其对应的oid显示，所以要直观看到实际本名要联合pg_namespace（系统表：模式）和pg_roles（系统视图：角色）一起查，这两个表和视图中都有oid字段。
该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。
| 名称 | 类型 | 参考 | 描述 |
|---|---|---|---|
| oid | oid | 行标识符（隐藏属性;必须明确选择） | |
| relname | name | 表格，索引，视图等的名称 | |
| relnamespace | oid | pg_namespace.oid | 包含此relation的名称空间的oid |
| reltype | oid | pg_type .oid | 与此表行类型对应的数据类型的oid（如果有的话）（对于没有pg_type条目的索引，为零 ） |
| reloftype | oid | pg_type .oid | 对于类型表，基础复合类型的oid，对于所有其他relation为零 |
| relowner | oid | pg_authid.oid | relation的所有者 |
| relam | oid | pg_am.oid | 如果这是一个索引，则使用的访问方法（B-树，散列等） |
| relfilenode | oid | 该relation的磁盘文件的名称; 零表示这是一个“映射”relation，其磁盘文件名由低级状态决定 | |
| reltablespace | oid | pg_tablespace.oid | 存储该relation的表空间。如果为零，则隐含数据库的默认表空间。（如果relation没有磁盘上的文件，则无意义。） |
| relpages | int4 | 该表的磁盘表示的大小（页面大小为BLCKSZ）。这只是计划者使用的估计值。它由 VACUUM，ANALYZE和一些DDL命令（如 CREATE INDEX）更新。 | |
| reltuples | float4 | 表中的行数。这只是计划者使用的估计值。它由VACUUM，ANALYZE和一些DDL命令（如CREATE INDEX）更新。 | |
| relallvisible | int4 | 在表格的可见性图中标记为全部可见的页面数。这只是计划者使用的估计值。它由VACUUM，ANALYZE和一些DDL命令（如CREATE INDEX）更新。 | |
| reltoastrelid | oid | pg_class .oid | 与此表关联的TOAST表的oid，如果没有，则为0。TOAST表在“辅助表”中存储“超出行”的大型属性 。 |
| relhasindex | bool | 如果这是一个表并且它有（或最近有）任何索引，则为真 | |
| relisshared | bool | 如果此表在群集中的所有数据库之间共享，则为true。只有某些系统目录（如 pg_database）被共享。 | |
| relpersistence | char | p =永久表， u =未记录表，t =临时表 | |
| relkind | char | r = 普通表， i = 索引， S = 序列， t = TOAST表， v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表， I = 分区索引 | |
| relnatts | int2 | relation中的用户列数（系统列未计数）。pg_attribute中必须有许多相应的条目。另见pg_attribute.attnum。 | |
| relchecks | int2 | 表上CHECK约束的数量; 请参阅pg_constraint目录 | |
| relhasoids | bool | 如果我们为relation的每一行生成oid，则为真 | |
| relhaspkey | bool | 如果表具有（或曾经有）主键，则为真 | |
| relhasrules | bool | 如果表具有（或曾经有）规则，则为真; 请参阅pg_rewrite目录 | |
| relhastriggers | bool | 如果表具有（或曾经有）触发器，则为真; 请参阅 pg_trigger目录 | |
| relhassubclass | bool | 如果表有（或曾经有过）任何继承孩子，则为真 | |
| relrowsecurity | bool | 如果表已启用行级安全性，则为true; 请参阅 pg_policy目录 | |
| relforcerowsecurity | bool | 如果行级别安全性（启用时）也为true，则也适用于表所有者; 请参阅pg_policy目录 | |
| relispopulated | bool | 如果relation被填充，则为真（对于除某些实例化视图之外的所有relation都是如此） | |
| relreplident | char | 用于为行构成“副本标识”的列：d = default（主键，如果有的话），n =无，f =所有列 i =具有indisreplident set的索引或default | |
| relfrozenxid | xid | 在此表之前的所有交易ID已被替换为永久（“冻结”）交易ID。这用于跟踪是否需要将表抽真空以防止事务ID环绕或允许缩小pg_clog。零（InvalidTransactionId）如果relation不是一个表。 | |
| relminmxid | xid | 在此表之前的所有多重作业ID已由该事务ID替换。这用于跟踪是否需要将表抽真空以防止多轴实现ID 绕回或允许缩小pg_multixact。零（InvalidMultiXactId）如果relation不是一个表。 | |
| relacl | aclitem[] | 访问权限; 看到GRANT和REVOKE的细节 | |
| reloptions | text[] | 特定于访问方法的选项，如“keyword = value”字符串 |
1 2 3 4 5 6 7 8 9 10 11 12 | #查看指定表对象testtable的模式 postgres=# SELECT relname,relnamespace,nspname FROM pg_class c,pg_namespace n WHERE relname = 'testtable' AND relnamespace = n.oid; relname | relnamespace | nspname -------------+--------------+--------- testtable | 2200 | public (1 row) #查看指定表对象testtable的owner(即role)。 postgres=# select relname,rolname from pg_class c,pg_authid au where relname = 'testtable' and relowner = au.oid; relname | rolname -------------+---------- testtable | postgres (1 row) |
pg_attribute
该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。
另外，查询列信息也可以使用information_schema.columns视图。
| 名字 | 类型 | 引用 | 描述 |
|---|---|---|---|
| attrelid | oid | pg_class.oid | 此字段所属的表。 |
| attname | name | 字段名。 | |
| atttypid | oid | pg_type.oid | 字段的数据类型。 |
| attstattarget | int4 | attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。 | |
| attlen | int2 | 该字段所属类型的长度。(pg_type.typlen的拷贝) | |
| attnum | int2 | 字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。 | |
| attndims | int4 | 如果该字段是数组，该值表示数组的维数，否则是0。 | |
| attcacheoff | int4 | 在磁盘上总是-1，但是如果装载入内存中的行描述器中， 它可能会被更新为缓冲在行中字段的偏移量。 | |
| atttypmod | int4 | 表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。 | |
| attbyval | bool | pg_type.typbyval字段值的拷贝。 | |
| attstorage | char | pg_type.typstorage字段值的拷贝。 | |
| attalign | char | pg_type.typalign字段值的拷贝。 | |
| attnotnull | bool | 如果该字段带有非空约束，则为真，否则为假。 | |
| atthasdef | bool | 该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。 | |
| attisdropped | bool | 该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过SQL访问。 | |
| attislocal | bool | 该字段是否局部定义在对象中的。 | |
| attinhcount | int4 | 该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。 |
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | #查看指定表中包含的字段名和字段编号。 postgres=# SELECT relname, attname,attnum FROM pg_class c,pg_attribute attr WHERE relname = 'testtable' AND c.oid = attr.attrelid; relname | attname | attnum ------------+----------+-------- testtable | tableoid | -7 testtable | cmax | -6 testtable | xmax | -5 testtable | cmin | -4 testtable | xmin | -3 testtable | ctid | -1 testtable | i | 1 (7 rows) #只查看用户自定义字段的类型 postgres=# SELECT relname,attname,typname FROM pg_class c,pg_attribute a,pg_type t WHERE c.relname = 'testtable' AND c.oid = attrelid AND atttypid = t.oid AND attnum > 0; relname | attname | typname ----------+---------+--------- testtable| i | int4 (7 rows) postgres=# \d information_schema.columns; View "information_schema.columns" Column | Type | Collation | Nullable | Default --------------------------+------------------------------------+-----------+----------+--------- table_catalog | information_schema.sql_identifier | | | table_schema | information_schema.sql_identifier | | | table_name | information_schema.sql_identifier | | | column_name | information_schema.sql_identifier | | | ordinal_position | information_schema.cardinal_number | | | column_default | information_schema.character_data | | | is_nullable | information_schema.yes_or_no | | | data_type | information_schema.character_data | | | character_maximum_length | information_schema.cardinal_number | | | character_octet_length | information_schema.cardinal_number | | | numeric_precision | information_schema.cardinal_number | | | numeric_precision_radix | information_schema.cardinal_number | | | numeric_scale | information_schema.cardinal_number | | | datetime_precision | information_schema.cardinal_number | | | interval_type | information_schema.character_data | | | interval_precision | information_schema.cardinal_number | | | character_set_catalog | information_schema.sql_identifier | | | character_set_schema | information_schema.sql_identifier | | | character_set_name | information_schema.sql_identifier | | | collation_catalog | information_schema.sql_identifier | | | collation_schema | information_schema.sql_identifier | | | collation_name | information_schema.sql_identifier | | | domain_catalog | information_schema.sql_identifier | | | domain_schema | information_schema.sql_identifier | | | domain_name | information_schema.sql_identifier | | | udt_catalog | information_schema.sql_identifier | | | udt_schema | information_schema.sql_identifier | | | udt_name | information_schema.sql_identifier | | | scope_catalog | information_schema.sql_identifier | | | scope_schema | information_schema.sql_identifier | | | scope_name | information_schema.sql_identifier | | | maximum_cardinality | information_schema.cardinal_number | | | dtd_identifier | information_schema.sql_identifier | | | is_self_referencing | information_schema.yes_or_no | | | is_identity | information_schema.yes_or_no | | | identity_generation | information_schema.character_data | | | identity_start | information_schema.character_data | | | identity_increment | information_schema.character_data | | | identity_maximum | information_schema.character_data | | | identity_minimum | information_schema.character_data | | | identity_cycle | information_schema.yes_or_no | | | is_generated | information_schema.character_data | | | generation_expression | information_schema.character_data | | | is_updatable | information_schema.yes_or_no | | | postgres=# select * from information_schema.columns where table_name='test'; table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable ---------------+--------------+------------+--------------+------------------+----------------+-------------+--------------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+-------------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+-------------- postgres | public | test | usename | 1 | | YES | name | | | | | | | | | | | | postgres | pg_catalog | C | | | | postgres | pg_catalog | name | | | | | 1 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | usesysid | 2 | | YES | oid | | | | | | | | | | | | | | | | | | postgres | pg_catalog | oid | | | | | 2 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | usecreatedb | 3 | | YES | boolean | | | | | | | | | | | | | | | | | | postgres | pg_catalog | bool | | | | | 3 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | usesuper | 4 | | YES | boolean | | | | | | | | | | | | | | | | | | postgres | pg_catalog | bool | | | | | 4 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | userepl | 5 | | YES | boolean | | | | | | | | | | | | | | | | | | postgres | pg_catalog | bool | | | | | 5 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | usebypassrls | 6 | | YES | boolean | | | | | | | | | | | | | | | | | | postgres | pg_catalog | bool | | | | | 6 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | passwd | 7 | | YES | text | | 1073741824 | | | | | | | | | | | | | | | | postgres | pg_catalog | text | | | | | 7 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | valuntil | 8 | | YES | timestamp with time zone | | | | | | 6 | | | | | | | | | | | | postgres | pg_catalog | timestamptz | | | | | 8 | NO | NO | | | | | | NO | NEVER | | YES postgres | public | test | useconfig | 9 | | YES | ARRAY | | | | | | | | | | | | postgres | pg_catalog | C | | | | postgres | pg_catalog | _text | | | | | 9 | NO | NO | | | | | | NO | NEVER | | YES (9 rows) |
pg_attrdef
该系统表主要存储字段缺省值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。
| 名字 | 类型 | 引用 | 描述 |
|---|---|---|---|
| adrelid | oid | pg_class.oid | 这个字段所属的表 |
| adnum | int2 | pg_attribute.attnum | 字段编号，其规则等同于pg_attribute.attnum |
| adbin | text | 字段缺省值的内部表现形式。 | |
| adsrc | text | 缺省值的人可读的表现形式。 |
1 2 3 4 5 6 7 8 | #查看指定表有哪些字段存在缺省值，同时显示出字段名和缺省值的定义方式 postgres=# CREATE TABLE testtable2 (i integer DEFAULT 100); CREATE TABLE postgres=# SELECT c.relname, a.attname, ad.adnum, ad.adsrc FROM pg_class c, pg_attribute a, pg_attrdef ad WHERE relname = 'testtable2' AND ad.adrelid = c.oid AND adnum = a.attnum AND attrelid = c.oid; relname | attname | adnum | adsrc -------------+----------+---------+------- testtable2 | i | 1 | 100 (1 row) |
pg_authid
该系统表存储有关数据库认证的角色信息，在PostgreSQL中角色可以表现为用户和组两种形式。对于用户而言只是设置了rolcanlogin标志的角色。由于该表包含口令数据，所以它不是公共可读的。PostgreSQL中提供了另外一个建立在该表之上的系统视图pg_roles，该视图将口令字段填成空白。
| 名字 | 类型 | 引用 | 描述 |
|---|---|---|---|
| rolname | name | 角色名称。 | |
| rolsuper | bool | 角色是否拥有超级用户权限。 | |
| rolcreaterole | bool | 角色是否可以创建其它角色。 | |
| rolcreatedb | bool | 角色是否可以创建数据库。 | |
| rolcatupdate | bool | 角色是否可以直接更新系统表(如果该设置为假，即使超级用户也不能更新系统表)。 | |
| rolcanlogin | bool | 角色是否可以登录，换句话说，这个角色是否可以给予会话认证标识符。 | |
| rolpassword | text | 口令(可能是加密的)；如果没有则为NULL。 | |
| rolvaliduntil | timestamptz | 口令失效时间(只用于口令认证)；如果没有失效期，则为NULL。 | |
| rolconfig | text[] | 运行时配置变量的会话缺省。 |
1 2 3 4 5 | #从输出结果可以看出口令字段已经被加密。 postgres=# SELECT rolname,rolpassword FROM pg_authid; rolname | rolpassword -----------+------------------------------------- postgres | md5a3556571e93b0d20722ba62be61e8c2d |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
