PostgreSQL Tutorial (16): Detailed system View _postgresql

First, Pg_tables:

This view provides access to useful information about each table in the database.

Name	Type	Reference	Describe
SchemaName	Name	Pg_namespace.nspname	The schema name that contains the table.
TableName	Name	Pg_class.relname	The name of the table.
Tableowner	Name	Pg_authid.rolname	The name of the owner of the table.
Tablespace	Name	Pg_tablespace.spcname	The name of the table space that contains the table (NULL if the database defaults).
Hasindexes	bool	Pg_class.relhasindex	True if the table has (or recently owned) any indexes.
Hasrules	bool	Pg_class.relhasrules	True if the table has a rule.
Hastriggers	bool	Pg_class.reltriggers	True if the table has a trigger.

Second, pg_indexes:

This view provides access to useful information for each index in the database.

Name	Type	Reference	Describe
SchemaName	Name	Pg_namespace.nspname	The name of the schema that contains the table and index.
TableName	Name	Pg_class.relname	The name of the table on which the index is located.
IndexName	Name	Pg_class.relname	The name of the index.
Tablespace	Name	Pg_tablespace.spcname	The name of the table space that contains the index (NULL if the database defaults).
IndexDef	Text		The index definition (a rebuild creation command).

Third, Pg_views:

This view provides access to useful information about each view in the database.

Name	Type	Reference	Describe
SchemaName	Name	Pg_namespace.nspname	Contains the schema name for this view.
ViewName	Name	Pg_class.relname	The name of the view.
Viewowner	Name	Pg_authid.rolname	The name of the owner of the view.
Definition	Text		The view definition (a rebuilt select query).

Four, Pg_user:

This view provides access to information about database users. This view is just a view of the public readable part of the Pg_shadow table, but does not contain a password field.

Name	Type	Reference	Describe
Usename	Name		User name.
Usesysid	Int4		User ID (used to refer to any number of this user).
Usecreatedb	bool		Whether the user can create a database.
Usesuper	bool		Whether the user is a superuser.
Usecatupd	bool		Whether the user can update the system tables. (Even Superuser cannot do this unless the word is Ke Weizhen.) )
passwd	Text		Password (may be encrypted).
Valuntil	Abstime		The time that the password was invalidated (for password authentication only).
Useconfig	Text[]		Session defaults for configuration parameters at run time.

Five, Pg_roles:

This view provides an interface to access information about the database role. This view is just a view of the publicly readable part of the Pg_authid table and fills the password field with blank space.

Name	Type	Reference	Describe
Rolname	Name		The role name.
Rolsuper	bool		Whether there is a role for super user privileges.
Rolcreaterole	bool		Whether you can create roles for more roles.
Rolcreatedb	bool		Whether you can create a role for the database.
Rolcatupdate	bool		Whether you can update the role of the system table directly.
Rolcanlogin	bool		If true, represents a role that can be logged in.
Rolpassword	Text		Not a password (always ********).
Rolvaliduntil	Timestamptz		Password expiration date (for password authentication only), or null if there is no expiration period.
Rolconfig	Text[]		Session defaults for Run-time configuration variables.

Vi. Pg_rules:

This view provides an interface for useful information access to query rewrite rules.

Name	Type	Reference	Describe
SchemaName	Name	Pg_namespace.nspname	The name of the schema that contains the table.
TableName	Name	Pg_class.relname	The name of the table to which the rule exerts influence.
RuleName	Name	Pg_rewrite.rulename	The name of the rule.
Definition	Text		The rule definition (a reconstructed creation command).

Seven, Pg_settings:

This view provides access to server Run-time parameters. It's actually a different way of show and set commands. It also provides access to parameters that are not directly available with show, such as the maximum and minimum values.

Name	Type	Reference	Describe
Name	Text		The runtime configures parameter names.
Setting	Text		The current value of the parameter.
Category	Text		A logical group of parameters.
Short_desc	Text		A short description of the parameter.
Extra_desc	Text		Additional, more detailed information about the parameters.
Context	Text		Sets the environment for which the value of this parameter requires.
VarType	Text		parameter types (bool, integer, real, and string).
Source	Text		The source of the current parameter value.
Min_val	Text		The minimum value allowed for this parameter (non-numeric value is null).
Max_val	Text		The maximum allowable value for this parameter (non-numeric value is null).

We cannot insert or delete the Pg_settings view, only update. Making an update to a row in pg_settings is equivalent to executing the SET command on that named parameter. This modification value affects the values used by the current session. If the update command is issued in a last-exited transaction, the effect of the update command disappears after the transaction is rolled back. Once the transaction that surrounds it commits, the effect will solidify until the session ends.