Information Schema in PostGreSQL

posted Sep 18, 2010, 6:36 AM by Sachchida Ojha
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 this 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.

The information schema is a facility to provide a standardized description of PostgreSQL metadata: definitions of tables, views etc. The information schema is defined in the SQL standard and should remain stable between PostgreSQL versions (unlike the system catalogues, PostgreSQL's "data dictionary") and should also be compatible with other database systems offering the same feature.

The information schema is kept in a separate schema - information_schema - which exists in all databases, but which is not included in the search path by default. It is implemented as a set of views returning specially defined datatypes as required by the standard. These types are implemented as simple domains over ordinary built-in types. information_schema can be dropped by the owner, but this does not provide any significant release of space or other advantages.

Example:

SELECT table_name FROM information_schema.tables WHERE table_schema='public'

will display the names of all tables in the default public schema.

Note that although the information schema also describes the system catalogue objects, it does not contain information on PostgreSQL-specific features. As always, invoking psql with the -E option and issuing appropriate slash commands will display the SQL used to query the system catalogues directly.

Comments