PostgreSQL tutorial (16): System View Details, postgresql View

PostgreSQL tutorial (16): System View Details, postgresql View

I. pg_tables:

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

Name	Type	Reference	Description
Schemaname	Name	Pg_namespace.nspname	The schema name of the table.
Tablename	Name	Pg_class.relname	Table name.
Tableowner	Name	Pg_authid.rolname	Name of the table owner.
Tablespace	Name	Pg_tablespace.spcname	Name of the tablespace that contains the table (NULL if it is the default value of the database ).
Hasindexes	Bool	Pg_class.relhasindex	If the table has (or has recently) any index, it is true.
Hasrules	Bool	Pg_class.relhasrules	True if the table has rules.
Hastriggers	Bool	Pg_class.reltriggers	If the table has a trigger, it is true.

Ii. pg_indexes:

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

 

Name	Type	Reference	Description
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 where the index is located.
Indexname	Name	Pg_class.relname	The name of the index.
Tablespace	Name	Pg_tablespace.spcname	Name of the tablespace that contains the index (NULL if it is the default value of the database ).
Indexdef	Text		Index definition (a re-built creation command ).

Iii. pg_views:

This view provides a way to access the useful information of each view in the database.

 

Name	Type	Reference	Description
Schemaname	Name	Pg_namespace.nspname	The schema name that contains this view.
Viewname	Name	Pg_class.relname	View name.
Viewowner	Name	Pg_authid.rolname	The name of the view owner.
Definition	Text		View definition (a re-built SELECT query ).

Iv. pg_user:

This view provides access to database user information. This view is only a public-readable part of the pg_shadow table, but does not contain the password field.

Name	Type	Reference	Description
Usename	Name		User name.
Usesysid	Int4		User ID (any number used to reference this user ).
Usecreatedb	Bool		Whether the user can create a database.
Usesuper	Bool		Whether the user is a Super User.
Usecatupd	Bool		Whether the user can update the system table. (This is not allowed even for Super Users, unless this field is true .)
Passwd	Text		Password (may be encrypted ).
Valuntil	Abstime		Password expiration time (only used for Password Authentication ).
Useconfig	Text []		The default session of parameters configured during runtime.

V. pg_roles:

This view provides an interface for accessing database role information. This view is only a view of the public readable part of the pg_authid table, and the password field is filled with blank space.

 

Name	Type	Reference	Description
Rolname	Name		Role name.
Rolsuper	Bool		Whether the role has the superuser permission.
Rolcreaterole	Bool		Can I create more roles.
Rolcreatedb	Bool		Whether the role of the database can be created.
Rolcatupdate	Bool		Whether the role of the system table can be directly updated.
Rolcanlogin	Bool		If it is true, it indicates a role that can be logged on.
Rolpassword	Text		It is not a password (always ********).
Rolvaliduntil	Timestamptz		Password Expiration date (for password authentication only); if there is no expiration date, it is NULL.
Rolconfig	Text []		Set the default session for variables during runtime.

6. pg_rules:

This view provides an interface for accessing useful information about query rewrite rules.

 

Name	Type	Reference	Description
Schemaname	Name	Pg_namespace.nspname	Name of the schema that contains the table.
Tablename	Name	Pg_class.relname	Name of the table to which the rule applies an impact.
Rulename	Name	Pg_rewrite.rulename	The name of the rule.
Definition	Text		Rule definition (a re-constructed creation command ).

VII. pg_settings:

This view provides access to Server Runtime parameters. It is actually another method of SHOW and SET commands. It also provides access to parameters that cannot be directly obtained using SHOW, such as the maximum and minimum values.

Name	Type	Reference	Description
Name	Text		The parameter name configured during running.
Setting	Text		The current value of the parameter.
Category	Text		The logical Group of the parameter.
Short_desc	Text		A brief description of the parameter.
Extra_desc	Text		Additional and more detailed information about the parameters.
Context	Text		Set the environment required for this parameter value.
Vartype	Text		Parameter type (bool, integer, real, and string ).
Source	Text		Source of the current parameter value.
Min_val	Text		Minimum value allowed by this parameter (non-numeric value is NULL ).
Max_val	Text		The maximum value allowed by this parameter (the non-numeric value is NULL ).

We cannot insert or delete the pg_settings view, but can only update it. Updating a row in pg_settings is equivalent to executing the SET command on the named parameter. This modification affects the value used by the current session. If an UPDATE command is issued in the last exited transaction, the effect of the UPDATE command will disappear after the transaction rollback. Once the transaction is committed, the effect will be fixed until the session ends.