Viewing Table Descriptions

At this point, you've defined three tables in the movies database: tapes, customers, and rentals. If you want to view the table definitions, you can use the \d meta-command in psql (remember that a meta-command is not really a SQL command, but a command understood by the psql client). The \d meta-command comes in two flavors: If you include a table name (\d customers), you will see the definition of that table; if you don't include a table name, \d will show you a list of all the tables defined in your database.

$ psql -d movies

Welcome to psql, the PostgreSQL interactive terminal.



Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help on internal slash commands

       \g or terminate with semicolon to execute query

       \q to quit



movies=# \d

         List of relations

   Name    | Type  |     Owner

-----------+-------+---------------

 customers | table | bruce

 rental    | table | bruce

 tapes     | table | bruce

(3 rows)



movies=# \d tapes

                 Table "tapes"

  Attribute  |         Type          | Modifier

-------------+-----------------------+----------

 tape_id     | character(8)          |

 title       | character varying(80) |

 distributor | character varying(80) |

Index: tapes_tape_id_key



movies=# \d customers

               Table "customers"

  Attribute  |         Type          | Modifier

-------------+-----------------------+----------

 customer_id | integer               |

 name        | character varying(50) |

 phone       | character(8)          |

 birth_date  | date                  |

 balance     | numeric(7,2)          |

Index: customers_customer_id_key



movies=# \d rentals

            Table "rentals"

  Attribute  |     Type     | Modifier

-------------+--------------+----------

 tape_id     | character(8) |

 customer_id | integer      |

 rental_date | date         |



movies=#

I'll point out a few things about the \d meta-command.

Notice that for each column in a table, the \d meta-command returns three pieces of information: the column name (or Attribute), the data type, and a Modifier.

The data type reported by the \d meta-command is spelled-out; you won't see char(n) or varchar(n), you'll see character(n) and character varying(n) instead.

The Modifier column shows additional column attributes. The most commonly encountered modifiers are NOT NULL and DEFAULT .... The NOT NULL modifier appears when you create a mandatory column?mandatory means that each row in the table must have a value for that column. The DEFAULT ... modifier appears when you create a column with a default value: A default value is inserted into a column when you don't specify a value for a column. If you don't specify a default value, PostgreSQL inserts the special value NULL. I'll discuss NULL values and default values in more detail in Chapter 2.

You might have noticed that the listing for the tapes and customers tables show that an index has been created. PostgreSQL automatically creates an index for you when you define UNIQUE columns. An index is a data structure that PostgreSQL can use to ensure uniqueness. Indexes are also used to increase performance. I'll cover indexes in more detail in Chapter 3, "PostgreSQL SQL Syntax and Use."