Postgres Schema information_schema pg_catalog
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
今天讨论Postgres中两个重要的系统Schema：information_schema与pg_catalog
首先，我们通过查看pg_catalog.pg_namespace来查看当前数据库中全部的Schema
qingping=> select oid,* from pg_catalog.pg_namespace;
oid | nspname | nspowner | nspacl
-------+--------------------+----------+-------------------------
99 | pg_toast | 10 |
11736 | pg_temp_1 | 10 |
11737 | pg_toast_temp_1 | 10 |
11 | pg_catalog | 10 | {root=UC/root,=U/root}
2200 | public | 10 | {root=UC/root,=UC/root}
12921 | information_schema | 10 | {root=UC/root,=U/root}
16386 | qingping | 16384 |
16396 | yaron | 16390 |
(8 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
今天重点分析Postgres数据库中最为常用的两个系统Schema：information_schema和pg_catalog。
其中information_schema是方便用户查看表／视图／函数信息提供的，它大多是视图，MySQL，SQL Server同样有information_schema这个schema。
pg_catalog是系统Schema，包含了系统的自带函数／数据类型定义等，pg_catalog是保障postgres正常运转的重要基石。
-- 查看information_schema提供的视图和表
qingping=> select relname, relkind from pg_catalog.pg_class where relnamespace=12921 order by 1;
relname | relkind
---------------------------------------+---------
_pg_foreign_data_wrappers | v
_pg_foreign_servers | v
_pg_foreign_table_columns | v
_pg_foreign_tables | v
_pg_user_mappings | v
administrable_role_authorizations | v
applicable_roles | v
attributes | v
character_sets | v
check_constraint_routine_usage | v
check_constraints | v
collation_character_set_applicability | v
collations | v
column_domain_usage | v
column_options | v
column_privileges | v
column_udt_usage | v
columns | v
constraint_column_usage | v
constraint_table_usage | v
data_type_privileges | v
domain_constraints | v
domain_udt_usage | v
domains | v
element_types | v
enabled_roles | v
foreign_data_wrapper_options | v
foreign_data_wrappers | v
foreign_server_options | v
foreign_servers | v
foreign_table_options | v
foreign_tables | v
information_schema_catalog_name | v
key_column_usage | v
parameters | v
referential_constraints | v
role_column_grants | v
role_routine_grants | v
role_table_grants | v
role_udt_grants | v
role_usage_grants | v
routine_privileges | v
routines | v
schemata | v
sequences | v
sql_features | r
sql_implementation_info | r
sql_languages | r
sql_packages | r
sql_parts | r
sql_sizing | r
sql_sizing_profiles | r
table_constraints | v
table_privileges | v
tables | v
transforms | v
triggered_update_columns | v
triggers | v
udt_privileges | v
usage_privileges | v
user_defined_types | v
user_mapping_options | v
user_mappings | v
view_column_usage | v
view_routine_usage | v
view_table_usage | v
views | v
(67 rows)
- 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
通过查看information_chema.tables, information_schema.columns可以方便的获取表／字段信息。
qingping=> create table tt(id int, name varchar(100), salary numeric(20,2));
CREATE TABLE
qingping=> insert into tt values(1, 'David', 15000), (2, 'Peter', 25000);
INSERT 0 2
qingping=> select * from information_schema.tables where table_name='tt';
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_def
ined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------
------------------+--------------------------+------------------------+--------------------+----------+---------------
qingping | qingping | tt | BASE TABLE | | |
| | | YES | NO |
(1 row)
qingping=> select table_name, column_name, data_type, numeric_precision, numeric_scale from information_schema.columns where table_name='tt';
table_name | column_name | data_type | numeric_precision | numeric_scale
------------+-------------+-------------------+-------------------+---------------
tt | id | integer | 32 | 0
tt | name | character varying | |
tt | salary | numeric | 20 | 2
(3 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
—-待完成
