PG的两个重要模式pg_catalog 和 information_schema

郑全

1.pg_catalog
   系统表的schema
   PG 16有 65个，具体可以参见官网 ： https://www.postgresql.org/docs/current/catalogs.html
2.information_schema
   系统表的替代视图
   PG 16 有66个，具体可以参见官方网站：https://www.postgresql.org/docs/16/information-schema.html


   
DBA的大部分的查询，就主要用这两个了。有疑问，可以直接上官网查询。

郑全

郑全 发表于 2023-12-25 13:29

Catalog  Name	Purpose
pg_aggregate	aggregate  functions
pg_am	relation  access methods
pg_amop	access  method operators
pg_amproc	access  method support functions
pg_attrdef	column  default values
pg_attribute	table  columns (“attributes”)
pg_authid	authorization  identifiers (roles)
pg_auth_members	authorization  identifier membership relationships
pg_cast	casts  (data type conversions)
pg_class	tables,  indexes, sequences, views (“relations”)
pg_collation	collations  (locale information)
pg_constraint	check  constraints, unique constraints, primary key constraints, foreign key  constraints
pg_conversion	encoding  conversion information
pg_database	databases  within this database cluster
pg_db_role_setting	per-role  and per-database settings
pg_default_acl	default  privileges for object types
pg_depend	dependencies  between database objects
pg_description	descriptions  or comments on database objects
pg_enum	enum  label and value definitions
pg_event_trigger	event  triggers
pg_extension	installed  extensions
pg_foreign_data_wrapper	foreign-data  wrapper definitions
pg_foreign_server	foreign  server definitions
pg_foreign_table	additional  foreign table information
pg_index	additional  index information
pg_inherits	table  inheritance hierarchy
pg_init_privs	object  initial privileges
pg_language	languages  for writing functions
pg_largeobject	data  pages for large objects
pg_largeobject_metadata	metadata  for large objects
pg_namespace	schemas
pg_opclass	access  method operator classes
pg_operator	operators
pg_opfamily	access  method operator families
pg_parameter_acl	configuration  parameters for which privileges have been granted
pg_partitioned_table	information  about partition key of tables
pg_policy	row-security  policies
pg_proc	functions  and procedures
pg_publication	publications  for logical replication
pg_publication_namespace	schema  to publication mapping
pg_publication_rel	relation  to publication mapping
pg_range	information  about range types
pg_replication_origin	registered  replication origins
pg_rewrite	query  rewrite rules
pg_seclabel	security  labels on database objects
pg_sequence	information  about sequences
pg_shdepend	dependencies  on shared objects
pg_shdescription	comments  on shared objects
pg_shseclabel	security  labels on shared database objects
pg_statistic	planner  statistics
pg_statistic_ext	extended  planner statistics (definition)
pg_statistic_ext_data	extended  planner statistics (built statistics)
pg_subscription	logical  replication subscriptions
pg_subscription_rel	relation  state for subscriptions
pg_tablespace	tablespaces  within this database cluster
pg_transform	transforms  (data type to procedural language conversions)
pg_trigger	triggers
pg_ts_config	text  search configurations
pg_ts_config_map	text  search configurations' token mappings
pg_ts_dict	text  search dictionaries
pg_ts_parser	text  search parsers
pg_ts_template	text  search templates
pg_type	data  types
pg_user_mapping	mappings  of users to foreign servers

郑全

郑全 发表于 2023-12-25 12:06
Catalog Name        Purpose
pg_aggregate        aggregate functions
pg_am        relation access methods

The Information Schema

Table  of Contents

37.1.  The Schema

37.2. Data Types

37.3. information_schema_catalog_name

37.4. administrable_role_​authorizations

37.5. applicable_roles

37.6. attributes

37.7. character_sets

37.8. check_constraint_routine_usage

37.9. check_constraints

37.10. collations

37.11. collation_character_set_​applicability

37.12. column_column_usage

37.13. column_domain_usage

37.14. column_options

37.15. column_privileges

37.16. column_udt_usage

37.17. columns

37.18. constraint_column_usage

37.19. constraint_table_usage

37.20. data_type_privileges

37.21. domain_constraints

37.22. domain_udt_usage

37.23. domains

37.24. element_types

37.25. enabled_roles

37.26. foreign_data_wrapper_options

37.27. foreign_data_wrappers

37.28. foreign_server_options

37.29. foreign_servers

37.30. foreign_table_options

37.31. foreign_tables

37.32. key_column_usage

37.33. parameters

37.34. referential_constraints

37.35. role_column_grants

37.36. role_routine_grants

37.37. role_table_grants

37.38. role_udt_grants

37.39. role_usage_grants

37.40. routine_column_usage

37.41. routine_privileges

37.42. routine_routine_usage

37.43. routine_sequence_usage

37.44. routine_table_usage

37.45. routines

37.46. schemata

37.47. sequences

37.48. sql_features

37.49. sql_implementation_info

37.50. sql_parts

37.51. sql_sizing

37.52. table_constraints

37.53. table_privileges

37.54. tables

37.55. transforms

37.56. triggered_update_columns

37.57. triggers

37.58. udt_privileges

37.59. usage_privileges

37.60. user_defined_types

37.61. user_mapping_options

37.62. user_mappings

37.63. view_column_usage

37.64. view_routine_usage

37.65. view_table_usage

37.66. views