1 of 65

36. 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。

36.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.

36.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.

36.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 36.1. `information_schema_catalog_name` Columns

36.4. administrable_role_authorizations

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

Table 36.2. `administrable_role_authorizations` Columns

36.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 36.3. `applicable_roles` Columns

36.6. 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 36.4. `attributes` Columns

36.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 36.5. `character_sets` Columns

36.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 36.6. check_constraint_routine_usage Columns

36.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 36.7. `check_constraints` Columns

36.10. collations

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

Table 36.8. `collations` Columns

36.11. collation_character_set_applicability

36.12. column_domain_usage

36.13. column_options

36.14. column_privileges

36.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 for details.

Table 36.14. `column_udt_usage` Columns

36.17. columns

此檢視表欄位包含了有關資料庫中所有資料表欄位（或檢視表欄位）的資訊。不包括系統欄位（ctid 等）。僅顯示目前使用者有權存取的那些欄位（擁有者或具有某些權限）。

Table 36.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.

36.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 36.16. `constraint_column_usage` Columns

37.18. constraint_table_usage

37.19. data_type_privileges

37.20. 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

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

Table 36.30. `key_column_usage` Columns

36.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 36.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

37.34. role_column_grants

37.35. role_routine_grants

36.37. role_table_grants

檢視表 role_table_grants 列出會授權者或被授權者為目前查詢的角色的資料表或檢視表所關連的所有權限。更多資訊可以參考在 table_privileges 裡。此檢視表與 table_privileges 之間唯一有效的區別是，此檢視表不包含了透過授權給 PUBLIC 的方式使目前角度有權存取的資料表。

Table 36.35. `role_table_grants` Columns

37.37. role_udt_grants

37.38. role_usage_grants

37.39. routine_privileges

37.40. routines

36.42. schemata

此檢視表 schemata 包含目前資料庫中目前使用者有權限存取的所有 schema（透過成為擁有者或具有某些權限）。

Table 36.40. `schemata` Columns

37.42. sequences

37.43. sql_features

37.44. sql_implementation_info

37.45. sql_languages

37.46. sql_packages

37.47. sql_parts

37.48. sql_sizing

37.49. sql_sizing_profiles

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

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

36. The Information Schema

36.1. The Schema

36.2. Data Types

36.3. information_schema_catalog_name

Table 36.1. information_schema_catalog_name Columns

36.4. administrable_role_authorizations

Table 36.2. administrable_role_authorizations Columns

36.5. applicable_roles

Table 36.3. applicable_roles Columns

36.6. attributes

Table 36.4. attributes Columns

36.7. character_sets

Table 36.5. character_sets Columns

36.8. check_constraint_routine_usage

36.9. check_constraints

Table 36.7. check_constraints Columns

36.10. collations

Table 36.8. collations Columns

36.11. collation_character_set_applicability

36.12. column_domain_usage

36.13. column_options

36.14. column_privileges

36.16. column_udt_usage

Table 36.14. column_udt_usage Columns

36.17. columns

Table 36.15. columns Columns

36.18. constraint_column_usage

Table 36.16. constraint_column_usage Columns

37.18. constraint_table_usage

37.19. data_type_privileges

37.20. 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

36.33. parameters

Table 36.31. parameters Columns

36.34. referential_constraints

Table 36.32. referential_constraints Columns

37.34. role_column_grants

37.35. role_routine_grants

36.37. role_table_grants

Table 36.35. role_table_grants Columns

37.37. role_udt_grants

37.38. role_usage_grants

37.39. routine_privileges

37.40. routines

36.42. schemata

Table 36.40. schemata Columns

37.42. sequences

37.43. sql_features

37.44. sql_implementation_info

37.45. sql_languages

37.46. sql_packages

37.47. sql_parts

37.48. sql_sizing

37.49. sql_sizing_profiles

36.51. table_constraints

Table 36.49. table_constraints Columns

36.49. 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.61. view_column_usage

37.62. view_routine_usage

Table 36.1. `information_schema_catalog_name` Columns

Table 36.2. `administrable_role_authorizations` Columns

Table 36.3. `applicable_roles` Columns

Table 36.4. `attributes` Columns

Table 36.5. `character_sets` Columns

Table 36.7. `check_constraints` Columns

Table 36.8. `collations` Columns

Table 36.14. `column_udt_usage` Columns

Table 36.15. `columns` Columns

Table 36.16. `constraint_column_usage` Columns

Table 36.30. `key_column_usage` Columns

Table 36.31. `parameters` Columns

Table 36.32. `referential_constraints` Columns

Table 36.35. `role_table_grants` Columns

Table 36.40. `schemata` Columns

Table 36.49. `table_constraints` Columns

Table 36.47. `table_privileges` Columns

Table 36.3. `applicable_roles` Columns

Table 36.2. `administrable_role_authorizations` Columns

Table 36.14. `column_udt_usage` Columns

Table 36.8. `collations` Columns

Table 36.7. `check_constraints` Columns

Table 36.1. `information_schema_catalog_name` Columns

Table 36.4. `attributes` Columns

Table 36.5. `character_sets` Columns

Table 36.16. `constraint_column_usage` Columns

Table 36.15. `columns` Columns

Table 36.30. `key_column_usage` Columns

Table 36.31. `parameters` Columns

Table 36.35. `role_table_grants` Columns

Table 36.40. `schemata` Columns

Table 36.32. `referential_constraints` Columns

Table 36.47. `table_privileges` Columns