Using Tables

CREATE TABLE syntax

The following is the syntax for CREATE TABLE, which is described in detail in Table 4-1.

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

CREATE 
[ TEMPORARY | TEMP ] 
TABLE

table_name (

{ column_name type 
[ column_constraint ]
| table_constraint }

 [ , ... ] )

[ INHERITS ( 
inherited_table 
[ , ... ] )]

Table 4-1 uses the terms column_constraint and table_constraint to refer to set of potentially complex constraint definitions. The syntax for these various constraints is listed in detail in the section called Constraints in Chapter 5.

Creating an example table

As an example, the syntax to create Book Town's books table is demonstrated in Example 4-6.

booktown=# CREATE TABLE books (
booktown(#        id integer UNIQUE,
booktown(#        title text NOT NULL,
booktown(#        author_id integer,
booktown(#        subject_id integer,
booktown(#        CONSTRAINT books_pkey PRIMARY KEY (id)
booktown(# );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 
'books_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, parenthese, or other special character symbol. Remember that in psql, you may hit the CTRL+C keys at any time to cancel a statement, and clear the buffer.

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_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). 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.

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.

ALTER TABLE syntax

The various forms of syntax for ALTER TABLE are summarized in Table 4-3, and described in detail in the following sections.

ALTER TABLE table_name 
      ADD [ COLUMN ] 
      column_name column_type

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

ALTER TABLE table_name 
      RENAME TO new_table_name

ALTER TABLE table_name 
      RENAME [ COLUMN ] 
      column_name 
      TO new_column_name

ALTER TABLE table_name 
      ADD CONSTRAINT 
      constraint_name 
      constraint_definition

ALTER TABLE table_name 
      OWNER TO new_owner

Adding columns

The columns of a table may be extended by passing the ADD COLUMN clause to the ALTER TABLE command. The following syntax describes adding a column where table_name is the name of the table to modify, column_name is the name of the column to add, and column_type is the data type of the new column:

  ALTER TABLE table_name
        ADD [ COLUMN ] column_name column_type

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

As an example of this function, 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. Such a procedure is demonstrated in Example 4-7.

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_pkey

Example 4-7 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. See the section called Re-structuring Existing Tables for information on how to re-structure 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 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 in the table called table_name:

  ALTER TABLE table_name
        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-8 demonstrates setting and dropping a simple default sequence value on the books table's id column.

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_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_pkey

Re-naming the table

A table may be safely re-named by passing the RENAME clause to the ALTER TABLE command. The following is the syntax to re-name a table where table_name is the name of the table to be renamed, and new_table_name is the desired new name for the table:

ALTER TABLE table_name
      RENAME TO new_table_name

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

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

Re-naming columns

A table's columns may be safely re-named in PostgreSQL without modifying the data which is represented in that table. Re-naming a column can obviously be a dangerous thing to do, however, if existing applications rely on an existing identifier.

The following syntax describes how to re-name a column where table_name is the name of the table being renamed, column_name is the current name of the column, and new_column_name is the intended new name for the column:

  ALTER TABLE table_name
        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 are enough for PostgreSQL to determine that you are requesting a column re-name, and not a table re-name. This is demonstrated in Example 4-10.

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 constraints may be added to an existing table column. The following is the syntax to add a constraint to a table with the name table_name, and a constraint named constraint_name, defined as constraint_definition:

  ALTER TABLE table_name
        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 are the only supported constraint with the ADD CONSTRAINT (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) is demonstrated in Example 4-11.

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

See the section called Constraints in Chapter 5 for more detailed information on 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 keywords (see the section called Privileges in Chapter 8). If ownership must be changed, the OWNER clause may be passed to ALTER TABLE. The syntax to change the ownership of a table from one user to another is:

  ALTER TABLE table_name
        OWNER TO new_owner

The preceding syntax describes how to change ownership of a table, where table_name is the name of the table whose ownership you wish to modify, and new_owner is the valid PostgreSQL username that you wish to transfer ownership of the table to.

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

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

Re-structuring with CREATE TABLE AS

One common technique to re-structure a table is to use the CREATE TABLE command in conjunction with the AS clause, and a valid SQL query, in order to re-structure your existing table into a temporary location, which can then be re-named. This technique 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.

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 called 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.

The following syntax describes this limited version of CREATE TABLE, where table is the name of the new table to be created, the grouped list of column_names are the optional names of the new columns to be created, and query is the valid SELECT statement which selects the data to populate the new table with. The data type is derived from the type of each column selected by query.

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

The convenient advantage to this technique is that you are able to create the new table and populate it in one SQL command. However, the most notable limitation of this technique is that there is no comprehensive way to set constraints on the newly created table; the only constraint which may be added to the table after is has been created is the foreign key constraint. Once the new table has been created, the old one can be re-named (or destroyed), and the new one can be re-named to the name of the original table. This technique is demonstrated in Example 4-13, but you may wish to read the next section if you require a table with other constraints.

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_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 |

Re-structuring with CREATE TABLE and INSERT INTO

If you require a more specifically defined table than that created by the 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. This is achieved 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.

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 called Inserting values from other tables with SELECT for more information on using the INSERT INTO command with a SELECT statement, and the section called Retrieving Rows with SELECT for more information on valid SELECT statements.