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