create_table.sgml

    <para>
     <productname>Postgres</productname> does not yet 
(as of version 6.4) support FOREIGN KEY
integrity constraints. The parser understands the FOREIGN KEY syntax,
but only prints a notice and otherwise ignores the clause.
     Foreign keys may be partially emulated by triggers (See the CREATE TRIGGER
     statement).
    </para>
   </note>
   
  <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-4">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    UNIQUE Constraint
   </TITLE>
   <para>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] )
   </SYNOPSIS>
   <refsect3>
    <title>Inputs</title>
    <variablelist>
     <varlistentry>
      <term>
      CONSTRAINT <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>
    <VARIABLELIST>
     <VARLISTENTRY>
      <TERM>
<replaceable>status</replaceable>
      </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>
      </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.
The behavior of the UNIQUE table constraint is the same as that for column
constraints, with the additional capability to span multiple columns.
    </para>
    <para>
See the section on the UNIQUE column constraint for more details.

  <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-4">
   <TITLE>
Usage
</title>

  <PARA>
   Define a UNIQUE table constraint for the table distributors:
  <ProgramListing>
CREATE TABLE distributors (
    did      DECIMAL(03),
    name     VARCHAR(40),
    UNIQUE(name)
);
  </ProgramListing>


  </REFSECT2>

  <REFSECT2 ID="R2-SQL-PRIMARYKEY-4">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    PRIMARY KEY Constraint
   </TITLE>
<para>
   <SYNOPSIS>
    [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) 
   </SYNOPSIS>
   
   <refsect3>
    <title>Inputs</title>
    <PARA>
     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue>
       </TERM>
       <LISTITEM>
	<PARA>
	 An arbitrary name for the constraint.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
      <VARLISTENTRY>
       <TERM>
<REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...]
       </TERM>
       <LISTITEM>
	<PARA>
	 The names of one or more columns in the table.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
     </VARIABLELIST>
    </para>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term>
<replaceable>status</replaceable>
</term>
<listitem>
<para>
    <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>
      </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,
     (non duplicate), 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.
    </PARA>
    <PARA>
Refer to the section on the PRIMARY KEY column constraint for more
information.
   </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 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>
   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 equivalent)
  </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>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-3">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    Notes
   </TITLE>
   <PARA>
    CREATE TABLE/INHERITS is a <productname>Postgres</productname>
 language extension.
   </PARA>
  </refsect2>
  
 </REFSECT1>
 
 <REFSECT1 ID="R1-SQL-CREATETABLE-3">
  <TITLE>
   Compatibility
  </TITLE>
  <PARA>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-4">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    SQL92
   </TITLE>
   <PARA>
    In addition to the 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-09-11</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-09-11</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-09-11</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-09-11</DATE>
    </REFSECT3INFO>
    <TITLE>
     CONSTRAINT clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for constraints,
     and also defines assertions and domain constraints.
   <note>
    <para>
     <productname>Postgres</productname> does not yet support 
either domains or assertions.
    </para>
   </note>

    <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-09-11</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-09-11</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>


<REFENTRY ID="SQL-CREATETABLEAS">
 <REFMETA>
  <REFENTRYTITLE>
   CREATE TABLE AS
  </REFENTRYTITLE>
  <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
 </REFMETA>
 <REFNAMEDIV>
  <REFNAME>
   CREATE TABLE AS
  </REFNAME>
  <REFPURPOSE>
   Creates a new table
  </REFPURPOSE>
  
 <REFSYNOPSISDIV>
  <REFSYNOPSISDIVINFO>
   <DATE>1998-09-22</DATE>
  </REFSYNOPSISDIVINFO>
  <SYNOPSIS>
CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> [ ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ] AS <REPLACEABLE CLASS="PARAMETER">select_clause</REPLACEABLE>
  </SYNOPSIS>
  
  <REFSECT2 ID="R2-SQL-CREATETABLEAS-1">
   <REFSECT2INFO>
    <DATE>1998-09-22</DATE>
   </REFSECT2INFO>
   <TITLE>
    Inputs
   </TITLE>
   <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. Multiple column names can be specified using
a comma-delimited list of column names.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">select_clause</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
A valid query statement. Refer to SELECT for a description of the
allowed syntax.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST>

  <REFSECT2 ID="R2-SQL-CREATETABLEAS-2">
   <REFSECT2INFO>
    <DATE>1998-09-22</DATE>
   </REFSECT2INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>
   Refer to CREATE TABLE and SELECT for a summary of possible output
messages.

  <REFSECT1 ID="R1-SQL-CREATETABLEAS-1">
   <REFSECT1INFO>
    <DATE>1998-09-22</DATE>
   </REFSECT1INFO>
   <TITLE>
    Description
   </TITLE>
   <PARA>
CREATE TABLE AS enables a table to be created from the contents of
an existing table. It has functionality equivalent to SELECT TABLE INTO,
but with perhaps a more obvious syntax.

</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:
-->