Once created, you may use the \d describe command (followed by the table name) within psql to display the structure of the table, and its constraints (if any). This descriptive format is replicated in Table 4-2 for the books table created in the last section.

Notice that this format does not show actual row data, instead putting each column, and its attributes, in its own row , essentially turning the table on its side. This is done for the sake of clarity, as many tables can grow to be too large to fit on a screen (or on a page) horizontally, and it is the convention that is used throughout this book when examining table structure without data.

Table 4-2. The "books" Table

Column

Type

Modifier

id

integer

NOT NULL

title

text

author_id

integer

subject_id

integer

Index: books_pkey

id

The id column is a numeric identifier which is unique to each book. It is defined as being of the data type integer , and has on it the following constraints:

  • UNIQUE : this constraint ensures that the column always has a unique value. A column with the UNIQUE constraint set may ordinarily contain empty ( NULL values, but any attempt to insert duplicate values will fail. However, the id column is also designed as the PRIMARY KEY .

  • PRIMARY KEY : while not displayed in the \d breakdown, you can see in our original CREATE TABLE statement that this table's primary key is defined on the id column. Placing the constraint of PRIMARY KEY on a column implicitly sets both the NOT NULL and UNIQUE constraints as well.

title

The title column of the book main contain character strings of type text . Text is more flexible than varchar, and is a good choice for this column, as it does not require that you specify the maximum number of characters allowed.

  • NOT NULL : this constraint ensures that the column always has a value. It can never empty, and any attempt to insert NULL values will fail. Therefore, there can never be a book with an empty ( NULL ) title, though there could be a book with a blank title ( '' ).

author_id

The author_id may contain values of type integer , and relates to the authors table for further information on the related author_id . There are no constraints placed on this column, as sometimes an author may not be conclusively known for a title (making NOT NULL inappropriate), and an author may certainly show up more than once (showing UNIQUE to also be inappropriate).

subject_id

The subject_id is similar to the author_id column, as it may contain values of type integer , and relates to the subjects table for further information on the related subject_id . Again, there are no constraints on the contents of this column, as many books may fall under the same subject, .

While a table's structure can be somewhat modified after it has been created, the available modifications are limited. These include, for example, re-naming the table, re-naming its columns, and adding new columns. As of PostgreSQL 7.1.x, dropping columns from a table is not supported. It is therefore good practice to thoughtfully and carefully plan your table structures before jumping into creating them.