CREATE TABLE

Name

CREATE TABLE — Creates a new table.
   CREATE TABLE table (
        column type [DEFAULT value] [CONSTRAINT column_constraint] [, ...]
        [, column ...]
        [, CONSTRAINT table_constraint]
        ) [INHERITS ( inherited_table [, ...] )]
  

Inputs

table

The name of a new table to be created.

column

The name of a column.

type

The type of the column. (Refer to the Postgres User's Guide for further information about data types).

value

A default value for a column. See the DEFAULT clause for more information.

inherited_table

The optional INHERITS clause specifies a collection of table names from which this table automatically inherits all fields. If any inherited field name appears more than once, PostgreSQL reports an error. PostgreSQL automatically allows the created table to inherit functions on tables above it in the inheritance hierarchy. Inheritance of functions is done according to the conventions of the Common Lisp Object System (CLOS).

column_constraint table_constraint

The optional CONSTRAINT clause specifies a list of integrity constraints which new or updated entries must satisfy for an insert or update operation to succeed. Each constraint must evaluate to a boolean expression. Multiple columns may be referenced within a single constraint. See CONSTRAINT clause for more information.

Outputs

status

CREATE

Message returned if table is successfully created.

ERROR

Message returned if table creation failed. This is usually accompanied by some descriptive text, such as:

   amcreate: "table" relation already exists
	   
which occurs at runtime, if the table specified already exists in the database.

Description

CREATE TABLE will enter a new table into the current data base. The table will be "owned" by the user issuing the command.

The new table is created as a heap with no initial data. A table can have no more than 1600 columns (realistically, this is limited by the fact that tuple sizes must be less than 8192 bytes), but this limit may be configured lower at some sites. A table cannot have the same name as a system catalog table.

DEFAULT clause

     DEFAULT value
    

The DEFAULT clause assigns a default data value to a column.

value

The possible values for expression are:

  • a literal value

  • a user function

  • a niladic function

ERROR: DEFAULT: type mismatched

if data type of default value doesn't match the column definition's data type.

The DEFAULT clause assigns a default data value to a column (via a column definition in the CREATE TABLE statement). The data type of a default value must match the column definition's data type.

An INSERT operation that includes a column without a specified default value will assign the NULL value to the column if no explicit data value is provided for it. Default literal means that the default is the specified constant value. Default niladic-function or user-function means that the default is the value of the specified function at the time of the INSERT.

There are two types of niladic functions:

niladic USER

CURRENT_USER / USER

See CURRENT_USER function

SESSION_USER

not yet supported

SYSTEM_USER

not yet supported

niladic datetime

CURRENT_DATE

See CURRENT_DATE function

CURRENT_TIME

See CURRENT_TIME function

CURRENT_TIMESTAMP

See CURRENT_TIMESTAMP function

NOT NULL constraint

    [ CONSTRAINT name ] NOT NULL 
   

The NOT NULL constraint specifies a rule that a column may contain only non-null values.

The NOT NULL constraint is a column constraint.

name

The optional name of a constraint.

Outputs

ERROR: ExecAppend: Fail to add null value in not null attribute "column".

This error occurs at runtime if one tries to insert a null value into a column which has a NOT NULL constraint.

UNIQUE constraint

Table Constraint definition

    [ CONSTRAINT name ] UNIQUE ( column [, ...] )
   

Column Constraint definition

    [ CONSTRAINT name ] UNIQUE
   

Parameters

name

An arbitrary name given to a constraint.

column

A name of a column in a table.

Outputs

ERROR: Cannot insert a duplicate key into a unique index.

This error occurs at runtime if one tries to insert a duplicate value into a column.

Description

The UNIQUE constraint specifies a rule that a group of one or more distinct columns of a table may contain only unique values.

The column definitions of the specified columns do not have to include a NOT NULL constraint to be included in a UNIQUE constraint. Having more than one null value in a column without a NOT NULL constraint, does not violate a UNIQUE constraint.

Each UNIQUE constraint must name a set of columns that is different from the set of columns named by any other UNIQUE or PRIMARY KEY constraint defined for the Table.

NOTE: PostgreSQL automatically creates a unique index for each UNIQUE constraint, to assure data integrity. See CREATE INDEX for more information.

CONSTRAINT clause

Table constraint definition

    [ CONSTRAINT name ] 
    { PRIMARY KEY constraint | 
    UNIQUE constraint | 
    CHECK constraint }  
   

Column constraint definition

    [ CONSTRAINT name ] 
    { NOT NULL constraint |
    PRIMARY KEY constraint | 
    UNIQUE constraint | 
    CHECK constraint }  
   

name

An arbitrary name given to an integrity constraint.

constraint

The definition of the constraint.

A Constraint is a named rule: a SQL object which helps define valid sets of values by putting limits on the results of INSERT, UPDATE or DELETE operations performed on a Base table.

There are two ways to define integrity constraints: Table constraint and Column constraint.

A Table Constraint is an integrity Constraint defined on one or more Columns of a Base table. The four variations of "Table Constraint" are:

PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK

A column constraint is an integrity constraint defined as part of a column definition, and logically becomes a table constraint as soon as it is created. The column constraints available are:

PRIMARY KEY
REFERENCES
UNIQUE
CHECK
NOT NULL

NOTE: PostgreSQL does not yet (at release 6.3.2) support the FOREIGN KEY or REFERENCES integrity constraints, although the parser will accept them. Foreign keys may be partially emulated by triggers (See CREATE TRIGGER statement)

NOTE: PostgreSQL does not yet support either DOMAINs or ASSERTIONs.

The CHECK constraint

   [ CONSTRAINT name ] CHECK ( condition [, ...] ) 
  

Inputs

name

An arbitrary name given to a constraint.

condition

Any valid conditional expression.

Outputs

ERROR: ExecAppend: rejected due to CHECK constraint "table_column".

This error occurs at runtime if one tries to insert an illegal value into a column subject to a CHECK constraint.

Description

The CHECK constraint specifies a rule that a group of one or more columns of a table may contain only those values allowed by the rule.

The CHECK constraint is either a table constraint or a column constraint.

PostgreSQL automatically creates an unique index to assure data integrity. (See CREATE INDEX statement)

The SQL92 CHECK column constraints can only be defined on, and refer to, one column of the table. PostgreSQL does not have this restriction.

BUGS in CHECK constraint

The following CHECK constraints gives a parse error like:

      ERROR:  parser: parse error at or near "opname":
     

CHECK ( column BETWEEN 'A' AND 'Z' )
CHECK ( column IN ('A','Z'))
CHECK ( column NOT LIKE 'A%')

PRIMARY KEY clause

Table constraint definition

    [ CONSTRAINT name ] PRIMARY KEY ( column [, ...] ) 
   

Column constraint definition

    [ CONSTRAINT name ] PRIMARY KEY 
   

Parameters

name

An arbitrary name for the constraint.

column

The name of a column in the table.

Outputs

ERROR: Cannot insert a duplicate key into a unique index.

This occurs at run-time if one tries to insert a duplicate value into a column subject to a PRIMARY KEY constraint.

Description

The PRIMARY KEY constraint specifies a rule that a group of one or more distinct columns of a table may contain only unique, (not duplicates), non-null values. The column definitions of the specified columns do not have to include a NOT NULL constraint to be included in a PRIMARY KEY constraint.

A table's set of valid values may be constrained by only one PRIMARY KEY constraint at a time.

The PRIMARY KEY constraint must name a set of columns that is different from the set of columns named by any UNIQUE constraint defined for the same table.

Notes

PostgreSQL automatically creates an unique index to assure data integrity. (See CREATE INDEX statement)

Usage

Create table films and table distributors

   CREATE TABLE films (
          code      CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
          title     CHARACTER VARYING(40) NOT NULL,
          did       DECIMAL(3) NOT NULL,
          date_prod DATE,
          kind      CHAR(10),
          len       INTERVAL HOUR TO MINUTE
          );
  
   CREATE TABLE distributors (
          did      DECIMAL(03) PRIMARY KEY DEFAULT NEXTVAL('serial'),
          name     VARCHAR(40) NOT NULL CHECK (name <> '')
          );
  

Create a table with a 2-dimensional array

   CREATE TABLE array (
          vector INT[][]
          );
  

Define two NOT NULL column constraints on the table distributors

   CREATE TABLE distributors (
        did      DECIMAL(3) CONSTRAINT no_null NOT NULL,
        name     VARCHAR(40) NOT NULL
        );
  

Define a UNIQUE table constraint for the table films. UNIQUE table constraints can be defined on one or more columns of the table

   CREATE TABLE films (
       code      CHAR(5),
       title     VARCHAR(40),
       did       DECIMAL(03),
       date_prod DATE,
       kind      CHAR(10),
       len       INTERVAL HOUR TO MINUTE,
       CONSTRAINT production UNIQUE(date_prod)
       );
  

Defines a UNIQUE column constraint for the table distributors. UNIQUE column constraints can only be defined on one column of the table (the following two examples are equivalents).

   CREATE TABLE distributors (
        did      DECIMAL(03),
        name     VARCHAR(40) UNIQUE,
        UNIQUE(name)
        );
  
   CREATE TABLE distributors (
        did      DECIMAL(3),
        name     VARCHAR(40) UNIQUE
        );
  

Define a CHECK column constraint.

   CREATE TABLE distributors (
        did      DECIMAL(3) CHECK (did > 100),
        name     VARCHAR(40)
        );
  

Define a CHECK table constraint

   CREATE TABLE distributors (
        did      DECIMAL(3),
        name     VARCHAR(40)
        CONSTRAINT con1 CHECK (did > 100 AND name > '')
        );
  

Define a PRIMARY KEY table constraint for the table films. PRIMARY KEY table constraints can be defined on one or more columns of the table

   CREATE TABLE films (
       code      CHAR(05),
       title     VARCHAR(40),
       did       DECIMAL(03),
       date_prod DATE,
       kind      CHAR(10),
       len       INTERVAL HOUR TO MINUTE,
       CONSTRAINT code_title PRIMARY KEY(code,title)
       );
  

Defines a PRIMARY KEY column constraint for table distributors. PRIMARY KEY column constraints can only be defined on one column of the table (the following two examples are equivalents)

   CREATE TABLE distributors (
        did      DECIMAL(03),
        name     CHAR VARYING(40),
        PRIMARY KEY(did)
        ); 

   CREATE TABLE distributors (
        did      DECIMAL(03) PRIMARY KEY,
        name     VARCHAR(40)
        );
  

To assign a sequence as the default for the column did, and a literal to the column name

       CREATE TABLE distributors (
              did      DECIMAL(3) DEFAULT NEXTVAL('serial'),
              name     VARCHAR(40) DEFAULT 'luso films'
              );
  

Notes

CREATE TABLE/INHERITS is a PostgreSQL language extension.

Compatibility

SQL92

In addition to normal CREATE TABLE, SQL92 also supports a CREATE TEMPORARY TABLE statement.

   CREATE [ {GLOBAL | LOCAL} ] TEMPORARY TABLE table (
        column type [DEFAULT value] [CONSTRAINT column_constraint] [, ...] )
        [CONSTRAINT table_constraint ]
        [ ON COMMIT {DELETE | PRESERVE} ROWS ] 
   

For temporary tables, the CREATE TEMPORARY TABLE statement names a new table and defines the table's columns and constraints.

The optional ON COMMIT clause of CREATE TEMPORARY TABLE specifies whether or not the temporary table should be emptied of rows whenever COMMIT is executed. If the ON COMMIT clause is omitted, the default option, ON COMMIT DELETE ROWS, is assumed.

To create a temporary table:

      CREATE TEMPORARY TABLE actors (
             id      DECIMAL(03),
             name    VARCHAR(40),
             CONSTRAINT actor_id CHECK (id < 150)
             ) ON COMMIT DELETE ROWS
   

TIP: In the current release of PostgreSQL (6.3.2), to create a temporary table you must create and drop the table by explicit commands.

UNIQUE clause

SQL92 specifies some additional capabilities for UNIQUE: Table Constraint definition

      [ CONSTRAINT name ]
      UNIQUE ( column [, ...] )
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

Column Constraint definition

      [ CONSTRAINT name ]
      UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

NOT NULL clause

SQL92 specifies some additional capabilities for NOT NULL:

      [ CONSTRAINT name ] NOT NULL 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

DEFAULT clause

SQL92 specifies some additional capabilities for the DEFAULT clause. A DEFAULT clause is used to set the default value for a column or a domain.

     DEFAULT literal |
              niladic USER function |
              niladic datetime function |
              NULL
    

CONSTRAINT clause

SQL92 specifies some additional capabilities for CONSTRAINTs, it also defines assertions and domain constraints.

An assertion is a special type of integrity constraint and share the same namespace as other constraints. However, an assertion is not necessarily dependent on one particular base table as constraints are, so SQL-92 provides the CREATE ASSERTION statement as an alternate method for defining a constraint:

     CREATE ASSERTION name CHECK ( condition )
    

Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN statements:

Domain constraint:

      [ CONSTRAINT name ] 
          CHECK constraint 
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]
    

Table constraint definition:

      [ CONSTRAINT name ] 
        { PRIMARY KEY constraint | 
          FOREIGN KEY constraint | 
          UNIQUE constraint | 
          CHECK constraint }
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]

Column constraint definition:

     [ CONSTRAINT name ] 
        { NOT NULL constraint |
          PRIMARY KEY constraint | 
          FOREIGN KEY constraint | 
          UNIQUE constraint | 
          CHECK constraint }  
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]
    

A CONSTRAINT definition may contain one deferment attribute clause and/or one initial constraint mode clause, in any order.

NOT DEFERRABLE

means that the Constraint must be checked for violation of its rule after the execution of every SQL statement.

DEFERRABLE

means that checking of the Constraint may be deferred until some later time, but no later than the end of the current transaction.

The constraint mode for every Constraint always has an initial default value which is set for that Constraint at the beginning of a transaction.

INITIALLY IMMEDIATE

means that, as of the start of the transaction, the Constraint must be checked for violation of its rule after the execution of every SQL statement.

INITIALLY DEFERRED

means that, as of the start of the transaction, checking of the Constraint may be deferred until some later time, but no later than the end of the current transaction.

CHECK clause

SQL92 specifies some additional capabilities for CHECK:

A CHECK constraint is either a table constraint, a column constraint or a domain constraint.

table constraint definition:

     [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

column constraint definition:

      [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

domain constraint definition:

     [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

CHECK domain constraints can be defined in either a CREATE DOMAIN statement or an ALTER DOMAIN statement:

     CREATE DOMAIN duration AS SMALLINT 
        CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240)); 

     ALTER DOMAIN cities 
        ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%');
    

PRIMARY KEY clause

SQL92 specifies some additional capabilities for PRIMARY KEY:

Table Constraint definition:

      [ CONSTRAINT name ] 
      PRIMARY KEY ( column [, ...] ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    

Column Constraint definition:

      [ CONSTRAINT name ] 
      PRIMARY KEY 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]