Practical PostgreSQL

Using Tables

Tables are the fundamental building blocks with which to store data within your database. Before you can begin to add, retrieve, or modify data within your database, you will first have to construct your tables to house that data.

This section covers how to create, modify and destroy tables, using the CREATE TABLE, ALTER TABLE, and DROP TABLE SQL commands. (If you need information about creating a database within which to work, see Chapter 9.)

Creating Tables with CREATE TABLE

The SQL command to create a table is CREATE TABLE. This command requires, at a minimum, the name for the new table and a description for each column, which consists of the column name and data type. The CREATE TABLE command accepts several optional parameters: column constraints (rules on what data is or is not allowed within a column), and table constraints (general limitations and relationships defined on the table itself).

CREATE TABLE syntax

The following is the syntax for CREATE TABLE with a detailed explanation of the terms used:

CREATE [ TEMPORARY | TEMP ] TABLE table_name (
       { column_name type [ column_constraint [... ] ] | table_constraint  }
       [, ... ]
       ) [ INHERITS ( inherited_table [, ... ] ) ]

TEMPORARY | TEMP

The TEMPORARY or TEMP SQL keyword causes the created table to be automatically destroyed at the end of the active session to PostgreSQL. A temporary table may have the same name as an existing table, and until the temporary table is destroyed, any references to that table name will utilize ...