1 of 64

37. 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. The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

在資料庫中查詢限制條件資訊時，符合標準的查詢可能預期會回傳一筆資料，到數筆資料。這是因為 SQL 標準要求限制條件名稱在綱要中必須是唯一的，但是 PostgreSQL 不強制執行此限制條件。PostgreSQL 自動產生的限制條件名稱會避免在同一綱要中的重複，但是使用者可以指定重複的名稱。

查詢 information schema 檢視表（如check_constraint_routine_usage，check_constraints，domain_constraints 和 referential_constraints）時，可能會出現此問題。其他一些檢視表也有類似的問題，但是包含資料表名稱用以協助區分重複的資料。例如，constraint_column_usage，constraint_table_usage，table_constraints。

37.1. The Schema

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 (but the space savings achieved by that are minuscule).

By default, the information schema is not in the schema search path, so you need to access all objects in it through qualified names. Since the names of some of the objects in the information schema are generic names that might occur in user applications, you should be careful if you want to put the information schema in the path.

37.2. Data Types

The columns of the information schema views use special data types that are defined in the information schema. These are defined as simple domains over ordinary built-in types. You should not use these types for work outside the information schema, but your applications must be prepared for them if they select from the information schema.

These types are:

cardinal_number

A nonnegative integer.

character_data

A character string (without specific maximum length).

sql_identifier

A character string. This type is used for SQL identifiers, the type character_data is used for any other kind of text data.

time_stamp

A domain over the type timestamp with time zone

yes_or_no

A character string domain that contains either YES or NO. This is used to represent Boolean (true/false) data in the information schema. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)

Every column in the information schema has one of these five types.

37.3. information_schema_catalog_name

information_schema_catalog_name is a table that always contains one row and one column containing the name of the current database (current catalog, in SQL terminology).

Table 37.1. `information_schema_catalog_name` Columns

37.4. administrable_role_authorizations

The view administrable_role_authorizations identifies all roles that the current user has the admin option for.

Table 37.2. administrable_role_authorizations Columns

37.5. applicable_roles

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 current user itself is also an applicable role. The set of applicable roles is generally used for permission checking.

Table 37.3. `applicable_roles` Columns

37.7. attributes

The view attributes contains information about the attributes of composite data types defined in the database. (Note that the view does not give information about table columns, which are sometimes called attributes in PostgreSQL contexts.) Only those attributes are shown that the current user has access to (by way of being the owner of or having some privilege on the type).

Table 37.4. `attributes` Columns

37.7. character_sets

The view character_sets identifies the character sets available in the current database. Since PostgreSQL does not support multiple character sets within one database, this view only shows one, which is the database encoding.

Take note of how the following terms are used in the SQL standard:

character repertoire

An abstract collection of characters, for example UNICODE, UCS, or LATIN1. Not exposed as an SQL object, but visible in this view.

character encoding form

An encoding of some character repertoire. Most older character repertoires only use one encoding form, and so there are no separate names for them (e.g., LATIN1 is an encoding form applicable to the LATIN1 repertoire). But for example Unicode has the encoding forms UTF8, UTF16, etc. (not all supported by PostgreSQL). Encoding forms are not exposed as an SQL object, but are visible in this view.

character set

A named SQL object that identifies a character repertoire, a character encoding, and a default collation. A predefined character set would typically have the same name as an encoding form, but users could define other names. For example, the character set UTF8 would typically identify the character repertoire UCS, encoding form UTF8, and some default collation.

You can think of an “encoding” in PostgreSQL either as a character set or a character encoding form. They will have the same name, and there can only be one in one database.

Table 37.5. `character_sets` Columns

37.8. check_constraint_routine_usage

The view check_constraint_routine_usage identifies routines (functions and procedures) that are used by a check constraint. Only those routines are shown that are owned by a currently enabled role.

Table 37.6. `check_constraint_routine_usage` Columns

37.9. check_constraints

The view check_constraints contains all check constraints, either defined on a table or on a domain, that are owned by a currently enabled role. (The owner of the table or domain is the owner of the constraint.)

Table 37.7. `check_constraints` Columns

37.10. collations

The view collations contains the collations available in the current database.

Table 37.8. `collations` Columns

37.11. collation_character_set_applicability

The view collation_character_set_applicability identifies which character set the available collations are applicable to. In PostgreSQL, there is only one character set per database (see explanation in ), so this view does not provide much useful information.

Table 37.9. `collation_character_set_applicability` Columns

37.12. column_column_usage

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.

Table 37.10. `column_column_usage` Columns

37.13. column_domain_usage

The view column_domain_usage identifies all columns (of a table or a view) that make use of some domain defined in the current database and owned by a currently enabled role.

Table 37.11. `column_domain_usage` Columns

37.14. column_options

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 (by way of being the owner or having some privilege).

Table 37.12. `column_options` Columns

37.15. column_privileges

The view column_privileges identifies all privileges granted on columns to a currently enabled role or by a currently enabled role. There is one row for each combination of column, grantor, and grantee.

If a privilege has been granted on an entire table, it will show up in this view as a grant for each column, but only for the privilege types where column granularity is possible: SELECT, INSERT, UPDATE, REFERENCES.

Table 37.13. `column_privileges` Columns

37.16. column_udt_usage

The view column_udt_usage identifies all columns that use data types owned by a currently enabled role. Note that in PostgreSQL, built-in data types behave like user-defined types, so they are included here as well. See also Section 37.17 for details.

Table 37.14. `column_udt_usage` Columns

37.17. columns

The view columns contains information about all table columns (or view columns) in the database. System columns (ctid, etc.) are not included. Only those columns are shown that the current user has access to (by way of being the owner or having some privilege).

Table 37.15. `columns` Columns

Since data types can be defined in a variety of ways in SQL, and PostgreSQL contains additional ways to define data types, their representation in the information schema can be somewhat difficult. The column data_type is supposed to identify the underlying built-in type of the column. In PostgreSQL, this means that the type is defined in the system catalog schema pg_catalog. This column might be useful if the application can handle the well-known built-in types specially (for example, format the numeric types differently or use the data in the precision columns). The columns udt_name, udt_schema, and udt_catalog always identify the underlying data type of the column, even if the column is based on a domain. (Since PostgreSQL treats built-in types like user-defined types, built-in types appear here as well. This is an extension of the SQL standard.) These columns should be used if an application wants to process data differently according to the type, because in that case it wouldn't matter if the column is really based on a domain. If the column is based on a domain, the identity of the domain is stored in the columns domain_name, domain_schema, and domain_catalog. If you want to pair up columns with their associated data types and treat domains as separate types, you could write coalesce(domain_name, udt_name), etc.

37.18. constraint_column_usage

The view constraint_column_usage identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contained in a table owned by a currently enabled role. For a check constraint, this view identifies the columns that are used in the check expression. For a foreign key constraint, this view identifies the columns that the foreign key references. For a unique or primary key constraint, this view identifies the constrained columns.

Table 37.16. `constraint_column_usage` Columns

37.19. constraint_table_usage

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. (This is different from the view table_constraints, which identifies all table constraints along with the table they are defined on.) For a foreign key constraint, this view identifies the table that the foreign key references. For a unique or primary key constraint, this view simply identifies the table the constraint belongs to. Check constraints and not-null constraints are not included in this view.

Table 37.17. `constraint_table_usage` Columns

37.20. data_type_privileges

The view data_type_privileges identifies all data type descriptors that the current user has access to, by way of being the owner of the described object or having some privilege for it. A data type descriptor is generated whenever a data type is used in the definition of a table column, a domain, or a function (as parameter or return type) and stores some information about how the data type is used in that instance (for example, the declared maximum length, if applicable). Each data type descriptor is assigned an arbitrary identifier that is unique among the data type descriptor identifiers assigned for one object (table, domain, function). This view is probably not useful for applications, but it is used to define some other views in the information schema.

Table 37.18. `data_type_privileges` Columns

37.21. domain_constraints

The view domain_constraints contains all constraints belonging to domains defined in the current database. Only those domains are shown that the current user has access to (by way of being the owner or having some privilege).

Table 37.19. domain_constraints Columns

37.21. domain_udt_usage

37.22. domains

37.23. element_types

37.24. enabled_roles

37.25. foreign_data_wrapper_options

37.26. foreign_data_wrappers

37.27. foreign_server_options

37.28. foreign_servers

37.29. foreign_table_options

37.30. foreign_tables

36.32. key_column_usage

檢視表 key_column_usage 列出目前資料庫中受到某些唯一、主鍵或外部鍵限制的所有欄位。此檢視表中不包括 check constraints。僅顯示目前使用者可以透過成為擁有者或具有某些權限存取的那些欄位。

Table 36.30. `key_column_usage` Columns

Name

Data Type

Description

37.33. parameters

The view parameters contains information about the parameters (arguments) of all functions in the current database. Only those functions are shown that the current user has access to (by way of being the owner or having some privilege).

Table 37.31. `parameters` Columns

36.34. referential_constraints

The view referential_constraints contains all referential (foreign key) constraints in the current database. Only those constraints are shown for which the current user has write access to the referencing table (by way of being the owner or having some privilege other than SELECT).

Table 36.32. `referential_constraints` Columns

Name

Data Type

Description

37.34. role_column_grants

37.35. role_routine_grants

37.37. role_table_grants

檢視表 role_table_grants 用於列出目前連線角色是授權者或被授權者，其資料表或檢視表上所被授予的權限。更多資訊可以在 table_privileges 下找到。此檢視表與 table_privileges 檢視表之間的唯一差別是，此檢視表省略了透過 PUBLIC 授權的方式使其可以存取的資料表。

Table 37.35. `role_table_grants` Columns

37.38. role_udt_grants

The view role_udt_grants is intended to identify USAGE privileges granted on user-defined types where the grantor or grantee is a currently enabled role. Further information can be found under udt_privileges. The only effective difference between this view and udt_privileges is that this view omits objects that have been made accessible to the current user by way of a grant to PUBLIC. Since data types do not have real privileges in PostgreSQL, but only an implicit grant to PUBLIC, this view is empty.

Table 37.36. `role_udt_grants` Columns

37.39. role_usage_grants

The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role. Further information can be found under usage_privileges. The only effective difference between this view and usage_privileges is that this view omits objects that have been made accessible to the current user by way of a grant to PUBLIC.

Table 37.37. `role_usage_grants` Columns

37.40. routine_column_usage

The view routine_column_usage is meant to identify all columns that are used by a function or procedure. This information is currently not tracked by PostgreSQL.

Table 37.38. `routine_column_usage` Columns

37.41. routine_privileges

The view routine_privileges identifies all privileges granted on functions to a currently enabled role or by a currently enabled role. There is one row for each combination of function, grantor, and grantee.

Table 37.39. `routine_privileges` Columns

37.45. routines

The view routines contains all functions and procedures in the current database. Only those functions and procedures are shown that the current user has access to (by way of being the owner or having some privilege).

Table 37.43. `routines` Columns

37.46. schemata

The view schemata contains all schemas in the current database that the current user has access to (by way of being the owner or having some privilege).

Table 37.44. `schemata` Columns

37.47. sequences

The view sequences contains all sequences defined in the current database. Only those sequences are shown that the current user has access to (by way of being the owner or having some privilege).

Table 37.45. `sequences` Columns

Note that in accordance with the SQL standard, the start, minimum, maximum, and increment values are returned as character strings.

37.48. sql_features

The table sql_features contains information about which formal features defined in the SQL standard are supported by PostgreSQL. This is the same information that is presented in Appendix D. There you can also find some additional background information.

Table 37.46. `sql_features` Columns

37.49. sql_implementation_info

The table sql_implementation_info contains information about various aspects that are left implementation-defined by the SQL standard. This information is primarily intended for use in the context of the ODBC interface; users of other interfaces will probably find this information to be of little use. For this reason, the individual implementation information items are not described here; you will find them in the description of the ODBC interface.

Table 37.47. `sql_implementation_info` Columns

37.50. sql_parts

The table sql_parts contains information about which of the several parts of the SQL standard are supported by PostgreSQL.

Table 37.48. sql_parts Columns

37.51. sql_sizing

The table sql_sizing contains information about various size limits and maximum values in PostgreSQL. This information is primarily intended for use in the context of the ODBC interface; users of other interfaces will probably find this information to be of little use. For this reason, the individual sizing items are not described here; you will find them in the description of the ODBC interface.

Table 37.49. sql_sizing Columns

36.51. table_constraints

The view table_constraints contains all constraints belonging to tables that the current user owns or has some privilege other than SELECT on.

Table 36.49. `table_constraints` Columns

Name

Data Type

Description

36.49. table_privileges

檢視圖 table_privileges 列出在資料表或檢視表上授權目前查詢的角色相關的所有權限。資料表、授權者和被授權者的每個組合形成一筆資料。

Table 36.47. `table_privileges` Columns

37.52. tables

37.53. transforms

37.54. triggered_update_columns

37.55. triggers

37.56. udt_privileges

37.57. usage_privileges

37.58. user_defined_types

37.59. user_mapping_options

37.60. user_mappings

37.64. view_routine_usage

The view view_routine_usage identifies all routines (functions and procedures) that are used in the query expression of a view (the SELECT statement that defines the view). A routine is only included if that routine is owned by a currently enabled role.

Table 37.62. `view_routine_usage` Columns

37.66. views

The view views contains all views defined in the current database. Only those views are shown that the current user has access to (by way of being the owner or having some privilege).

37. The Information Schema

37.1. The Schema

37.2. Data Types

37.3. information_schema_catalog_name

Table 37.1. information_schema_catalog_name Columns

37.4. administrable_role_authorizations

37.5. applicable_roles

Table 37.3. applicable_roles Columns

37.7. attributes

Table 37.4. attributes Columns

37.7. character_sets

Table 37.5. character_sets Columns

37.8. check_constraint_routine_usage

Table 37.6. check_constraint_routine_usage Columns

37.9. check_constraints

Table 37.7. check_constraints Columns

37.10. collations

Table 37.8. collations Columns

37.11. collation_character_set_applicability

Table 37.9. collation_character_set_applicability Columns

37.12. column_column_usage

Table 37.10. column_column_usage Columns

37.13. column_domain_usage

Table 37.11. column_domain_usage Columns

37.14. column_options

Table 37.12. column_options Columns

37.15. column_privileges

Table 37.13. column_privileges Columns

37.16. column_udt_usage

Table 37.14. column_udt_usage Columns

37.17. columns

Table 37.15. columns Columns

37.18. constraint_column_usage

Table 37.16. constraint_column_usage Columns

37.19. constraint_table_usage

Table 37.17. constraint_table_usage Columns

37.20. data_type_privileges

Table 37.18. data_type_privileges Columns

37.21. domain_constraints

37.21. domain_udt_usage

37.22. domains

37.23. element_types

37.24. enabled_roles

37.25. foreign_data_wrapper_options

37.26. foreign_data_wrappers

37.27. foreign_server_options

37.28. foreign_servers

37.29. foreign_table_options

37.30. foreign_tables

36.32. key_column_usage

Table 36.30. key_column_usage Columns

37.33. parameters

Table 37.31. parameters Columns

36.34. referential_constraints

Table 36.32. referential_constraints Columns

37.34. role_column_grants

37.35. role_routine_grants

37.37. role_table_grants

Table 37.35. role_table_grants Columns

37.38. role_udt_grants

Table 37.36. role_udt_grants Columns

37.39. role_usage_grants

Table 37.37. role_usage_grants Columns

37.40. routine_column_usage

Table 37.38. routine_column_usage Columns

37.41. routine_privileges

Table 37.39. routine_privileges Columns

37.45. routines

Table 37.43. routines Columns

37.46. schemata

Table 37.44. schemata Columns

37.47. sequences

Table 37.45. sequences Columns

37.48. sql_features

Table 37.46. sql_features Columns

37.49. sql_implementation_info

Table 37.47. sql_implementation_info Columns

37.50. sql_parts

37.51. sql_sizing

36.51. table_constraints

Table 37.1. `information_schema_catalog_name` Columns

Table 37.3. `applicable_roles` Columns

Table 37.4. `attributes` Columns

Table 37.5. `character_sets` Columns

Table 37.6. `check_constraint_routine_usage` Columns

Table 37.7. `check_constraints` Columns

Table 37.8. `collations` Columns

Table 37.9. `collation_character_set_applicability` Columns

Table 37.10. `column_column_usage` Columns

Table 37.11. `column_domain_usage` Columns

Table 37.12. `column_options` Columns

Table 37.13. `column_privileges` Columns

Table 37.14. `column_udt_usage` Columns

Table 37.15. `columns` Columns

Table 37.16. `constraint_column_usage` Columns

Table 37.17. `constraint_table_usage` Columns

Table 37.18. `data_type_privileges` Columns

Table 36.30. `key_column_usage` Columns

Table 37.31. `parameters` Columns

Table 36.32. `referential_constraints` Columns

Table 37.35. `role_table_grants` Columns

Table 37.36. `role_udt_grants` Columns

Table 37.37. `role_usage_grants` Columns

Table 37.38. `routine_column_usage` Columns

Table 37.39. `routine_privileges` Columns

Table 37.43. `routines` Columns

Table 37.44. `schemata` Columns

Table 37.45. `sequences` Columns

Table 37.46. `sql_features` Columns

Table 37.47. `sql_implementation_info` Columns

Table 36.49. `table_constraints` Columns

Table 36.47. `table_privileges` Columns

Table 37.62. `view_routine_usage` Columns

Table 37.64. `views` Columns

Table 37.6. `check_constraint_routine_usage` Columns

Table 37.7. `check_constraints` Columns

Table 37.11. `column_domain_usage` Columns

Table 37.4. `attributes` Columns

Table 37.3. `applicable_roles` Columns

Table 37.5. `character_sets` Columns

Table 37.13. `column_privileges` Columns

Table 37.12. `column_options` Columns

Table 37.8. `collations` Columns

Table 37.16. `constraint_column_usage` Columns

Table 37.1. `information_schema_catalog_name` Columns

Table 37.14. `column_udt_usage` Columns

Table 37.35. `role_table_grants` Columns

Table 37.10. `column_column_usage` Columns

Table 37.36. `role_udt_grants` Columns

Table 37.17. `constraint_table_usage` Columns

Table 36.32. `referential_constraints` Columns

Table 37.9. `collation_character_set_applicability` Columns

Table 37.37. `role_usage_grants` Columns

Table 37.18. `data_type_privileges` Columns

Table 37.43. `routines` Columns

Table 37.44. `schemata` Columns

Table 37.45. `sequences` Columns

Table 37.47. `sql_implementation_info` Columns