Contents
Information schema
A special schema containing
The information schema
is a special schema containing standardized views containing information about the objects defined in the current database.
The information schema
was added in PostgreSQL 7.4.
Usage
The information schema
provides a mostly SQL-standard compliant way of accessing metadata about PostgreSQL database objects which should be stable across major releases. This provides a more stable interface than is available via PostgreSQL's system catalogue tables and views, however does not include information about PostgreSQL-specific features.
Change history
Work-in-progress
- PostgreSQL 14
- routine usage tables added as placeholders (commit f40c6969)
- PostgreSQL 8.2
- SQL:2003 support (commit f7ae9004)
- PostgreSQL 8.0
- support for named parameters in
information_schema.parameters
(commit 9d888807)
- support for named parameters in
- PostgreSQL 7.4
- added (initial commit 1b42ad7e)
Examples
Base metadata about a table as exposed by the information_schema.tables
view:
postgres=# CREATE TABLE foo (id int); CREATE TABLE postgres=# SELECT * FROM information_schema.tables WHERE table_name='foo'; -[ RECORD 1 ]----------------+----------- table_catalog | postgres table_schema | public table_name | foo table_type | BASE TABLE self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | YES is_typed | NO commit_action |
References
- PostgreSQL documentation: The Information Schema