Getting Information About Databases and Tables

When you create a table, PostgreSQL stores the definition of that table in the system catalog. The system catalog is a collection of PostgreSQL tables. You can issue SELECT statements against the system catalog tables just like any other table, but there are easier ways to view table and index definitions.

When you are using the psql client application, you can view the list of tables defined in your database using the d meta-command:

movies=# d
 List of relations
 Name | Type | Owner
------------------+-------+---------------
 customers | table | bruce
 rentals | table | bruce
 tapes | table | bruce

To see the detailed definition of a particular table, use the d table-name meta-command:

movies=# d tapes
 Table "tapes"
 Column | Type | Modifiers
----------+-----------------------+-----------
 tape_id | character(8) | not null
 title | character varying(80) | not null
 duration | interval |

You can also view a list of all indexes defined in your database. The di meta-command displays indexes:

movies=# di
 List of relations
 Schema | Name | Type | Owner | Table
--------+---------------------------+-------+-------+-----------
 public | customers_customer_id_key | index | korry | customers

You can see the full definition for any given index using the d index-name meta-command:

movies=# d customers_customer_id_key
Index "public.customers_customer_id_key"
 Column | Type
-------------+---------
 customer_id | integer
UNIQUE, btree, for table "public.customers"

Table 3.1 shows a complete list of the system catalog-related meta-commands in psql:

Table 3.1. System Catalog Meta-Commands

Command	Result
dd object-name	Display comments for object-name
db	List all tablespaces
dn	List all schemas
d_dt	List all tables
di	List all indexes
ds	List all sequences
dv	List all views
dS	List all PostgreSQL-defined tables
d table-name	Show table definition
d index-name	Show index definition
d view-name	Show view definition
d sequence-name	Show sequence definition
dp	List all privileges
dl	List all large objects
da	List all aggregates
df	List all functions
dc	List all conversions
dC	List all casts
df function-name	List all functions with given name
do	List all operators
do operator-name	List all operators with given name
dT	List all types
dD	List all domains
dg	List all groups
du	List all users
l	List all databases in this cluster

Alternative Views (Oracle Style Dictionary Views)

One of the nice things about an open-source product is that code contributions come from many different places. One such project exists to add Oracle-style dictionary views to PostgreSQL. If you are an experienced Oracle user, you will appreciate this feature. The orapgsqlviews project contributes Oracle-style views such as all_views, all_tables, user_tables, and so on. For more information, see http://gborg.postgresql.org.

PostgreSQL version 8.0 introduced a set of views known as the INFORMATION_SCHEMA. The views defined in the INFORMATION_SCHEMA give you access to the information stored in the PostgreSQL system tables. The INFORMATION_SCHEMA is defined as part of the SQL standard and you'll find an INFORMATION_SCHEMA in most commercial (and a few open-source) database systems. If you become familiar with the views defined in the INFORMATION_SCHEMA, you'll find it much easier to move from one RDBMS system to anotherevery INFORMATION_SCHEMA contains the same set of views, each containing the same set of columns. For example, to see a list of the tables defined in your current database, you could execute the command:

SELECT table_schema, table_name, table_type FROM information_schema.tables;

You can execute that same query in DB2, MS SQL Server, or Informix (sadly, Oracle doesn't support the INFORMATION_SCHEMA standard at the time we are writing this). So what can you find in the INFORMATION_SCHEMA?

• schemata Lists the schemas (in the current database) that are owned by you
• tables Lists all tables in the current database (actually, you only see those tables that you have the right to access in some way)
• columns Lists all columns in all tables that you have the right to access
• views Lists all of the views you have access to in the current database
• table_privileges Shows the privileges you hold (or that you granted) for each accessible object in the current database
• domains Lists all of the domains defined in the current database
• check_constraints Lists all of the CHECK constraints defined for the accessible tables (or domains) in the current database

There are more views in the INFORMATION_SCHEMA than we've described here (in fact, there are a total of 39 INFORMATION_SCHEMA views in PostgreSQL 8.0). See Chapter 30, "The Information Schema," of the PostgreSQL user guide for a complete list.

Why would you want to use the INFORMATION_SCHEMAinstead of psql's d commands? We can think of three reasons. First, you can use the INFORMATION_SCHEMA inside of your own client applicationsyou can't do that with the d commands because they are part of the psql console application (itself a PostgreSQL client) instead of the PostgreSQL server. Second, by using the views defined in the INFORMATION_SCHEMA, you can read the PostgreSQL system tables using the same queries that you would use to read the DB2 system tables (or Sybase or SQL Server). That makes your client applications a bit more portable. Finally, you can write custom queries against the views defined in the INFORMATION_SCHEMAyou can't customize the d commands. For example, if you need to find all of the date columns in your database, just look inside of INFORMATION_SCHEMA.columns, like this:

SELECT DISTINCT table_name 
 FROM information_schema.columns WHERE data_type = 'date';

Need to know which columns can hold a NUMERIC value of at least seven digits? Use this query:

SELECT table_name,column_name, numeric_precision
 FROM information_schema.columns
 WHERE data_type = 'numeric' AND numeric_precision >= 7;

Of course, you can find all the information exposed by the INFORMATION_SCHEMA in the PostgreSQL system tables (pg_class, pg_index, and so on), but the INFORMATION_SCHEMA is often much easier to work with. The INFORMATION_SCHEMA views usually contain human-readable names for things like data type names, table names, and so onthe PostgreSQL system tables typically contain OIDs that you have to JOIN to another table in order to come up with a human-readable name.