create_table.sgml

  <para>
   Create table <structname>films</> and table
   <structname>distributors</>:

<programlisting>
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);
</programlisting>

<programlisting>
CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name &lt;&gt; '')
);
</programlisting>
  </para>

  <para>
   Create a table with a 2-dimensional array:

<programlisting>
CREATE TABLE array_int (
    vector  int[][]
);
</programlisting>
  </para>

  <para>
   Define a unique table constraint for the table
   <literal>films</literal>.  Unique table constraints can be defined
   on one or more columns of the table:

<programlisting>
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);
</programlisting>
  </para>

  <para>
   Define a check column constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer CHECK (did &gt; 100),
    name    varchar(40)
);
</programlisting>
  </para>

  <para>
   Define a check table constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did &gt; 100 AND name &lt;&gt; '')
);
</programlisting>
  </para>

  <para>
   Define a primary key table constraint for the table
   <structname>films</>:

<programlisting>
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);
</programlisting>
  </para>

  <para>
   Define a primary key constraint for table
   <structname>distributors</>.  The following two examples are
   equivalent, the first using the table constraint syntax, the second
   the column constraint syntax:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);
</programlisting>

<programlisting>
CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);
</programlisting>
  </para>

  <para>
   Assign a literal constant default value for the column
   <literal>name</literal>, arrange for the default value of column
   <literal>did</literal> to be generated by selecting the next value
   of a sequence object, and make the default value of
   <literal>modtime</literal> be the time at which the row is
   inserted:

<programlisting>
CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);
</programlisting>
  </para>

  <para>
   Define two <literal>NOT NULL</> column constraints on the table
   <classname>distributors</classname>, one of which is explicitly
   given a name:

<programlisting>
CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);
</programlisting>
    </para>

    <para>
     Define a unique constraint for the <literal>name</literal> column:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);
</programlisting>

     The same, specified as a table constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);
</programlisting>
  </para>

  <para>
   Create the same table, specifying 70% fill factor for both the table
   and its unique index:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);
</programlisting>
  </para>

  <para>
   Create table <structname>circles</> with an exclusion
   constraint that prevents any two circles from overlapping:

<programlisting>
CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &amp;&amp;)
);
</programlisting>
  </para>

  <para>
   Create table <structname>cinemas</> in tablespace <structname>diskvol1</>:

<programlisting>
CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;
</programlisting>
  </para>

  <para>
   Create a composite type and a typed table:
<programlisting>
CREATE TYPE employee_type AS (name text, salary numeric);

CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
);
</programlisting>
  </para>
 </refsect1>

 <refsect1 id="SQL-CREATETABLE-compatibility">
  <title id="SQL-CREATETABLE-compatibility-title">Compatibility</title>

  <para>
   The <command>CREATE TABLE</command> command conforms to the
   <acronym>SQL</acronym> standard, with exceptions listed below.
  </para>

  <refsect2>
   <title>Temporary Tables</title>

   <para>
    Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
    resembles that of the SQL standard, the effect is not the same.  In the
    standard,
    temporary tables are defined just once and automatically exist (starting
    with empty contents) in every session that needs them.
    <productname>PostgreSQL</productname> instead
    requires each session to issue its own <literal>CREATE TEMPORARY
    TABLE</literal> command for each temporary table to be used.  This allows
    different sessions to use the same temporary table name for different
    purposes, whereas the standard's approach constrains all instances of a
    given temporary table name to have the same table structure.
   </para>

   <para>
    The standard's definition of the behavior of temporary tables is
    widely ignored.  <productname>PostgreSQL</productname>'s behavior
    on this point is similar to that of several other SQL databases.
   </para>

   <para>
    The standard's distinction between global and local temporary tables
    is not in <productname>PostgreSQL</productname>, since that distinction
    depends on the concept of modules, which
    <productname>PostgreSQL</productname> does not have.
    For compatibility's sake, <productname>PostgreSQL</productname> will
    accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
    in a temporary table declaration, but they have no effect.
   </para>

   <para>
    The <literal>ON COMMIT</literal> clause for temporary tables
    also resembles the SQL standard, but has some differences.
    If the <literal>ON COMMIT</> clause is omitted, SQL specifies that the
    default behavior is <literal>ON COMMIT DELETE ROWS</>.  However, the
    default behavior in <productname>PostgreSQL</productname> is
    <literal>ON COMMIT PRESERVE ROWS</literal>.  The <literal>ON COMMIT
    DROP</literal> option does not exist in SQL.
   </para>
  </refsect2>

  <refsect2>
   <title>Non-deferred Uniqueness Constraints</title>

   <para>
    When a <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint is
    not deferrable, <productname>PostgreSQL</productname> checks for
    uniqueness immediately whenever a row is inserted or modified.
    The SQL standard says that uniqueness should be enforced only at
    the end of the statement; this makes a difference when, for example,
    a single command updates multiple key values.  To obtain
    standard-compliant behavior, declare the constraint as
    <literal>DEFERRABLE</> but not deferred (i.e., <literal>INITIALLY
    IMMEDIATE</>).  Be aware that this can be significantly slower than
    immediate uniqueness checking.
   </para>
  </refsect2>

  <refsect2>
   <title>Column Check Constraints</title>

   <para>
    The SQL standard says that <literal>CHECK</> column constraints
    can only refer to the column they apply to; only <literal>CHECK</>
    table constraints can refer to multiple columns.
    <productname>PostgreSQL</productname> does not enforce this
    restriction; it treats column and table check constraints alike.
   </para>
  </refsect2>

  <refsect2>
   <title><literal>EXCLUDE</literal> Constraint</title>

   <para>
    The <literal>EXCLUDE</> constraint type is a
    <productname>PostgreSQL</productname> extension.
   </para>
  </refsect2>

  <refsect2>
   <title><literal>NULL</literal> <quote>Constraint</quote></title>

   <para>
    The <literal>NULL</> <quote>constraint</quote> (actually a
    non-constraint) is a <productname>PostgreSQL</productname>
    extension to the SQL standard that is included for compatibility with some
    other database systems (and for symmetry with the <literal>NOT
    NULL</literal> constraint).  Since it is the default for any
    column, its presence is simply noise.
   </para>
  </refsect2>

  <refsect2>
   <title>Inheritance</title>

   <para>
    Multiple inheritance via the <literal>INHERITS</literal> clause is
    a <productname>PostgreSQL</productname> language extension.
    SQL:1999 and later define single inheritance using a
    different syntax and different semantics.  SQL:1999-style
    inheritance is not yet supported by
    <productname>PostgreSQL</productname>.
   </para>
  </refsect2>

  <refsect2>
   <title>Zero-column tables</title>

   <para>
    <productname>PostgreSQL</productname> allows a table of no columns
    to be created (for example, <literal>CREATE TABLE foo();</>).  This
    is an extension from the SQL standard, which does not allow zero-column
    tables.  Zero-column tables are not in themselves very useful, but
    disallowing them creates odd special cases for <command>ALTER TABLE
    DROP COLUMN</>, so it seems cleaner to ignore this spec restriction.
   </para>
  </refsect2>

  <refsect2>
   <title><literal>WITH</> clause</title>

   <para>
    The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
    extension; neither storage parameters nor OIDs are in the standard.
   </para>
  </refsect2>

  <refsect2>
   <title>Tablespaces</title>

   <para>
    The <productname>PostgreSQL</productname> concept of tablespaces is not
    part of the standard.  Hence, the clauses <literal>TABLESPACE</literal>
    and <literal>USING INDEX TABLESPACE</literal> are extensions.
   </para>
  </refsect2>

  <refsect2>
   <title>Typed Tables</title>

   <para>
    Typed tables implement a subset of the SQL standard.  According to
    the standard, a typed table has columns corresponding to the
    underlying composite type as well as one other column that is
    the <quote>self-referencing column</quote>.  PostgreSQL does not
    support these self-referencing columns explicitly, but the same
    effect can be had using the OID feature.
   </para>
  </refsect2>
 </refsect1>


 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
   <member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
   <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
   <member><xref linkend="sql-createtype" endterm="sql-createtype-title"></member>
  </simplelist>
 </refsect1>
</refentry>