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.

Note

When querying the database for constraint information, it is possible for a standard-compliant query that expects to return one row to return several. This is because the SQL standard requires constraint names to be unique within a schema, but PostgreSQL does not enforce this restriction. PostgreSQL automatically-generated constraint names avoid duplicates in the same schema, but users can specify such duplicate names.

This problem can appear when querying information schema views such as check_constraint_routine_usage, check_constraints, domain_constraints, and referential_constraints. Some other views have similar issues but contain the table name to help distinguish duplicate rows, e.g., 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 zoneyes_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 typeboolean 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

36.4. administrable_role_authorizations

36.5. applicable_roles

36.6. attributes

36.7. character_sets

36.8. check_constraint_routine_usage

36.9. check_constraints

36.10. collations

36.11. collation_character_set_applicability

36.12. column_domain_usage

36.13. column_options

36.14. column_privileges

36.15. column_udt_usage

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

The view key_column_usage identifies all columns in the current database that are restricted by some unique, primary key, or foreign key constraint. Check constraints are not included in this view. Only those columns are shown that the current user has access to, by way of being the owner or having some privilege.

Table 36.30. key_column_usage Columns

37.32. parameters

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

37.36. role_table_grants

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

37.51. table_privileges

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

Note

36.1. The Schema

36.2. Data Types

36.3. information_schema_catalog_name

36.4. administrable_role_authorizations

36.5. applicable_roles

36.6. attributes

36.7. character_sets

36.8. check_constraint_routine_usage

36.9. check_constraints

36.10. collations

36.11. collation_character_set_applicability

36.12. column_domain_usage

36.13. column_options

36.14. column_privileges

36.15. column_udt_usage

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

37.32. parameters

36.34. referential_constraints

Table 36.32. referential_constraints Columns

37.34. role_column_grants

37.35. role_routine_grants

37.36. role_table_grants

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

37.51. table_privileges

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

37.63. view_table_usage

37.64. views

36. The Information Schema

Note

36.1. The Schema

36.2. Data Types

36.18. constraint_column_usage

Table 36.16. constraint_column_usage Columns

36.32. key_column_usage

36.17. columns

Table 36.15. columns Columns

Table 36.15. `columns` Columns

Table 36.16. `constraint_column_usage` Columns

Table 36.32. `referential_constraints` Columns

Table 36.40. `schemata` Columns

Table 36.49. `table_constraints` Columns

Table 36.16. `constraint_column_usage` Columns

Table 36.15. `columns` Columns

Table 36.49. `table_constraints` Columns

Table 36.32. `referential_constraints` Columns

Table 36.40. `schemata` Columns