CREATE TABLE - Creates a new table


SYNOPSIS

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

       where column_constraint can be:
       [ CONSTRAINT constraint_name ]
       { NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition) |
         REFERENCES table [ ( column ) ] [ MATCH FULL | MATCH PARTIAL ]
          [ ON DELETE action ] [ ON UPDATE action ]
          [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
       }

       and table_constraint can be:
       [ CONSTRAINT constraint_name ]
       { UNIQUE ( column_name [, ... ] ) |
         PRIMARY KEY ( column_name [, ... ] ) |
         CHECK ( condition ) |
         FOREIGN KEY ( column_name [, ... ] ) REFERENCES table [ ( column [, ... ] ) ]
          [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
          [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
       }

   INPUTS
       TEMPORARY or TEMP
              If  specified,  the  table is created only for this
              session, and is automatically  dropped  on  session
              exit.  Existing permanent tables with the same name
              are not visible (in this session) while the  tempo­
              rary table exists.  Any indexes created on a tempo­
              rary table are automatically temporary as well.

       table_name
              The name of the new table to be created.

       column_name
              The name of a column  to  be  created  in  the  new
              table.

       type   The  type  of  the  column.  This may include array
              specifiers.  Refer to the PostgreSQL  User's  Guide
              for   further  information  about  data  types  and
              arrays.

       inherited_table
              The optional INHERITS clause specifies  a  list  of
              table  names  from  which  this table automatically
              inherits all fields.  If any inherited  field  name

              Postgres automatically allows the created table  to
              inherit functions on tables above it in the inheri­
              tance hierarchy.

       constraint_name
              An optional name for a column or table  constraint.
              If not specified, the system generates a name.

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

       condition
              CHECK  clauses  specify  integrity  constraints  or
              tests which new or updated rows must satisfy for an
              insert or update operation to  succeed.  Each  con­
              straint  must  be an expression producing a boolean
              result.  A condition appearing within a column def­
              inition  should reference that column's value only,
              while a condition appearing as a  table  constraint
              may reference multiple columns.

       table  The name of an existing table to be referenced by a
              foreign key constraint.

       column The name of a column in an  existing  table  to  be
              referenced  by  a  foreign  key  constraint. If not
              specified, the primary key of the existing table is
              assumed.

       action A keyword indicating the action to take when a for­
              eign key constraint is violated.

   OUTPUTS
       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:  ERROR: Relation 'table' already exists , which
              occurs at runtime if the  table  specified  already
              exists in the database.


DESCRIPTION

       CREATE  TABLE will enter a new, initially empty table into
       the current database. The table will  be  "owned"  by  the
       user issuing the command.

       Each type may be a simple type, a complex type (set) or an
       array type.  Each attribute may be specified  to  be  non-
       null  and  each may have a default value, specified by the
       DEFAULT Clause [create_table(l)].

              attribute are not enforced. This will likely change
              in a future release.

       CREATE TABLE also automatically creates a data  type  that
       represents  the  tuple type (structure type) corresponding
       to one row of the table. Therefore, tables can't have  the
       same name as any existing datatype.

       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, Postgres reports an  error.  Postgres  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 Com­
       mon Lisp Object System (CLOS).

       A table can have no more than 1600 columns  (in  practice,
       the  effective limit is lower because of tuple-length con­
       straints).  A table cannot have the same name as a  system
       catalog table.


DEFAULT CLAUSE

       DEFAULT value

       The  DEFAULT  clause  assigns a default data value for the
       column whose column  definition  it  appears  within.  The
       value  is  any  variable-free  expression  (note that sub-
       selects and cross-references to other columns in the  cur­
       rent table are not supported).  The data type of a default
       value must match the column definition's data type.

       The DEFAULT expression will be used in any  INSERT  opera­
       tion  that  does  not  specify  a value for the column. If
       there is no DEFAULT clause, then the default is NULL.

   USAGE
       CREATE TABLE distributors (
           name     VARCHAR(40) DEFAULT 'luso films',
           did      INTEGER  DEFAULT NEXTVAL('distributors_serial'),
           modtime  TIMESTAMP DEFAULT now()
       );

       The above assigns a literal constant default value for the
       column  name, and arranges for the default value of column
       did to be generated by  selecting  the  next  value  of  a
       sequence  object. The default value of modtime will be the
       time at which the row is inserted.


           modtime  TIMESTAMP DEFAULT 'now'

       would produce a result that is probably not  the  intended
       one: the string 'now' will be coerced to a timestamp value
       immediately, and so the  default  value  of  modtime  will
       always  be  the time of table creation. This difficulty is
       avoided by specifying the  default  value  as  a  function
       call.


COLUMN CONSTRAINTS

       [ CONSTRAINT constraint_name ] {
           NULL | NOT NULL | UNIQUE | PRIMARY KEY | CHECK condition |
           REFERENCES reftable [ ( refcolumn ) ]
            [ MATCH matchtype ]
            [ ON DELETE action ]
            [ ON UPDATE action ]
            [ [ NOT ] DEFERRABLE ]
            [ INITIALLY checktime ] }

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

       NULL   The  column is allowed to contain NULL values. This
              is the default.

       NOT NULL
              The column is not allowed to contain  NULL  values.
              This  is  equivalent to the column constraint CHECK
              (column NOT NULL).

       UNIQUE The column must have  unique  values.  In  Postgres
              this  is enforced by automatic creation of a unique
              index on the column.

       PRIMARY KEY
              This column is a primary key,  which  implies  that
              other  tables  may  rely on this column as a unique
              identifier for rows. Both UNIQUE and NOT  NULL  are
              implied  by  PRIMARY  KEY. See PRIMARY KEY for more
              information.

       condition
              An arbitrary boolean-valued constraint condition.

   DESCRIPTION
       The optional constraint  clauses  specify  constraints  or
       tests which new or updated rows must satisfy for an insert


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

       There  are two ways to define integrity constraints: table
       constraints, covered later, and column  constraints,  cov­
       ered here.

       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 con­
       straints available are:

       PRIMARY KEY

       REFERENCES

       UNIQUE

       CHECK

       NOT NULL

   NOT NULL CONSTRAINT
       [ CONSTRAINT name ] NOT NULL

       The NOT NULL constraint specifies a rule that a column may
       contain only non-null values.  This is a column constraint
       only, and not allowed as a table constraint.

   OUTPUTS
       status

              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.

   DESCRIPTION
   USAGE
       Define two NOT NULL column constraints on the  table  dis­
       tributors, one of which is explicitly given a name:

       CREATE TABLE distributors (
           did      DECIMAL(3) CONSTRAINT no_null NOT NULL,

       );

   UNIQUE CONSTRAINT
       [ CONSTRAINT constraint_name ] UNIQUE

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

   OUTPUTS
       status

              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.  (This deviates from the SQL92 defini­
       tion, but is a more sensible convention. See  the  section
       on compatibility for more details.)

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

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

   USAGE
       Defines a UNIQUE constraint for the name column:

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

       constraint:

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

   THE CHECK CONSTRAINT
       [ CONSTRAINT constraint_name ] CHECK ( condition )

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

       condition
              Any valid conditional expression  evaluating  to  a
              boolean result.

   OUTPUTS
       status

              ERROR:   ExecAppend:  rejected  due  to  CHECK
                     constraint  "con­ straint_name".
                     This error occurs at runtime if one tries to
                     insert an illegal value into a  column  sub­
                     ject to a CHECK constraint.

   DESCRIPTION
       The  CHECK  constraint  specifies a generic restriction on
       allowed values within a column. The  CHECK  constraint  is
       also allowed as a table constraint.

       CHECK specifies a general boolean expression involving one
       or more columns of a table. A new row will be rejected  if
       the  boolean expression evaluates to FALSE when applied to
       the row's values.

       Currently, CHECK expressions  cannot  contain  sub-selects
       nor  refer  to  variables other than fields of the current
       row.

       The SQL92 standard says that CHECK column constraints  may
       only  refer  to the column they apply to; only CHECK table
       constraints may refer to multiple columns.  Postgres  does
       not  enforce this restriction.  It treats column and table


   PRIMARY KEY CONSTRAINT
       [ CONSTRAINT constraint_name ] PRIMARY KEY

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

   OUTPUTS
       ERROR: Cannot insert a duplicate key into a unique index.
              This occurs at runtime if one  tries  to  insert  a
              duplicate  value into a column subject to a PRIMARY
              KEY constraint.

   DESCRIPTION
       The PRIMARY KEY column constraint specifies that a  column
       of  a  table may contain only unique (non-duplicate), non-
       NULL values. The definition of the specified  column  does
       not  have to include an explicit NOT NULL constraint to be
       included in a PRIMARY KEY constraint.

       Only one PRIMARY KEY can be specified for a table, whether
       as a column constraint or a table constraint.

   NOTES
       Postgres  automatically  creates  a unique index to assure
       data integrity (see CREATE INDEX statement).

       The PRIMARY KEY constraint should name a  set  of  columns
       that  is different from other sets of columns named by any
       UNIQUE constraint defined for the  same  table,  since  it
       will  result  in  duplication  of  equivalent  indexes and
       unproductive additional runtime overhead.  However,  Post­
       gres does not specifically disallow this.

   REFERENCES CONSTRAINT
       [ CONSTRAINT constraint_name ] REFERENCES reftable [ ( refcolumn ) ]
           [ MATCH matchtype ]
           [ ON DELETE action ]
           [ ON UPDATE action ]
           [ [ NOT ] DEFERRABLE ]
           [ INITIALLY checktime ]

       The  REFERENCES  constraint specifies a rule that a column
       value is checked against the  values  of  another  column.
       REFERENCES  can also be specified as part of a FOREIGN KEY
       table constraint.

       constraint_name
              An arbitrary name given to a constraint clause.

       reftable
              The table that contains the data to check  against.

       refcolumn
              The  column  in reftable to check the data against.
              If this is not specified, the PRIMARY  KEY  of  the
              reftable is used.

       MATCH matchtype
              There are three match types: MATCH FULL, MATCH PAR­
              TIAL, and a default match type if  none  is  speci­
              fied.  MATCH  FULL  will  not allow one column of a
              multi-column foreign key to be NULL unless all for­
              eign  key  columns are NULL. The default MATCH type
              allows some foreign key columns to  be  NULL  while
              other  parts of the foreign key are not NULL. MATCH
              PARTIAL is currently not supported.

       ON DELETE action
              The action to do when a referenced row in the  ref­
              erenced  table is being deleted. There are the fol­
              lowing actions.

              NO ACTION
                     Produce error if foreign key violated.  This
                     is the default.

              RESTRICT
                     Same as NO ACTION.

              CASCADE
                     Delete any rows referencing the deleted row.

              SET NULL
                     Set the referencing column values to NULL.

              SET DEFAULT
                     Set the referencing column values  to  their
                     default value.

       ON UPDATE action
              The  action  to  do when a referenced column in the
              referenced table is being updated to a  new  value.
              If the row is updated, but the referenced column is
              not changed, no action is done.  There are the fol­
              lowing actions.

              NO ACTION
                     Produce  error if foreign key violated. This


              RESTRICT
                     Same as NO ACTION.

              CASCADE
                     Update the value of the  referencing  column
                     to the new value of the referenced column.

              SET NULL
                     Set the referencing column values to NULL.

              SET DEFAULT
                     Set  the  referencing column values to their
                     default value.

       [ NOT ] DEFERRABLE
              This  controls  whether  the  constraint   can   be
              deferred   to   the  end  of  the  transaction.  If
              DEFERRABLE, SET CONSTRAINTS ALL DEFERRED will cause
              the  foreign  key  to be checked only at the end of
              the transaction. NOT DEFERRABLE is the default.

       INITIALLY checktime
              checktime has two possible values which specify the
              default time to check the constraint.

              DEFERRED
                     Check  constraint  only  at  the  end of the
                     transaction.

              IMMEDIATE
                     Check constraint after each statement.  This
                     is the default.

   OUTPUTS
       status

              ERROR: name referential integrity violation - key
                     referenced from
                     This error occurs at runtime if one tries to
                     insert a value into a column which does  not
                     have  a  matching  column  in the referenced
                     table.

   DESCRIPTION
       The REFERENCES column constraint specifies that  a  column
       of  a  table  must only contain values which match against
       values in a referenced column of a referenced table.

       of  the  referenced  table and referenced column using the
       given match type.  In addition, when the referenced column
       data is changed, actions are run upon this column's match­
       ing data.

   NOTES
       Currently Postgres only supports MATCH FULL and a  default
       match  type.  In addition, the referenced columns are sup­
       posed to be the columns of a UNIQUE constraint in the ref­
       erenced table, however Postgres does not enforce this.


TABLE CONSTRAINTS

       [ CONSTRAINT name ] { PRIMARY KEY |  UNIQUE } ( column [, ... ] )
       [ CONSTRAINT name ] CHECK ( constraint )
       [ CONSTRAINT name ] FOREIGN KEY ( column [, ... ] )
                            REFERENCES reftable [ ( refcolumn [, ... ] ) ]
                             [ MATCH matchtype ]
                             [ ON DELETE action ]
                             [ ON UPDATE action ]
                             [ [ NOT ] DEFERRABLE ]
                             [ INITIALLY checktime ]

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

       column [, ... ]
              The  column  name(s)  for  which to define a unique
              index and, for PRIMARY KEY, a NOT NULL  constraint.

       CHECK ( constraint )
              A  boolean  expression  to be evaluated as the con­
              straint.

   OUTPUTS
       The possible outputs for the table constraint  clause  are
       the  same  as for the corresponding portions of the column
       constraint clause.

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

       UNIQUE

       CHECK

       PRIMARY KEY


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

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

       column A name of a column in a table.

   OUTPUTS
       status

              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 behavior of the UNIQUE table constraint
       is the same as that for column constraints, with the addi­
       tional capability to span multiple columns.

       See the section on the UNIQUE column constraint  for  more
       details.

   USAGE
       Prevent duplicate rows in the table distributors:

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

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

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

              The names of one or more columns in the table.

   OUTPUTS
       status

              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 sub­
                     ject 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 (nonduplicate), 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.

       The  PRIMARY  KEY  table constraint is similar to that for
       column constraints,  with  the  additional  capability  of
       encompassing multiple columns.

       Refer  to the section on the PRIMARY KEY column constraint
       for more information.

   REFERENCES CONSTRAINT
       [ CONSTRAINT constraint_name ] FOREIGN KEY ( column [, ... ] )
           REFERENCES reftable [ ( refcolumn [, ... ] ) ]
           [ MATCH matchtype ]
           [ ON DELETE action ]
           [ ON UPDATE action ]
           [ [ NOT ] DEFERRABLE ]
           [ INITIALLY checktime ]

       The REFERENCES constraint specifies a rule that  a  column
       value  or set of column values is checked against the val­
       ues in another table.

   INPUTS
       constraint_name
              An arbitrary name given to a constraint clause.

       column [, ... ]
              The names of one or more columns in the table.

       reftable
              The table that contains the data to check  against.

              One  or  more  columns in the reftable to check the
              data against. If this is not specified, the PRIMARY
              KEY of the reftable is used.

       MATCH matchtype
              There are three match types: MATCH FULL, MATCH PAR­
              TIAL, and a default match type if  none  is  speci­
              fied.  MATCH  FULL  will  not allow one column of a
              multi-column foreign key to be NULL unless all for­
              eign  key  columns are NULL. The default MATCH type
              allows some foreign key columns to  be  NULL  while
              other  parts of the foreign key are not NULL. MATCH
              PARTIAL is currently not supported.

       ON DELETE action
              The action to do when a referenced row in the  ref­
              erenced  table is being deleted. There are the fol­
              lowing actions.

              NO ACTION
                     Produce error if foreign key violated.  This
                     is the default.

              RESTRICT
                     Same as NO ACTION.

              CASCADE
                     Delete any rows referencing the deleted row.

              SET NULL
                     Set the referencing column values to NULL.

              SET DEFAULT
                     Set the referencing column values  to  their
                     default value.

       ON UPDATE action
              The  action  to  do when a referenced column in the
              referenced table is being updated to a  new  value.
              If the row is updated, but the referenced column is
              not changed, no action is done.  There are the fol­
              lowing actions.

              NO ACTION
                     Produce  error if foreign key violated. This
                     is the default.

              RESTRICT
                     Disallow update of row being referenced.

              CASCADE
                     Update the value of the  referencing  column


              SET NULL
                     Set the referencing column values to NULL.

              SET DEFAULT
                     Set  the  referencing column values to their
                     default value.

       [ NOT ] DEFERRABLE
              This  controls  whether  the  constraint   can   be
              deferred   to   the  end  of  the  transaction.  If
              DEFERRABLE, SET CONSTRAINTS ALL DEFERRED will cause
              the  foreign  key  to be checked only at the end of
              the transaction. NOT DEFERRABLE is the default.

       INITIALLY checktime
              checktime has two possible values which specify the
              default time to check the constraint.

              IMMEDIATE
                     Check  constraint after each statement. This
                     is the default.

              DEFERRED
                     Check constraint only  at  the  end  of  the
                     transaction.

   OUTPUTS
       status

              ERROR: name referential integrity violation - key
                     referenced from
                     This error occurs at runtime if one tries to
                     insert a value into a column which does  not
                     have  a  matching  column  in the referenced
                     table.

   DESCRIPTION
       The FOREIGN KEY constraint specifies a rule that  a  group
       of one or more distinct columns of a table is related to a
       group of distinct columns in the referenced table.

       The FOREIGN KEY table constraint is similar  to  that  for
       column  constraints,  with  the  additional  capability of
       encompassing multiple columns.

       Refer to the section on the FOREIGN KEY column  constraint
       for more information.

       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(3) 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  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(3),
           date_prod DATE,
           kind      CHAR(10),
           len       INTERVAL HOUR TO MINUTE,
           CONSTRAINT production UNIQUE(date_prod)
       );

       Define a CHECK column constraint:

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


       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(5),
           title     VARCHAR(40),
           did       DECIMAL(3),
           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 distrib­
       utors.  PRIMARY KEY column constraints can only be defined
       on one column of the table (the following two examples are
       equivalent):

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

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


COMPATIBILITY

   SQL92
       In addition to the locally visible temporary table,  SQL92
       also  defines  a  CREATE GLOBAL TEMPORARY TABLE statement,
       and optionally an ON COMMIT clause:

       CREATE GLOBAL TEMPORARY TABLE table ( column type [
           DEFAULT value ] [ CONSTRAINT column_constraint ] [, ... ] )


       For temporary tables, the CREATE  GLOBAL  TEMPORARY  TABLE
       statement  names  a new table visible to other clients 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, SQL92 specifies that the default
       is ON COMMIT DELETE ROWS. However, Postgres'  behavior  is
       always like ON COMMIT PRESERVE ROWS.

   UNIQUE CLAUSE
       SQL92 specifies some additional capabilities for UNIQUE:

       Table Constraint definition:

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

       Column Constraint definition:

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

   NULL CLAUSE
       The  NULL  "constraint"  (actually  a non-constraint) is a
       Postgres extension to SQL92 that is included for  symmetry
       with  the NOT NULL clause (and for compatibility with some
       other RDBMSes). Since it is the default  for  any  column,
       its presence is simply noise.

       [ CONSTRAINT constraint_name ] NULL

   NOT NULL CLAUSE
       SQL92 specifies some additional capabilities for NOT NULL:

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

       SQL92 specifies  some  additional  capabilities  for  con­
       straints,  and  also  defines  assertions  and domain con­
       straints.

              Note: Postgres does not yet support either  domains
              or assertions.

       An assertion is a special type of integrity constraint and
       shares the same namespace as other  constraints.  However,
       an  assertion is not necessarily dependent on one particu­
       lar table as constraints are, so SQL-92 provides the  CRE­
       ATE  ASSERTION statement as an alternate method for defin­
       ing a constraint:

       CREATE ASSERTION name CHECK ( condition )

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

       Domain constraint:

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

       Table constraint definition:

       [ CONSTRAINT constraint_name ] { PRIMARY KEY ( column, ... ) | FOREIGN KEY constraint | UNIQUE constraint | CHECK constraint }
           [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
           [ [ NOT ] DEFERRABLE ]

       Column constraint definition:

       [ CONSTRAINT constraint_name ] { NOT NULL | PRIMARY KEY | FOREIGN KEY constraint | UNIQUE | 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
              The  constraint  must be checked at the end of each
              statement.  SET CONSTRAINTS ALL DEFERRED will  have


       DEFERRABLE
              This   controls   whether  the  constraint  can  be
              deferred to the end of the transaction. If SET CON­
              STRAINTS  ALL DEFERRED is used or the constraint is
              set to INITIALLY DEFERRED, this will cause the for­
              eign  key  to  be  checked  only  at the end of the
              transaction.

              Note: SET CONSTRAINTS changes the foreign key  con­
              straint mode only for the current transaction.

       INITIALLY IMMEDIATE
              Check  constraint after each statement. This is the
              default.

       INITIALLY DEFERRED
              Check constraint only at the end  of  the  transac­
              tion.

   CHECK CLAUSE
       SQL92  specifies some additional capabilities for CHECK in
       either table or column constraints.

       table constraint definition:

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

       column constraint definition:

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

   PRIMARY KEY CLAUSE
       SQL92 specifies some additional capabilities  for  PRIMARY
       KEY:

       Table Constraint definition:

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


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

   INHERITANCE
       Multiple inheritance via the INHERITS clause is a Postgres
       language extension.  SQL99 (but not SQL92) defines  single
       inheritance  using a different syntax and different seman­
       tics. SQL99-style inheritance  is  not  yet  supported  by
       Postgres.


Man(1) output converted with man2html