Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

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
 distributors     | table | bruce
 rentals          | table | bruce
 returns          | table | John Whorfin
 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) |
 dist_id | integer               |
Primary key: tapes_pkey
Triggers: RI_ConstraintTrigger_74939,

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

movies=# \di
        List of relations
      Name                 | Type  |   Owner
customers_pkey             | index | Administrator
distributors_pkey          | index | Administrator
tapes_pkey                 | index | Administrator

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

movies=# \d tapes
  Index "tapes_pkey"
 Column  |   Type
 tape_id | character(8)
unique btree (primary key)

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

Table 3.1 System Catalog Meta-Commands






List all tables


List all indexes


List all sequences


List all views


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


List all privileges


List all large objects


List all aggregates


List all functions

\df function-name

List all functions with given name


List all operators

\do operator-name

List all operators with given name


List all types


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

  • + Share This
  • 🔖 Save To Your Account