PostgreSQL The Information Schema
I want to learn more about the "information schema": The information schema consists of a set of views that contain information about the objects defined in the current database. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns.
References
Notes
The information schema consists of a set of views that contain information about the objects defined in the current databased. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable - unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns. The information schema views do not, however, contain information about PostgreSQL specific features.
The information schema itself is a schema named information_schema. This schema automatically exists in all databases. The owner of this schema is the initial database user in the cluster, and that user naturally has all the privileges on this schema, including the ability to drop it.
The columns of the information schema view use special data that are defined in the information schema. These are defined as simple domains over ordinary built in types. You should not use these types outside of the information schema, but you should use them if they select from the information schema.
The information_schema_catalog_name is a table that always contains one row and one column containing the name of the current database. The administratable_role_authorization identifies all roles that the current user has the admin option for. The view applicable_roles identifies all roles whose privileges the current user can use. This means there is some chain of role grants from the current user to the role in question.
The view attributes contains information about the attributes of composite data types defines in the database. Only those attributes are shown that the current user has access to. The view check_constraint_routine_usage identifies routines that are used by a check constraint. The view check_constraints contains all check constraints, either defined in a table or a domain, that are owned by a currently enabled role. The view collations contains the collations available in the current database. The view column_column_usage identifies all generated columns that depend on another base column in the same table. Only tables owned by a currently enabled role are included. The view column_options contains all the options defined for foreign table columns in the current database. Only those foreign table columns are shown that the current user has access to. The view column_priveleges identifies all privileges granted on columns to a currently enabled role or by a currently enabled role.
The view columns contains information about all table columns (or view columns) in the database. System columns are not included. Only those columns are shown that the current user has access to. The view constaint_column_usage identifies all columns in the current database that are used by some constraint. The view constraint_table_usage identifies all tables in the current database that are used by some constraint and are owned by a currently enabled role.
Comments
You can read more about how comments are sorted in this blog post.
User Comments
There are currently no comments for this article.