path: root/doc/src/sgml/ref/create_table.sgml

<REFENTRY ID="SQL-CREATETABLE-1">
 <REFMETA>
  <REFENTRYTITLE>
   CREATE TABLE
  </REFENTRYTITLE>
  <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
 </REFMETA>
 <REFNAMEDIV>
  <REFNAME>
   CREATE TABLE
  </REFNAME>
  <REFPURPOSE>
   Creates a new table
  </REFPURPOSE>
  
 <REFSYNOPSISDIV>
  <REFSYNOPSISDIVINFO>
   <DATE>1998-04-15</DATE>
  </REFSYNOPSISDIVINFO>
  <SYNOPSIS>
   CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> (
        <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE> [DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>] [CONSTRAINT <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE>] [, ...]
        [, <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> ...]
        [, CONSTRAINT <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE>]
        ) [INHERITS ( <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> [, ...] )]
  </SYNOPSIS>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Inputs
   </TITLE>
   <PARA>
   </PARA>
   
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The name of a new table to be created.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The name of a column.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The type of the column.
       (Refer to the <ProductName>Postgres</ProductName> User's Guide for
       further information about data types).
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       A default value for a column.
       See the DEFAULT clause for more information.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       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).
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE>
      <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       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.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
   
   
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-2">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
      <ReturnValue>status</ReturnValue>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>CREATE</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Message returned if table is successfully created.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Message returned if table creation failed.
	   This is usually accompanied by some descriptive text, such as:
	   <ProgramListing>
   amcreate: "<replaceable class="parameter">table</replaceable>" relation already exists
	   </ProgramListing>
	   which occurs at runtime, if the table specified already exists
	   in the database.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </VARIABLELIST>
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
  </REFSECT2>
 </REFSYNOPSISDIV>
 
 <REFSECT1 ID="R1-SQL-CREATETABLE-1">
  <REFSECT1INFO>
   <DATE>1998-04-15</DATE>
  </REFSECT1INFO>
  <TITLE>
   Description
  </TITLE>
  <PARA>
   CREATE TABLE will enter a new table into the current data
   base. The table will be "owned" by the user issuing the
   command.
   
  <PARA>
   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.
  </PARA>
  
  <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    DEFAULT clause
   </TITLE>
   <PARA>
    <SYNOPSIS>
     DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
    </SYNOPSIS>
   <PARA>
    The DEFAULT clause assigns a default data value to a column.
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue><replaceable class="parameter">value</replaceable></ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The possible values for expression are:
	   <itemizedlist>
	    <listitem>
	     <simpara>
	      a literal value
	     </simpara>
	    </listitem>
	    <listitem>
	     <simpara>
	      a user function
	     </simpara>
	    </listitem>
	    <listitem>
	     <simpara>
	      a niladic function
	     </simpara>
	    </listitem>
	   </itemizedlist>
	  </para>
	 </listitem>
	</VARLISTENTRY>
	
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR:  DEFAULT: type mismatched</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   if data type of default value doesn't match the
	   column definition's data type.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
      <PARA>
       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.
      </PARA>
      <PARA>
       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 <replaceable class="parameter">literal</replaceable> means
       that the default is the specified constant value.
       Default <replaceable class="parameter">niladic-function</replaceable>
       or <replaceable class="parameter">user-function</replaceable> means
       that the default
       is the value of the specified function at the time of the INSERT.
      </PARA>
      <PARA>
       There are two types of niladic functions:<variablelist>
	<varlistentry>
	 <term>niladic USER</term>
	 <listitem>
	  <variablelist>
	   <varlistentry>
	    <term>CURRENT_USER / USER</term>
	    <listitem>
	     <simpara>See CURRENT_USER function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>SESSION_USER</term>
	    <listitem>
	     <simpara>not yet supported</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>SYSTEM_USER</term>
	    <listitem>
	     <simpara>not yet supported</simpara>
	    </listitem>
	   </varlistentry>
	  </variablelist>
	 </listitem>
	</varlistentry>
	<varlistentry>
	 <term>niladic datetime</term>
	 <listitem>
	  <variablelist>
	   <varlistentry>
	    <term> CURRENT_DATE</term>
	    <listitem>
	     <simpara>See CURRENT_DATE function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>CURRENT_TIME</term>
	    <listitem>
	     <simpara>See CURRENT_TIME function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>CURRENT_TIMESTAMP</term>
	    <listitem>
	     <simpara>See CURRENT_TIMESTAMP function</simpara>
	    </listitem>
	   </varlistentry>
	  </variablelist>
	 </listitem>
	</varlistentry>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-NOTNULL-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    NOT NULL constraint
   </TITLE>
   <SYNOPSIS>
    [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] NOT NULL 
   </SYNOPSIS>
   <PARA>
    The NOT NULL constraint specifies a rule that a column may
    contain only non-null values. 
   </PARA>
   <PARA>
    The NOT NULL constraint is a column constraint. 
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The optional name of a constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
  
  <REFSECT3 ID="R3-SQL-NOTNULL-1">
   <REFSECT3INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT3INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR:  ExecAppend: Fail to add null value in not
	   null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   This error occurs at runtime if one tries to insert a null value
	   into a column which has a NOT NULL constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST> 
  </REFSECT3>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    UNIQUE constraint
   </TITLE>
   <para>
    Table Constraint definition
   </para>
   <synopsis>
    [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] )
   </SYNOPSIS>
   <para>
    Column Constraint definition
   </para>
   <synopsis>
    [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE
   </SYNOPSIS>
   <refsect3>
    <title>Parameters</title>
    <variablelist>
     <varlistentry>
      <term>
       <replaceable class="parameter">name</replaceable>
      </term>
      <listitem>
       <para>
	An arbitrary name given to a constraint.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>
       <replaceable class="parameter">column</replaceable>
      </term>
      <listitem>
       <para>
	A name of a column in a table.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>
   <refsect3>
    <title>Outputs</title>
    <PARA>
    </PARA>
    <VARIABLELIST>
     <VARLISTENTRY>
      <TERM>
      </TERM>
      <LISTITEM>
       <PARA>
	<VARIABLELIST>
	 <VARLISTENTRY>
	  <TERM>
	   ERROR: Cannot insert a duplicate key into a unique index.
	  </term>
	  <listitem>
	   <para>
	    This error occurs at runtime if one tries to insert a
	    duplicate value into a column.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist></para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>
   <refsect3>
    <title>Description</title>
    <PARA>
     The UNIQUE constraint specifies a rule that a group of one or
     more distinct columns of a table may contain only unique values.
    </para>
    <para>
     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.
    </PARA>
    <PARA>
     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.
    </PARA>
    <Note>
     <Para>
      PostgreSQL automatically creates a unique index for each UNIQUE
      constraint, to assure
      data integrity. See CREATE INDEX for more information.
     </Para>
    </Note>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-CONSTRAINT-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    CONSTRAINT clause
   </TITLE>
   <para>
    Table constraint definition
   </para>
   <SYNOPSIS>
    [ CONSTRAINT name ] 
    { PRIMARY KEY constraint | 
    UNIQUE constraint | 
    CHECK constraint }  
   </SYNOPSIS>
   <PARA>
    Column constraint definition 
   </PARA>
   <SYNOPSIS>
    [ CONSTRAINT name ] 
    { NOT NULL constraint |
    PRIMARY KEY constraint | 
    UNIQUE constraint | 
    CHECK constraint }  
   </SYNOPSIS>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>
	   <replaceable class="parameter">name</replaceable>
	  </ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
          An arbitrary name given to an integrity constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>
	   <replaceable class="parameter">constraint</replaceable>
	  </ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The definition of the constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </VARIABLELIST>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>
   <para>
    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. 
   </para>
   <para>
    There are two ways to define integrity constraints:
    Table constraint and Column constraint.
   </para>
   <para>
    A Table Constraint is an integrity Constraint defined on one or
    more Columns of a Base table. The four variations of "Table
    Constraint" are:
    <simplelist columns="1">
     <member>PRIMARY KEY</member>
     <member>FOREIGN KEY</member>
     <member>UNIQUE</member>
     <member>CHECK</member>
    </simplelist>
   </para>
   <para>
    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:
    <simplelist columns="1">
     <member>PRIMARY KEY</member>
     <member>REFERENCES</member>
     <member>UNIQUE</member>
     <member>CHECK</member>
     <member>NOT NULL</member>
    </simplelist></para>
   <note>
    <para>
     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)
    </para>
   </note>
   <note>
    <para>
     PostgreSQL does not yet support either DOMAINs or ASSERTIONs.
    </para>
   </note>
   
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-CHECK-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <title>The CHECK constraint</title>
  <SYNOPSIS>
   [ CONSTRAINT name ] CHECK ( condition [, ...] ) 
  </SYNOPSIS>
   <refsect3 id="R3-SQL-CHECK-1">
    <title>Inputs</title>
   <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   An arbitrary name given to a constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>condition</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Any valid conditional expression.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
  </REFSECT3>

  <REFSECT3 ID="R3-SQL-CHECK-2">
   <REFSECT3INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT3INFO>
    <TITLE>
     Outputs
    </TITLE>
    <PARA>
     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
	<ReturnValue>
	 ERROR:  ExecAppend: rejected due to CHECK constraint
	 "<replaceable class="parameter">table_column</replaceable>".
	</ReturnValue>
       </TERM>
       <LISTITEM>
	<PARA>
	 This error occurs at runtime if one tries to insert an illegal
	 value into a column subject to a CHECK constraint.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
     </variablelist>
   </REFSECT3>
   <refsect3>
    <title>Description</title>
    <para>
     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.
    </PARA>
    <PARA>
     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.
    </PARA>
   </refsect3>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-PRIMARYKEY-1">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    PRIMARY KEY clause
   </TITLE>
   <PARA>
   Table constraint definition
   </PARA>
   <SYNOPSIS>
    [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) 
   </SYNOPSIS>
   <PARA>
    Column constraint definition
   </PARA> 
   <SYNOPSIS>
    [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY 
   </SYNOPSIS>
   
   <refsect3>
    <title>Parameters</title>
    <PARA>
     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
	<ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue>
       </TERM>
       <LISTITEM>
	<PARA>
	 An arbitrary name for the constraint.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
      <VARLISTENTRY>
       <TERM>
	<ReturnValue><REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE></ReturnValue>
       </TERM>
       <LISTITEM>
	<PARA>
	 The name of a column in the table.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
     </VARIABLELIST>
    </para>
   </refsect3>
   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term>ERROR: Cannot insert a duplicate key into a unique index.</term>
      <listitem>
       <para>
	This occurs at run-time if one tries to insert a duplicate value into
	a column subject to a PRIMARY KEY constraint.
      </PARA>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>
   <refsect3>
    <title>Description</title>
    <PARA>
     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. 
    </PARA>
    <PARA>
     A table's set of valid values may be constrained by only one
     PRIMARY KEY constraint at a time. 
    </PARA>
    <PARA>
     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. 
    </PARA>
   </REFSECT3>
   
   <REFSECT3 ID="R3-SQL-PRIMARYKEY-3">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     Notes
    </TITLE>
    <PARA>
     PostgreSQL automatically creates an unique index to assure
     data integrity. (See CREATE INDEX statement)
    </PARA>
   </refsect3>
   
  </REFSECT2>
 </refsect1>
 
 <REFSECT1 ID="R1-SQL-CREATETABLE-2">
  <TITLE>
   Usage
  </TITLE>
  <PARA>
   Create table films and table distributors
  </PARA>
  <ProgramListing>
   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
          );
  </ProgramListing>

  <ProgramListing>
   CREATE TABLE distributors (
          did      DECIMAL(03) PRIMARY KEY DEFAULT NEXTVAL('serial'),
          name     VARCHAR(40) NOT NULL CHECK (name &lt;&gt; '')
          );
  </ProgramListing>

  <PARA>
   Create a table with a 2-dimensional array
  </PARA>
  <ProgramListing>
   CREATE TABLE array (
          vector INT[][]
          );
  </ProgramListing>
   
  <PARA>
   Define two NOT NULL column constraints on the table distributors
  </PARA>
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(3) CONSTRAINT no_null NOT NULL,
        name     VARCHAR(40) NOT NULL
        );
  </ProgramListing>

  <PARA>
   Define a UNIQUE table constraint for the table films.
   UNIQUE table constraints can be defined on one or more
   columns of the table
  </PARA>
  <ProgramListing>
   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)
       );
  </ProgramListing>
  
  <PARA>
   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).
  </PARA>
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(03),
        name     VARCHAR(40) UNIQUE,
        UNIQUE(name)
        );
  </ProgramListing>

  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(3),
        name     VARCHAR(40) UNIQUE
        );
  </ProgramListing>

  <PARA>
   Define a CHECK column constraint.
  </PARA>
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(3) CHECK (did > 100),
        name     VARCHAR(40)
        );
  </ProgramListing>
  
  <PARA>
   Define a CHECK table constraint
  </PARA>
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(3),
        name     VARCHAR(40)
        CONSTRAINT con1 CHECK (did > 100 AND name > '')
        );
  </ProgramListing>
    
  <PARA>
   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
  </PARA>
  <ProgramListing>
   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)
       );
  </ProgramListing>
  
  <PARA>
   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)
  </PARA>
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(03),
        name     CHAR VARYING(40),
        PRIMARY KEY(did)
        ); 

   CREATE TABLE distributors (
        did      DECIMAL(03) PRIMARY KEY,
        name     VARCHAR(40)
        );
  </ProgramListing>
  <para>
   To assign a sequence as the default for the column did,
   and a literal to the column name
  </PARA>
  
  <ProgramListing>
       CREATE TABLE distributors (
              did      DECIMAL(3) DEFAULT NEXTVAL('serial'),
              name     VARCHAR(40) DEFAULT 'luso films'
              );
  </ProgramListing>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-3">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    Notes
   </TITLE>
   <PARA>
    CREATE TABLE/INHERITS is a PostgreSQL language extension.
   </PARA>
  </refsect2>
  
 </REFSECT1>
 
 <REFSECT1 ID="R1-SQL-CREATETABLE-3">
  <TITLE>
   Compatibility
  </TITLE>
  <PARA>
  </PARA>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-4">
   <REFSECT2INFO>
    <DATE>1998-04-15</DATE>
   </REFSECT2INFO>
   <TITLE>
    SQL92
   </TITLE>
   <PARA>
    In addition to normal CREATE TABLE, SQL92 also defines a
    CREATE TEMPORARY TABLE statement:
   </PARA>
   <synopsis>
   CREATE [ {GLOBAL | LOCAL} ] TEMPORARY TABLE table (
        column type [DEFAULT value] [CONSTRAINT column_constraint] [, ...] )
        [CONSTRAINT table_constraint ]
        [ ON COMMIT {DELETE | PRESERVE} ROWS ] 
   </synopsis>
   <para>
    For temporary tables, the CREATE TEMPORARY TABLE statement
    names a new table and defines the table's columns and
    constraints. 
   </para>
   <para>
    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. 
   </para>
   <para>
    To create a temporary table:
   </para>
   <programlisting>
      CREATE TEMPORARY TABLE actors (
             id      DECIMAL(03),
             name    VARCHAR(40),
             CONSTRAINT actor_id CHECK (id &lt; 150)
             ) ON COMMIT DELETE ROWS
   </programlisting> 
   <para>
Temporary tables are not currently available in <productname>Postgres</productname>.
<tip>
   <para>
    In the current release of <productname>Postgres</productname> (v6.4), to create a temporary
    table you must create and drop the table by explicit commands.
</tip>
   
   <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-1">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     UNIQUE clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for UNIQUE:
    <para>
     Table Constraint definition
    </PARA>
    <synopsis>
      [ CONSTRAINT name ]
      UNIQUE ( column [, ...] )
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     Column Constraint definition
    </para>
    <synopsis>
      [ CONSTRAINT name ]
      UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
   </refsect3>
   
   <REFSECT3 ID="R3-SQL-NOTNULL-4">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     NOT NULL clause
    </TITLE>
    <PARA>
     
     SQL92 specifies some additional capabilities for NOT NULL:
    </PARA>
    <synopsis>
      [ CONSTRAINT name ] NOT NULL 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
   </REFSECT3>
   
<!--
I can't figure out why DEFAULT clause is different from what we already have.
Perhaps because CURRENT_USER and CURRENT_DATE have specific types (currently
the "name" type), if you aren't careful then the types won't match up with
the column. Not our problem...
- Thomas 1998-08-16

   <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     DEFAULT clause
    </TITLE>
    <PARA>
     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.
    </para>
    <synopsis>
     DEFAULT niladic USER function |
             niladic datetime function |
             NULL
    </synopsis>
   </refsect3>
-->
   
   <REFSECT3 ID="R3-SQL-CONSTRAINT-3">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     CONSTRAINT clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for CONSTRAINTs,
     and also defines assertions and domain constraints.
    </PARA>
    <PARA>
     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:
    </para>
    <synopsis>
     CREATE ASSERTION name CHECK ( condition )
    </synopsis>
    
    <PARA>
     Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN
     statements:
    </para>
    <PARA>
     Domain constraint: 
    </para>
    <synopsis>
      [ CONSTRAINT name ] 
          CHECK constraint 
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     Table constraint definition:
    </para>
    <synopsis>
      [ CONSTRAINT name ] 
        { PRIMARY KEY constraint | 
          FOREIGN KEY constraint | 
          UNIQUE constraint | 
          CHECK constraint }
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]</synopsis>
    <para>
     Column constraint definition:
    </para>
    <synopsis>
     [ CONSTRAINT name ] 
        { NOT NULL constraint |
          PRIMARY KEY constraint | 
          FOREIGN KEY constraint | 
          UNIQUE constraint | 
          CHECK constraint }  
          [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
          [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     A CONSTRAINT definition may contain one deferment attribute
     clause and/or one initial constraint mode clause, in any order.
     <variablelist>
      <varlistentry>
       <term>NOT DEFERRABLE</term>
       <listitem>
	<para>
	 means that the Constraint must be checked for
	 violation of its rule after the execution of every SQL statement.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>DEFERRABLE</term>
       <listitem>
	<para>
	 means that checking of the Constraint may be deferred
	 until some later time, but no later than the end of the current
	 transaction.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
    <para>
     The constraint mode for every Constraint always has an initial
     default value which is set for that Constraint at the beginning
     of a transaction.
     <variablelist>
      <varlistentry>
       <term>INITIALLY IMMEDIATE</term>
       <listitem>
	<para>
	 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.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>INITIALLY DEFERRED</term>
       <listitem>
	<para>
	 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.</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   
   <REFSECT3 ID="R3-SQL-CHECK-4">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     CHECK clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for CHECK in either
table or column constraints.
    </PARA>
<!--
Constraints associated with domains do not need to be mentioned here,
even though it is the case that a domain constraint may possibly
affect a column or a table.
- Thomas 1998-08-16
    <para>
     A CHECK constraint is either a table constraint, a column
     constraint or a domain constraint.
    </para> 
-->
    <para>
     table constraint definition:
    </para>
    <synopsis>
     [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     column constraint definition:
    </para>
    <synopsis>
      [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
<!--
    <para>
     domain constraint definition: 
    </para>
    <synopsis>
     [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     CHECK domain constraints can be defined in either
     a CREATE DOMAIN statement or an ALTER DOMAIN statement:
    </para>
    <programlisting>
     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%');
    </programlisting>
-->
   </refsect3>
   
   
   <REFSECT3 ID="R3-SQL-PRIMARYKEY-1">
    <REFSECT3INFO>
     <DATE>1998-04-15</DATE>
    </REFSECT3INFO>
    <TITLE>
     PRIMARY KEY clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for PRIMARY KEY:
    </para>
    <PARA>
     Table Constraint definition:
    </para>
    <synopsis>
      [ CONSTRAINT name ] 
      PRIMARY KEY ( column [, ...] ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     Column Constraint definition: 
    </para>
    <synopsis>
      [ CONSTRAINT name ] 
      PRIMARY KEY 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
   </refsect3>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:t
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->