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 the temporary table. Any indices placed on this table are temporary and will be destroyed in the same fashion at the end of the session.

table_name

table_name identifies your table’s name (once created).

column_name type [ column_constraint ] | table_constraint

Each table column and table constraint is defined within the parentheses following the table name, separated by commas. Column definitions must contain a valid identifier for a column_name, followed by a valid data type, and may optionally include a column_constraint. The requirements of column constraint definitions are dependent on the constraints, described in the section titled Using Constraints in Chapter 7. Table constraints and columns may be mixed in this grouped list, though it is common practice to list columns first, followed by any table constraints.

[, ... ]

Each column definition may be followed by a comma in order to define a subsequent column after it. The ellipses denote that you may enter as many columns as you wish (up to the limit of 1,600). Be sure that you do not follow the last column or constraint in the list with a comma, as is allowed in languages like Perl; this will cause a parsing error.

INHERITS ( inherited_table [, ...] )

The object-relational capabilities of PostgreSQL allow you to specify one or more tables (in a grouped, comma-delimited list) from which your table will inherit. This optional specification creates an implied parent-child relationship between tables. This relatively new technique to RDBMSs is discussed in more detail in the section titled Inheritance within Chapter 7.

Note

The terms column_constraint and table_constraint in the above syntax definition refer to sets of potentially complex constraint definitions. The syntax for these various constraints is listed in detail in the section titled Using Constraints within Chapter 7.

Creating an example table

Example 4-6 demonstrates the syntax to create Book Town’s books table.

Example 4-6. Creating the books table

booktown=#  CREATE TABLE books (
booktown(#               id integer UNIQUE,
booktown(#               title text NOT NULL,
booktown(#               author_id integer,
booktown(#               subject_id integer,
booktown(#               CONSTRAINT books_id_pkey PRIMARY KEY (id));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'books'
CREATE

The CREATE output following the execution of the statement indicates that the table was successfully created. If you receive an error message, check your punctuation and spelling to make sure you have entered the correct syntax. Receiving no message at all means that you probably left open a quote, parenthesis, or other special character symbol.

Additionally, the NOTICE statement serves to inform you that in order to properly complete the creation of this table as described, an implicit index called books_id_pkey will be created.

Examining a created table

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). Example 4-7 shows the output of \d when it is used to describe the books table created in the last section.

Notice that this format does not show actual row data, but instead places 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 too large to fit on a screen (or on a page) horizontally. We’ll use this format throughout the book when examining table structure without data.

Example 4-7. The \d command’s output

booktown=#  \d books
          Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

The following list provides a more detailed explanation of the fields and terms shown in Example 4-7:

id

The id column is a numeric identifier 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. The id column is also designed to be used 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.

NOT NULL

This constraint is set automatically by setting the PRIMARY KEY constraint. It ensures that the ID column always has a value. Data for this column can never be empty, and any attempt to insert NULL values will fail.

title

The title column of the table must contain character strings of type text. The text type 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. This column has the NOT NULL constraint set, indicating that a row’s title column cannot ever be set to NULL.

author_id

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

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. Again, there are no constraints on the contents of this column, as many books may be uncategorized, or fall under the same subject.

While a table’s structure can be modified after it has been created, the available modifications are limited. These include, for example, renaming the table, renaming its columns, and adding new columns. PostgreSQL 7.1.x does not support dropping columns from a table. It is therefore good practice to thoughtfully and carefully plan your table structures before creating them.

Altering Tables with ALTER TABLE

Most mature RDBMSs allow you to alter the properties of existing tables via the ALTER TABLE command. The PostgreSQL implementation of ALTER TABLE allows for six total types of table modifications as of version 7.1.x:

• Adding columns

• Setting and removing default column values

• Renaming the table

• Renaming columns

• Adding constraints

• Changing ownership

Adding columns

You can add a new column to a table using the ALTER TABLE command’s ADD COLUMN clause. Here is the syntax for the ALTER TABLE command’s ADD COLUMN clause:

ALTER TABLE table
      ADD [ COLUMN ] column_name column_type

table_name

The name of the table to modify.

column_name

The name of the column to add.

column_type

The data type of the new column.

Technically, the COLUMN keyword may be omitted; it is considered a noise term and is only useful for your own readability.

As an example of adding a column, imagine that an industrious employee at Book Town decides that the books table requires another column, specifically, a date column to represent the publication date. Example 4-8 demonstrates such a procedure.

Example 4-8. Adding a column

booktown=#  ALTER TABLE books
booktown-#        ADD publication date;
ALTER
booktown=# \d books
          Table "books"
 Attribute   | Type    | Modifier
-------------+---------+----------
 id          | integer | not null
 title       | text    | not null
 author_id   | integer |
 subject_id  | integer |
 publication | date    |
Index: books_id_pkey

Example 4-8 successfully adds a new column to Book Town’s books table with the name of publication, and a data type of date. It also demonstrates a pitfall of uncoordinated table design among developers: in our examples, the Book Town editions table already stores the publication date, so the column should not have been added to the books table. See the section titled Restructuring Existing Tables for information on how to restructure a table after such a mistake has been made.

Setting and removing default values

The most flexible table modification pertains to the default values of columns. These values may be both set and removed from a column with relative ease via the ALTER TABLE command’s ALTER COLUMN clause.

The following syntax passed to PostgreSQL describes how to use ALTER TABLE in order to either set, or remove a default value of value from a column named column_name:

ALTER TABLE table
      ALTER [ COLUMN ] column_name
      { SET DEFAULT value | DROP DEFAULT }

Again, the COLUMN keyword is considered noise, and is an optional term used only for improved readability of the statement. Example 4-9 demonstrates setting and dropping a simple default sequence value on the books table’s id column.

Example 4-9. Altering column defaults

booktown=#  ALTER TABLE books
booktown-#        ALTER COLUMN id
booktown-#        SET DEFAULT nextval('book_ids');
ALTER
booktown=#  \d books
                            Table "books"
 Attribute  |  Type   |                   Modifier
------------+---------+--------------------------------------------
 id         | integer |  not null default nextval('book_ids'::text)
 title      | text    |  not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

booktown=#  ALTER TABLE books
booktown-#        ALTER id
booktown-#        DROP DEFAULT;
ALTER
booktown=#  \d books
           Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

Renaming a table

A table may be safely renamed by passing the RENAME clause with the ALTER TABLE command. The following is the syntax to rename a table:

ALTER TABLE table
      RENAME TO new_table

A table may be arbitrarily renamed as many times as you like without affecting the data. This could, of course, be a dangerous thing to do if you are dealing with a table on which an external application relies.

Example 4-10. Renaming a table

booktown=# ALTER TABLE books RENAME TO literature;
ALTER
booktown=# ALTER TABLE literature RENAME TO books;
ALTER

Renaming columns

A table’s columns may be safely renamed in PostgreSQL without modifying the data contained in the table. Renaming a column is a dangerous thing to do because existing applications may use explicit references to column names. If an existing program references a column by name and the column is renamed, the program could cease functioning correctly.

The following syntax describes how to rename a column:

ALTER TABLE table
      RENAME [ COLUMN ] column_name TO new_column_name;

As with the other ALTER TABLE commands, the COLUMN keyword is considered noise, and may be optionally omitted. The existence of two identifiers separated by the TO keyword provides enough information for PostgreSQL to determine that you are renaming a column, and not a table, as demonstrated in Example 4-11.

Example 4-11. Renaming a column

booktown=# \d daily_inventory
    Table "daily_inventory"
 Attribute |  Type   | Modifier
-----------+---------+----------
 isbn      | text    |
 in_stock  | boolean |

booktown=#  ALTER TABLE daily_inventory
booktown-#        RENAME COLUMN in_stock TO is_in_stock;
ALTER
booktown=#  ALTER TABLE daily_inventory
booktown-#        RENAME is_in_stock TO is_stocked;
ALTER

Adding constraints

Constraints may be added in a limited fashion after a table has been created. As of PostgreSQL 7.1.x, only foreign key and check constraints may be added to an existing table column with ALTER TABLE. The following is the syntax to add a constraint to a table:

ALTER TABLE table
      ADD CONSTRAINT constraint_name constraint_definition

The syntax of the constraint_definition is dependent on the type of constraint you wish to add. As foreign keys and checks are the only supported constraints with the ADD CONSTRAINT clause (as of PostgreSQL 7.1.x), the syntax for adding a foreign key to the editions table (which references the books table’s id column) and a check condition on the type column is demonstrated in Example 4-12.

Example 4-12. Adding constraints to a table

booktown=# ALTER TABLE editions
booktown-#       ADD CONSTRAINT foreign_book
booktown-#       FOREIGN KEY (book_id) REFERENCES books (id);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
booktown=# ALTER TABLE editions
booktown-#       ADD CONSTRAINT hard_or_paper_back
booktown-#       CHECK (type = 'p' OR type = 'h');
ALTER

Due to the foreign key constraint, any book_id value in the editions table will now also have to exist in the books table. Additionally, due to the check constraint, the type values within the editions table may only be set to either p or h.

Note

To implicitly add a unique constraint, a workaround is to create a unique index using the CREATE INDEX command (see the section titled Indices in Chapter 4).

See the section titled Using Constraints in Chapter 7 for more detailed information about constraints, their purpose, and their syntax.

Changing ownership

By default, the creator of a table is automatically its owner. The owner has all rights that can be associated with a table, in addition to the ability to grant and revoke rights with the GRANT and REVOKE commands (for more information see Chapter 10). If ownership must be changed, you can use the ALTER TABLE command’s OWNER clause. The syntax to change the ownership of a table from one user to another is:

ALTER TABLE table
      OWNER TO new_owner

Example 4-13 demonstrates altering a table’s ownership with the ALTER TABLE command’s OWNER clause. In it, corwin is set as the owner of the employees table.

Example 4-13. Changing table ownership

booktown=#  ALTER TABLE employees
booktown-#        OWNER TO corwin;
ALTER

Note

In order to change the ownership of a table, you must either be the owner of that table or a PostgreSQL superuser.

Restructuring Existing Tables

While you have the ability to arbitrarily add new columns to existing tables, remember that (as of PostgreSQL 7.1.x) you cannot drop columns from existing tables. There are two fairly painless workarounds for restructuring existing tables. The first involves the CREATE TABLE AS command, while the second combines the CREATE TABLE command with the INSERT INTO command.

Each of these methods, in essence, involves creating a new table with your desired structure, filling it up with the data from your existing table, and renaming the tables so that the new table takes the place of your old table.

Note

When “restructuring” a table in this fashion, it is important to notice that old indices placed on the original table will not automatically be applied to the newly created table, nor will the OIDs (object identifiers) be the same. Any indices must be dropped and recreated.

Restructuring with CREATE TABLE AS

One common technique of restructuring a table is to use the CREATE TABLE command in conjunction with the AS clause and a valid SQL query. This allows you to restructure your existing table into a temporary table, which can then be renamed. Doing this also allows you to both remove and re-arrange columns to a table by physically re-creating it, and simultaneously re-populating it with data from the original table.

The following syntax describes this limited version of CREATE TABLE, where query is the valid SELECT statement that selects the data to populate the new table with. The data type of each created column is implied by the type of each corresponding column selected by query:

CREATE [ TEMPORARY | TEMP ] TABLE table
       [ ( column_name [, ...] ) ]
       AS query

The advantage to this technique is that you may create the new table and populate it in a single SQL command. The most notable limitation of this technique is that there is no comprehensive way to set constraints on the newly created table; the only constraints that may be added to the table after is has been created are the foreign key and check constraints. Once the new table has been created, the old one can be renamed (or destroyed), and the new one can be renamed to the name of the original table.

Suppose, for example, that you wanted to modify the books table in order to drop the superfluous publication column which was created in the section titled Adding columns. You can create a limited copy of the table (designating only the desired columns) by passing a valid SELECT statement to the AS clause of CREATE TABLE, and dropping the old table with DROP TABLE, as shown in Example 4-14.

Example 4-14. Restructuring a table with CREATE TABLE AS

booktown=# \d books
          Table "books"
  Attribute  |  Type   | Modifier
-------------+---------+----------
 id          | integer | not null
 title       | text    | not null
 author_id   | integer |
 subject_id  | integer |
 publication | date    |
Index: books_id_pkey

booktown=# CREATE TABLE new_books
booktown-#        (id, title, author_id, subject_id)
booktown-#        AS SELECT id, title, author_id, subject_id
booktown-#                  FROM books;
SELECT
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
        Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer |
 title      | text    |
 author_id  | integer |
 subject_id | integer |

booktown=# DROP TABLE books;
DROP

Note

As of PostgreSQL 7.1.x, if you specify the optional column list within parentheses, you cannot use the asterisk (*) in the query statement. This behavior is scheduled to be corrected in PostgreSQL 7.2.

Restructuring with CREATE TABLE and INSERT INTO

If you require a more specifically defined table than that created by CREATE TABLE AS (e.g., one with column constraints), you can replicate the effect of the CREATE TABLE AS technique by issuing two SQL statements rather than one. You can achieve this by first creating the new table as you ordinarily would with CREATE TABLE, and then populating the table with data via the INSERT INTO command and a valid SELECT statement.

Example 4-15. Restructuring a table with CREATE TABLE and INSERT INTO

booktown=#  CREATE TABLE new_books (
booktown(#    id integer UNIQUE,
booktown(#    title text NOT NULL,
booktown(#    author_id integer,
booktown(#    subject_id integer,
booktown(#    CONSTRAINT books_id_pkey PRIMARY KEY (id)
booktown(#  );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'new_books'
CREATE
booktown=# INSERT INTO new_books
booktown-#             SELECT id, title, author_id, subject_id
booktown-#                    FROM books;
INSERT 0 12
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
          Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

See the section titled Inserting Values from Other Tables with SELECT for more information about using the INSERT INTO command with a SELECT statement, and the section titled Retrieving Rows with SELECT for more information about valid SELECT statements.

Destroying Tables with DROP TABLE

The SQL command to permanently destroy a table is DROP TABLE. The following is the syntax for DROP TABLE, where tablename is the table that you wish to destroy:

DROP TABLE tablename

Use caution when dropping a table, as doing so destroys all data associated with the table.

Note

Destroying a table with an implicitly-created index will destroy any associated indices.