This page in other versions: 9.1 / 9.2 / 9.3 / 9.4 / current (9.5)  |  Development versions: devel  |  Unsupported versions: 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0

Chapter 31. 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 modelled 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.

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


July 15, 2006, 10:57 p.m.

Since this doesn't appear obvious at first, and that there are no provided examples (or perhaps there are but were very hard to locate), here consists of an example listing table names for the current database:

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

And another example to retreive some information about the columns of a table named 'events':

SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE table_name='events';

Furthermore, you can obtain online information about the available schema components using in the psql client:

# SET search_path TO INFORMATION_SCHEMA, public;
# \d

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group