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 rentals | table | bruce tapes | table | bruce (3 rows) movies=# d tapes Table "tapes" Column | Type | Modifiers ----------+-----------------------+----------- tape_id | character(8) | title | character varying(80) | duration | interval | Indexes: "tapes_tape_id_key" UNIQUE, btree (tape_id) movies=# d customers Table "customers" Attribute | Type | Modifier --------------+-----------------------+---------- customer_id | integer | customer_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 columnmandatory 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."

Depending on which version of PostgreSQL you're using, you may see each table name listed as "Table "public.table-name". The "public" part is the name of the schema that the table is defined in.