Information Schema in PostGreSQL

posted Sep 18, 2010, 6:36 AM by Sachchida Ojha

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.


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.