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:

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 another—every 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_SCHEMA instead of psql's d commands? We can think of three reasons. First, you can use the INFORMATION_SCHEMA inside of your own client applications—you 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_SCHEMA—you 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 on—the PostgreSQL system tables typically contain OIDs that you have to JOIN to another table in order to come up with a human-readable name.