This chapter covers how one creates the database structures
that will hold one's data. In a relational database, the raw data
is stored in tables, so the majority of this chapter is devoted
to explaining how tables are created and modified and what
features are available to control what data is stored in the
tables. Subsequently, we discuss how tables can be organized into
schemas, and how privileges can be assigned to tables. Finally,
we will briefly look at other features that affect the data
storage, such as inheritance, views, functions, and triggers.

A table in a relational database is much like a table on
paper: It consists of rows and columns. The number and order of
the columns is fixed, and each column has a name. The number of
rows is variable -- it reflects how much data is stored at a
given moment. SQL does not make any guarantees about the order
of the rows in a table. When a table is read, the rows will
appear in random order, unless sorting is explicitly requested.
This is covered in Chapter 7.
Furthermore, SQL does not assign unique identifiers to rows, so
it is possible to have several completely identical rows in a
table. This is a consequence of the mathematical model that
underlies SQL but is usually not desirable. Later in this
chapter we will see how to deal with this issue.

Each column has a data type. The data type constrains the
set of possible values that can be assigned to a column and
assigns semantics to the data stored in the column so that it
can be used for computations. For instance, a column declared
to be of a numerical type will not accept arbitrary text
strings, and the data stored in such a column can be used for
mathematical computations. By contrast, a column declared to be
of a character string type will accept almost any kind of data
but it does not lend itself to mathematical calculations,
although other operations such as string concatenation are
available.

PostgreSQL includes a
sizable set of built-in data types that fit many applications.
Users can also define their own data types. Most built-in data
types have obvious names and semantics, so we defer a detailed
explanation to Chapter 8. Some of
the frequently used data types are integer for whole numbers, numeric for possibly fractional numbers, text for character strings, date for dates, time for
time-of-day values, and timestamp for
values containing both date and time.

To create a table, you use the aptly named CREATE TABLE command. In this command you
specify at least a name for the new table, the names of the
columns and the data type of each column. For example:

This creates a table named my_first_table with two columns. The first
column is named first_column and has a
data type of text; the second column has
the name second_column and the type
integer. The table and column names
follow the identifier syntax explained in Section 4.1.1. The
type names are usually also identifiers, but there are some
exceptions. Note that the column list is comma-separated and
surrounded by parentheses.

Of course, the previous example was heavily contrived.
Normally, you would give names to your tables and columns that
convey what kind of data they store. So let's look at a more
realistic example:

(The numeric type can store fractional
components, as would be typical of monetary amounts.)

Tip: When you create many interrelated tables it
is wise to choose a consistent naming pattern for the
tables and columns. For instance, there is a choice of
using singular or plural nouns for table names, both of
which are favored by some theorist or other.

There is a limit on how many columns a table can contain.
Depending on the column types, it is between 250 and 1600.
However, defining a table with anywhere near this many columns
is highly unusual and often a questionable design.

If you no longer need a table, you can remove it using the
DROP TABLE command. For example:

DROP TABLE my_first_table;
DROP TABLE products;

Attempting to drop a table that does not exist is an error.
Nevertheless, it is common in SQL script files to
unconditionally try to drop each table before creating it,
ignoring the error messages.

If you need to modify a table that already exists look into
Section 5.5 later in this
chapter.

With the tools discussed so far you can create fully
functional tables. The remainder of this chapter is concerned
with adding features to the table definition to ensure data
integrity, security, or convenience. If you are eager to fill
your tables with data now you can skip ahead to Chapter 6 and read the rest of this chapter
later.