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.