Chapter 37. The Information Schema

Chapter 37. The Information Schema
Prev	Up	Part IV. Client Interfaces	Home	Next

Table of Contents

37.1. The Schema
37.2. Data Types
37.3. information_schema_catalog_name
37.4. administrable_role_authorizations
37.5. applicable_roles
37.6. attributes
37.7. character_sets
37.8. check_constraint_routine_usage
37.9. check_constraints
37.10. collations
37.11. collation_character_set_applicability
37.12. column_column_usage
37.13. column_domain_usage
37.14. column_options
37.15. column_privileges
37.16. column_udt_usage
37.17. columns
37.18. constraint_column_usage
37.19. constraint_table_usage
37.20. data_type_privileges
37.21. domain_constraints
37.22. domain_udt_usage
37.23. domains
37.24. element_types
37.25. enabled_roles
37.26. foreign_data_wrapper_options
37.27. foreign_data_wrappers
37.28. foreign_server_options
37.29. foreign_servers
37.30. foreign_table_options
37.31. foreign_tables
37.32. key_column_usage
37.33. parameters
37.34. referential_constraints
37.35. role_column_grants
37.36. role_routine_grants
37.37. role_table_grants
37.38. role_udt_grants
37.39. role_usage_grants
37.40. routine_column_usage
37.41. routine_privileges
37.42. routine_routine_usage
37.43. routine_sequence_usage
37.44. routine_table_usage
37.45. routines
37.46. schemata
37.47. sequences
37.48. sql_features
37.49. sql_implementation_info
37.50. sql_parts
37.51. sql_sizing
37.52. table_constraints
37.53. table_privileges
37.54. tables
37.55. transforms
37.56. triggered_update_columns
37.57. triggers
37.58. udt_privileges
37.59. usage_privileges
37.60. user_defined_types
37.61. user_mapping_options
37.62. user_mappings
37.63. view_column_usage
37.64. view_routine_usage
37.65. view_table_usage
37.66. views

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