Databases - Practical PostgreSQL
Previous Page	Home	Next Page

CREATE TABLE

Name

CREATE TABLE -- Creates a new table.

Synopsis

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


column_constraint
 ::=
  [ CONSTRAINT 
column_constraint_name
 ]
  { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
    DEFAULT 
default_value
 |
    CHECK (
condition
 |
    REFERENCES 
foreign_table
 [ ( 
foreign_column
 ) ]
      [ MATCH FULL | MATCH PARTIAL ]
      [ ON DELETE 
action
 ]
      [ ON UPDATE 
action
 ]
      [ DEFERRABLE | NOT DEFERRABLE ]
      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  }


table constraint
 ::=
  [ CONSTRAINT 
table_constraint_name
 ]
  { UNIQUE ( 
column_name
 [, ... ] ) |
    PRIMARY KEY ( 
column_name
 [, ... ] ) |
    CHECK ( 
condition
 ) |
    FOREIGN KEY ( 
column_name
 [, ... ] )
      REFERENCES 
foreign_table

      [ ( 
foreign_column
 [, ... ] ) ]
      [ MATCH FULL | MATCH PARTIAL ]
      [ ON DELETE 
action
 ]
      [ ON UPDATE 
action
 ]
      [ DEFERRABLE | NOT DEFERRABLE ]
      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  }


action
 ::= { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }

Parameters

TEMPORARY | TEMP

The keyword which defines a table as having a temporary lifespan. Such a table will be destroyed after the user's session has ended. Any table-related constructions (such as indices and constraints) will also be destroyed with the table at the end of the session.

If a temporary table is given the same name as an existing permanent table, only the temporary table will be accessible by the session which created it. This will cause problems, since it will implicitly take precedence over the permanent table within the current session until it is destroyed.

table_name

The name of the table you are creating.

column_name

The name of a column within the new table. Multiple column definitions are specified within parentheses, separated by commas.

type

The type of a specified column, immediately following a column name. This can be a standard type or an array of a standard type.

column_constraint

A complete constraint definition for a column. Here are the parameters available for a column constraint:

column_constraint_name

The optional name for a constraint clause.

NULL

The clause used to explicitly allow the column to contain NULL values. This option is set by default.

NOT NULL

The clause used to forbid the use of a NULL value for this column. You can accomplish this by using the CHECK ( column NOT NULL) column constraint.

UNIQUE

The clause used to force all rows within a column to have unique values (unique within the table). This is enforced by the creation of a unique index on the column.

PRIMARY KEY

The clause used to set a column as a primary key for the table. Other tables rely on primary keys to act as the identifying column for each row. A primary key is effectively the same as a column created with the UNIQUE and NOT NULL clauses.

DEFAULT

The clause used to set a default value for a column. Such a value is used if an input value is not provided for the column by an INSERT statement. Without an explicit default_value, a column defaults to contain NULL.

CHECK

The clause used to have values checked against a specified condition. If the condition yields false on an INSERT or UPDATE, the statement will fail.

condition

An arbitrary conditional expression yielding a Boolean value, following the CHECK clause.

REFERENCES

The clause used to verify column values against the values of a column in another table. (See Chapter 7 for more on this creating and using this constraint.)

foreign_table

The name of a table you wish to be referenced by a foreign key constraint.

foreign_column

The name of a column in another table which you are referencing in a foreign key constraint. The column must reside within an existing table. If no column name is given, the database will use the referenced table's primary key is used.

MATCH FULL | MATCH PARTIAL

The MATCH clause affects what kind of NULL and non-NULL values are allowed to be mixed on insertion into a table whose foreign key references multiple columns . The MATCH clause is therefore only practically applicable to table constraints, though the syntax is technically valid in a column constraint as well.

MATCH FULL disallows insertion of row data whose columns contain NULL values unless all referenced columns are NULL. MATCH PARTIAL is not supported as of PostgreSQL 7.1.x. Not specifying either clause allows NULL columns to satisfy the constraint.

ON DELETE

The ON DELETE clause indicates that when a DELETE is executed on a referenced row in the referenced table, one of the following actions will be executed upon the constrained column, as specified by action:

NO ACTION: The NO ACTION clause produces an error if the reference is violated. This is the default if action is not specified.
RESTRICT: The RESTRICT keyword is identical to NO ACTION.
CASCADE: The CASCADE keyword removes all rows which reference the deleted row. Exercise caution with this action.
SET NULL: The SET NULL clause assigns a NULL value to all referenced column values.
SET DEFAULT: The SET DEFAULT clause sets all referenced columns to their default values.

Note that specifying CASCADE as the ON UPDATE action updates all of the rows which reference the updated row with the new value (rather than deleting them, as would be the case with ON DELETE CASCADE).

ON UPDATE

The ON DELETE clause indicates that when an UPDATE statement is performed on a referenced row in the referenced table, the same actions are available as with the ON DELETE clause. The default action is also NO ACTION.

Specifying CASCADE as the ON UPDATE action updates all of the rows which reference the updated row with the new value (rather than deleting them, as would be the case with ON DELETE CASCADE).

DEFERRABLE | NOT DEFERRABLE

The DEFERRABLE clause gives you the option of postponing enforcement of the constraint to the end of a transaction rather than having it enforced at the end of each statement. Use the INITIALLY clause to specify the initial point at which the constraint will be enforced.

The NOT DEFERRABLE clause indicates the enforcement of the constraint must always be done immediately as each statement is executed. This is the default.

INITIALLY DEFERRED | INITIALLY IMMEDIATE

The INITIALLY DEFERRED clause postpones constraint enforcement until the end of the transaction, whereas INITIALLY IMMEDIATE causes constraint checking to be performed after each statement. The INITIALLY IMMEDIATE clause is the default.

table_constraint

A complete table constraint definition for the table being created. A table constraint can affect multiple columns, whereas a column constraint only creates a constraint for a single column. Here are the parameters available for a table constraint:

table_constraint_name

The optional name for the constraint to be created.

column_name [, ...]

The name of the column (or comma-delimited list of columns) to which the table constraint applies.

PRIMARY KEY | UNIQUE

The table constraint keywords that apply an implicit index. Use the UNIQUE keyword to have the specified column's value checked for duplicate values. Any attempt to insert new rows that do not contain a unique value for the specified column (or columns) will fail if this constraint is used.

Use the PRIMARY KEY keywords to both check for duplicate values, and to disallow NULL values on the specified column, or columns.

CHECK ( condition )

The conditional CHECK constraint keyword. Use this keyword to check a value against the evaluated boolean condition before a new row is inserted; if the check fails (i.e., condition returned false), the row is not added.

FOREIGN KEY

The FOREIGN KEY constraint keyword. Use this keyword to identify a column in another table that will be referenced as a foreign key relation (see Chapter 7 for more on this constraint). The remainder of this clause is identical to the REFERENCES clause of a column constraint.

inherited_table

The name of a table from which the new table should inherit columns. If there are any column names inherited that match column names you've already specified as columns for the new table, PostgreSQL will display an error and terminate execution of the command.

Results

CREATE: The message returned when a table is successfully created.
ERROR: Relation ' table_name ' already exists: The error returned if a table named table_name already exists.
ERROR: CREATE TABLE: attribute " b " duplicated: The error returned a column name is listed twice.
ERROR: Unable to locate type name ' type ' in catalog: The error returned if a specified column type does not exist.
ERROR: Illegal class name ' table_name ': The error returned if table_name begins with pg_.

Description

Use the CREATE TABLE command to add a new table to the database to which you are connected. After it is created, the new table will be completely empty, and its ownership will be set to the user who issued the CREATE TABLE command.

You must supply a name and data type for each column of which the new table will be comprised (except for inherited columns, for which this data will be derived from the parent table). The name supplied may be up to 31 characters in length, and will be folded to lowercase unless placed within double quotes. The data type can be a standard type (e.g., int4, char), or an array type (a standard type, followed by square brackets, such as float4[]).

You may set a variety of constraints on a column, such as the NOT NULL clause, which disallows NULL values from being inserted into the column.

Warning

Tables cannot have the same name as existing data types; nor can they have the same names as system catalog tables, or even be prefixed with pg_, which is the reserved system table prefix.

A table can have a maximum of about 1,600 columns. Due to tuple-length issues, this number is lower in practice.

For more information about creating tables, see Chapter 4. For more information about column and table constraints in general, see Chapter 7.

Examples

The following example creates a table called shipments. It places the NOT NULL constraint and DEFAULT constraints on its id column:

booktown=# 
CREATE TABLE shipments (

booktown(# 
       id integer NOT NULL DEFAULT nextval('shipments_ship_id_seq'),

booktown(# 
       customer_id integer,

booktown(# 
       isbn text,

booktown(# 
       ship_date timestamp);

CREATE

Databases - Practical PostgreSQL
Previous Page	Home	Next Page