doc/src/sgml/ref/create_table.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATETABLE">
   7  <refmeta>
   8   <refentrytitle id="sql-createtable-title">CREATE TABLE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE TABLE</refname>
  15   <refpurpose>define a new table</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createtable">
  19   <primary>CREATE TABLE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable> ( [
  25   { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ DEFAULT <replaceable>default_expr</> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
  26     | <replaceable>table_constraint</replaceable>
  27     | LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
  28     [, ... ]
  29 ] )
  30 [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
  31 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
  32 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  33 [ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
  34
  35 where <replaceable class="PARAMETER">column_constraint</replaceable> is:
  36
  37 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
  38 { NOT NULL |
  39   NULL |
  40   UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
  41   PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
  42   CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
  43   REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  44     [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
  45 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  46
  47 and <replaceable class="PARAMETER">table_constraint</replaceable> is:
  48
  49 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
  50 { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
  51   PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
  52   CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
  53   FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
  54     [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
  55 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  56
  57 <replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</> and <literal>PRIMARY KEY</> constraints are:
  58
  59 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
  60 [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
  61 </synopsis>
  62
  63  </refsynopsisdiv>
  64
  65  <refsect1 id="SQL-CREATETABLE-description">
  66   <title>Description</title>
  67
  68   <para>
  69    <command>CREATE TABLE</command> will create a new, initially empty table
  70    in the current database. The table will be owned by the user issuing the
  71    command.
  72   </para>
  73
  74   <para>
  75    If a schema name is given (for example, <literal>CREATE TABLE
  76    myschema.mytable ...</>) then the table is created in the specified
  77    schema.  Otherwise it is created in the current schema.  Temporary
  78    tables exist in a special schema, so a schema name cannot be given
  79    when creating a temporary table.  The name of the table must be
  80    distinct from the name of any other table, sequence, index, or view
  81    in the same schema.
  82   </para>
  83
  84   <para>
  85    <command>CREATE TABLE</command> also automatically creates a data
  86    type that represents the composite type corresponding
  87    to one row of the table.  Therefore, tables cannot have the same
  88    name as any existing data type in the same schema.
  89   </para>
  90
  91   <para>
  92    The optional constraint clauses specify constraints (tests) that
  93    new or updated rows must satisfy for an insert or update operation
  94    to succeed.  A constraint is an SQL object that helps define the
  95    set of valid values in the table in various ways.
  96   </para>
  97
  98   <para>
  99    There are two ways to define constraints: table constraints and
 100    column constraints.  A column constraint is defined as part of a
 101    column definition.  A table constraint definition is not tied to a
 102    particular column, and it can encompass more than one column.
 103    Every column constraint can also be written as a table constraint;
 104    a column constraint is only a notational convenience for use when the
 105    constraint only affects one column.
 106   </para>
 107  </refsect1>
 108
 109  <refsect1>
 110   <title>Parameters</title>
 111
 112   <variablelist>
 113
 114    <varlistentry>
 115     <term><literal>TEMPORARY</> or <literal>TEMP</></term>
 116     <listitem>
 117      <para>
 118       If specified, the table is created as a temporary table.
 119       Temporary tables are automatically dropped at the end of a
 120       session, or optionally at the end of the current transaction
 121       (see <literal>ON COMMIT</literal> below).  Existing permanent
 122       tables with the same name are not visible to the current session
 123       while the temporary table exists, unless they are referenced
 124       with schema-qualified names. Any indexes created on a temporary
 125       table are automatically temporary as well.
 126      </para>
 127
 128      <para>
 129       Optionally, <literal>GLOBAL</literal> or <literal>LOCAL</literal>
 130       can be written before <literal>TEMPORARY</> or <literal>TEMP</>.
 131       This makes no difference in <productname>PostgreSQL</>, but see
 132       <xref linkend="sql-createtable-compatibility"
 133       endterm="sql-createtable-compatibility-title">.
 134      </para>
 135     </listitem>
 136    </varlistentry>
 137
 138    <varlistentry>
 139     <term><replaceable class="PARAMETER">table_name</replaceable></term>
 140     <listitem>
 141      <para>
 142       The name (optionally schema-qualified) of the table to be created.
 143      </para>
 144     </listitem>
 145    </varlistentry>
 146
 147    <varlistentry>
 148     <term><replaceable class="PARAMETER">column_name</replaceable></term>
 149     <listitem>
 150      <para>
 151       The name of a column to be created in the new table.
 152      </para>
 153     </listitem>
 154    </varlistentry>
 155
 156    <varlistentry>
 157     <term><replaceable class="PARAMETER">data_type</replaceable></term>
 158     <listitem>
 159      <para>
 160       The data type of the column. This can include array
 161       specifiers. For more information on the data types supported by
 162       <productname>PostgreSQL</productname>, refer to <xref
 163       linkend="datatype">.
 164      </para>
 165     </listitem>
 166    </varlistentry>
 167
 168    <varlistentry>
 169     <term><literal>DEFAULT
 170     <replaceable>default_expr</replaceable></literal></term>
 171     <listitem>
 172      <para>
 173       The <literal>DEFAULT</> clause assigns a default data value for
 174       the column whose column definition it appears within.  The value
 175       is any variable-free expression (subqueries and cross-references
 176       to other columns in the current table are not allowed).  The
 177       data type of the default expression must match the data type of the
 178       column.
 179      </para>
 180
 181      <para>
 182       The default expression will be used in any insert operation that
 183       does not specify a value for the column.  If there is no default
 184       for a column, then the default is null.
 185      </para>
 186     </listitem>
 187    </varlistentry>
 188
 189    <varlistentry>
 190     <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
 191     <listitem>
 192      <para>
 193       The optional <literal>INHERITS</> clause specifies a list of
 194       tables from which the new table automatically inherits all
 195       columns.
 196      </para>
 197
 198      <para>
 199       Use of <literal>INHERITS</> creates a persistent relationship
 200       between the new child table and its parent table(s).  Schema
 201       modifications to the parent(s) normally propagate to children
 202       as well, and by default the data of the child table is included in
 203       scans of the parent(s).
 204      </para>
 205
 206      <para>
 207       If the same column name exists in more than one parent
 208       table, an error is reported unless the data types of the columns
 209       match in each of the parent tables.  If there is no conflict,
 210       then the duplicate columns are merged to form a single column in
 211       the new table.  If the column name list of the new table
 212       contains a column name that is also inherited, the data type must
 213       likewise match the inherited column(s), and the column
 214       definitions are merged into one.  If the
 215       new table explicitly specifies a default value for the column,
 216       this default overrides any defaults from inherited declarations
 217       of the column.  Otherwise, any parents that specify default
 218       values for the column must all specify the same default, or an
 219       error will be reported.
 220      </para>
 221
 222      <para>
 223       <literal>CHECK</> constraints are merged in essentially the same way as
 224       columns: if multiple parent tables and/or the new table definition
 225       contain identically-named <literal>CHECK</> constraints, these
 226       constraints must all have the same check expression, or an error will be
 227       reported.  Constraints having the same name and expression will
 228       be merged into one copy.  Notice that an unnamed <literal>CHECK</>
 229       constraint in the new table will never be merged, since a unique name
 230       will always be chosen for it.
 231      </para>
 232
 233 <!--
 234      <para>
 235       <productname>PostgreSQL</> automatically allows the
 236      created table to inherit
 237       functions on tables above it in the inheritance hierarchy; that
 238       is, if we create table <literal>foo</literal> inheriting from
 239       <literal>bar</literal>, then functions that accept the tuple
 240       type <literal>bar</literal> can also be applied to instances of
 241       <literal>foo</literal>.  (Currently, this works reliably for
 242       functions on the first or only parent table, but not so well for
 243       functions on additional parents.)
 244      </para>
 245 -->
 246     </listitem>
 247    </varlistentry>
 248
 249    <varlistentry>
 250     <term><literal>LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ]</literal></term>
 251     <listitem>
 252      <para>
 253       The <literal>LIKE</literal> clause specifies a table from which
 254       the new table automatically copies all column names, their data types,
 255       and their not-null constraints.
 256      </para>
 257      <para>
 258       Unlike <literal>INHERITS</literal>, the new table and original table
 259       are completely decoupled after creation is complete.  Changes to the
 260       original table will not be applied to the new table, and it is not
 261       possible to include data of the new table in scans of the original
 262       table.
 263      </para>
 264      <para>
 265       Default expressions for the copied column definitions will only be
 266       copied if <literal>INCLUDING DEFAULTS</literal> is specified.  The
 267       default behavior is to exclude default expressions, resulting in the
 268       copied columns in the new table having null defaults.
 269      </para>
 270      <para>
 271       Not-null constraints are always copied to the new table.
 272       <literal>CHECK</literal> constraints will only be copied if
 273       <literal>INCLUDING CONSTRAINTS</literal> is specified; other types of
 274       constraints will never be copied. Also, no distinction is made between
 275       column constraints and table constraints &mdash; when constraints are
 276       requested, all check constraints are copied.
 277      </para>
 278      <para>
 279       Any indexes on the original table will not be created on the new
 280       table, unless the <literal>INCLUDING INDEXES</literal> clause is
 281       specified.
 282      </para>
 283      <para>
 284       Note also that unlike <literal>INHERITS</literal>, copied columns and
 285       constraints are not merged with similarly named columns and constraints.
 286       If the same name is specified explicitly or in another
 287       <literal>LIKE</literal> clause, an error is signalled.
 288      </para>
 289     </listitem>
 290    </varlistentry>
 291
 292    <varlistentry>
 293     <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
 294     <listitem>
 295      <para>
 296       An optional name for a column or table constraint.  If the
 297       constraint is violated, the constraint name is present in error messages,
 298       so constraint names like <literal>col must be positive</> can be used
 299       to communicate helpful constraint information to client applications.
 300       (Double-quotes are needed to specify constraint names that contain spaces.)
 301       If a constraint name is not specified, the system generates a name.
 302      </para>
 303     </listitem>
 304    </varlistentry>
 305
 306    <varlistentry>
 307     <term><literal>NOT NULL</></term>
 308     <listitem>
 309      <para>
 310       The column is not allowed to contain null values.
 311      </para>
 312     </listitem>
 313    </varlistentry>
 314
 315    <varlistentry>
 316     <term><literal>NULL</></term>
 317     <listitem>
 318      <para>
 319       The column is allowed to contain null values. This is the default.
 320      </para>
 321
 322      <para>
 323       This clause is only provided for compatibility with
 324       non-standard SQL databases.  Its use is discouraged in new
 325       applications.
 326      </para>
 327     </listitem>
 328    </varlistentry>
 329
 330    <varlistentry>
 331     <term><literal>UNIQUE</> (column constraint)</term>
 332     <term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
 333
 334     <listitem>
 335      <para>
 336       The <literal>UNIQUE</literal> constraint specifies that a
 337       group of one or more columns of a table can contain
 338       only unique values. The behavior of the unique table constraint
 339       is the same as that for column constraints, with the additional
 340       capability to span multiple columns.
 341      </para>
 342
 343      <para>
 344       For the purpose of a unique constraint, null values are not
 345       considered equal.
 346      </para>
 347
 348      <para>
 349       Each unique table constraint must name a set of columns that is
 350       different from the set of columns named by any other unique or
 351       primary key constraint defined for the table.  (Otherwise it
 352       would just be the same constraint listed twice.)
 353      </para>
 354     </listitem>
 355    </varlistentry>
 356
 357    <varlistentry>
 358     <term><literal>PRIMARY KEY</> (column constraint)</term>
 359     <term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
 360     <listitem>
 361      <para>
 362       The primary key constraint specifies that a column or columns of a table
 363       can contain only unique (non-duplicate), nonnull values.
 364       Technically, <literal>PRIMARY KEY</literal> is merely a
 365       combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but
 366       identifying a set of columns as primary key also provides
 367       metadata about the design of the schema, as a primary key
 368       implies that other tables
 369       can rely on this set of columns as a unique identifier for rows.
 370      </para>
 371
 372      <para>
 373       Only one primary key can be specified for a table, whether as a
 374       column constraint or a table constraint.
 375      </para>
 376
 377      <para>
 378       The primary key constraint should name a set of columns that is
 379       different from other sets of columns named by any unique
 380       constraint defined for the same table.
 381      </para>
 382     </listitem>
 383    </varlistentry>
 384
 385    <varlistentry>
 386     <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>
 387     <listitem>
 388      <para>
 389       The <literal>CHECK</> clause specifies an expression producing a
 390       Boolean result which new or updated rows must satisfy for an
 391       insert or update operation to succeed.  Expressions evaluating
 392       to TRUE or UNKNOWN succeed.  Should any row of an insert or
 393       update operation produce a FALSE result an error exception is
 394       raised and the insert or update does not alter the database.  A
 395       check constraint specified as a column constraint should
 396       reference that column's value only, while an expression
 397       appearing in a table constraint can reference multiple columns.
 398      </para>
 399
 400      <para>
 401       Currently, <literal>CHECK</literal> expressions cannot contain
 402       subqueries nor refer to variables other than columns of the
 403       current row.
 404      </para>
 405     </listitem>
 406    </varlistentry>
 407
 408
 409    <varlistentry>
 410     <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term>
 411
 412    <term><literal>FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] )
 413     REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
 414     [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
 415     [ ON DELETE <replaceable class="parameter">action</replaceable> ]
 416     [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal>
 417     (table constraint)</term>
 418
 419     <listitem>
 420      <para>
 421       These clauses specify a foreign key constraint, which requires
 422       that a group of one or more columns of the new table must only
 423       contain values that match values in the referenced
 424       column(s) of some row of the referenced table.  If <replaceable
 425       class="parameter">refcolumn</replaceable> is omitted, the
 426       primary key of the <replaceable
 427       class="parameter">reftable</replaceable> is used.  The
 428       referenced columns must be the columns of a unique or primary
 429       key constraint in the referenced table.  Note that foreign key
 430       constraints cannot be defined between temporary tables and
 431       permanent tables.
 432      </para>
 433
 434      <para>
 435       A value inserted into the referencing column(s) is matched against the
 436       values of the referenced table and referenced columns using the
 437       given match type.  There are three match types: <literal>MATCH
 438       FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
 439       SIMPLE</literal>, which is also the default.  <literal>MATCH
 440       FULL</> will not allow one column of a multicolumn foreign key
 441       to be null unless all foreign key columns are null.
 442       <literal>MATCH SIMPLE</literal> allows some foreign key columns
 443       to be null while other parts of the foreign key are not
 444       null. <literal>MATCH PARTIAL</> is not yet implemented.
 445      </para>
 446
 447      <para>
 448       In addition, when the data in the referenced columns is changed,
 449       certain actions are performed on the data in this table's
 450       columns.  The <literal>ON DELETE</literal> clause specifies the
 451       action to perform when a referenced row in the referenced table is
 452       being deleted.  Likewise, the <literal>ON UPDATE</literal>
 453       clause specifies the action to perform when a referenced column
 454       in the referenced table is being updated to a new value. If the
 455       row is updated, but the referenced column is not actually
 456       changed, no action is done. Referential actions other than the
 457       <literal>NO ACTION</literal> check cannot be deferred, even if
 458       the constraint is declared deferrable. There are the following possible
 459       actions for each clause:
 460
 461       <variablelist>
 462        <varlistentry>
 463         <term><literal>NO ACTION</literal></term>
 464         <listitem>
 465          <para>
 466           Produce an error indicating that the deletion or update
 467           would create a foreign key constraint violation.
 468           If the constraint is deferred, this
 469           error will be produced at constraint check time if there still
 470           exist any referencing rows.  This is the default action.
 471          </para>
 472         </listitem>
 473        </varlistentry>
 474
 475        <varlistentry>
 476         <term><literal>RESTRICT</literal></term>
 477         <listitem>
 478          <para>
 479           Produce an error indicating that the deletion or update
 480           would create a foreign key constraint violation.
 481           This is the same as <literal>NO ACTION</literal> except that
 482           the check is not deferrable.
 483          </para>
 484         </listitem>
 485        </varlistentry>
 486
 487        <varlistentry>
 488         <term><literal>CASCADE</literal></term>
 489         <listitem>
 490          <para>
 491           Delete any rows referencing the deleted row, or update the
 492           value of the referencing column to the new value of the
 493           referenced column, respectively.
 494          </para>
 495         </listitem>
 496        </varlistentry>
 497
 498        <varlistentry>
 499         <term><literal>SET NULL</literal></term>
 500         <listitem>
 501          <para>
 502           Set the referencing column(s) to null.
 503          </para>
 504         </listitem>
 505        </varlistentry>
 506
 507        <varlistentry>
 508         <term><literal>SET DEFAULT</literal></term>
 509         <listitem>
 510          <para>
 511           Set the referencing column(s) to their default values.
 512          </para>
 513         </listitem>
 514        </varlistentry>
 515       </variablelist>
 516      </para>
 517
 518      <para>
 519       If the referenced column(s) are changed frequently, it might be wise to
 520       add an index to the foreign key column so that referential actions
 521       associated with the foreign key column can be performed more
 522       efficiently.
 523      </para>
 524     </listitem>
 525    </varlistentry>
 526
 527    <varlistentry>
 528     <term><literal>DEFERRABLE</literal></term>
 529     <term><literal>NOT DEFERRABLE</literal></term>
 530     <listitem>
 531      <para>
 532       This controls whether the constraint can be deferred.  A
 533       constraint that is not deferrable will be checked immediately
 534       after every command.  Checking of constraints that are
 535       deferrable can be postponed until the end of the transaction
 536       (using the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command).
 537       <literal>NOT DEFERRABLE</literal> is the default.  Only foreign
 538       key constraints currently accept this clause.  All other
 539       constraint types are not deferrable.
 540      </para>
 541     </listitem>
 542    </varlistentry>
 543
 544    <varlistentry>
 545     <term><literal>INITIALLY IMMEDIATE</literal></term>
 546     <term><literal>INITIALLY DEFERRED</literal></term>
 547     <listitem>
 548      <para>
 549       If a constraint is deferrable, this clause specifies the default
 550       time to check the constraint.  If the constraint is
 551       <literal>INITIALLY IMMEDIATE</literal>, it is checked after each
 552       statement. This is the default.  If the constraint is
 553       <literal>INITIALLY DEFERRED</literal>, it is checked only at the
 554       end of the transaction.  The constraint check time can be
 555       altered with the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command.
 556      </para>
 557     </listitem>
 558    </varlistentry>
 559
 560    <varlistentry>
 561     <term><literal>WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term>
 562     <listitem>
 563      <para>
 564       This clause specifies optional storage parameters for a table or index;
 565       see <xref linkend="sql-createtable-storage-parameters"
 566       endterm="sql-createtable-storage-parameters-title"> for more
 567       information.  The <literal>WITH</> clause for a
 568       table can also include <literal>OIDS=TRUE</> (or just <literal>OIDS</>)
 569       to specify that rows of the new table
 570       should have OIDs (object identifiers) assigned to them, or
 571       <literal>OIDS=FALSE</> to specify that the rows should not have OIDs.
 572       If <literal>OIDS</> is not specified, the default setting depends upon
 573       the <xref linkend="guc-default-with-oids"> configuration parameter.
 574       (If the new table inherits from any tables that have OIDs, then
 575       <literal>OIDS=TRUE</> is forced even if the command says
 576       <literal>OIDS=FALSE</>.)
 577      </para>
 578
 579      <para>
 580       If <literal>OIDS=FALSE</literal> is specified or implied, the new
 581       table does not store OIDs and no OID will be assigned for a row inserted
 582       into it. This is generally considered worthwhile, since it
 583       will reduce OID consumption and thereby postpone the wraparound
 584       of the 32-bit OID counter. Once the counter wraps around, OIDs
 585       can no longer be assumed to be unique, which makes them
 586       considerably less useful. In addition, excluding OIDs from a
 587       table reduces the space required to store the table on disk by
 588       4 bytes per row (on most machines), slightly improving performance.
 589      </para>
 590
 591      <para>
 592       To remove OIDs from a table after it has been created, use <xref
 593       linkend="sql-altertable" endterm="sql-altertable-title">.
 594      </para>
 595     </listitem>
 596    </varlistentry>
 597
 598    <varlistentry>
 599     <term><literal>WITH OIDS</></term>
 600     <term><literal>WITHOUT OIDS</></term>
 601     <listitem>
 602      <para>
 603       These are obsolescent syntaxes equivalent to <literal>WITH (OIDS)</>
 604       and <literal>WITH (OIDS=FALSE)</>, respectively.  If you wish to give
 605       both an <literal>OIDS</> setting and storage parameters, you must use
 606       the <literal>WITH ( ... )</> syntax; see above.
 607      </para>
 608     </listitem>
 609    </varlistentry>
 610
 611    <varlistentry>
 612     <term><literal>ON COMMIT</literal></term>
 613     <listitem>
 614      <para>
 615       The behavior of temporary tables at the end of a transaction
 616       block can be controlled using <literal>ON COMMIT</literal>.
 617       The three options are:
 618
 619       <variablelist>
 620        <varlistentry>
 621         <term><literal>PRESERVE ROWS</literal></term>
 622         <listitem>
 623          <para>
 624           No special action is taken at the ends of transactions.
 625           This is the default behavior.
 626          </para>
 627         </listitem>
 628        </varlistentry>
 629
 630        <varlistentry>
 631         <term><literal>DELETE ROWS</literal></term>
 632         <listitem>
 633          <para>
 634           All rows in the temporary table will be deleted at the end
 635           of each transaction block.  Essentially, an automatic <xref
 636           linkend="sql-truncate" endterm="sql-truncate-title"> is done
 637           at each commit.
 638          </para>
 639         </listitem>
 640        </varlistentry>
 641
 642        <varlistentry>
 643         <term><literal>DROP</literal></term>
 644         <listitem>
 645          <para>
 646           The temporary table will be dropped at the end of the current
 647           transaction block.
 648          </para>
 649         </listitem>
 650        </varlistentry>
 651       </variablelist>
 652      </para>
 653     </listitem>
 654    </varlistentry>
 655
 656    <varlistentry>
 657     <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
 658     <listitem>
 659      <para>
 660       The <replaceable class="PARAMETER">tablespace</replaceable> is the name
 661       of the tablespace in which the new table is to be created.
 662       If not specified,
 663       <xref linkend="guc-default-tablespace"> is consulted, or
 664       <xref linkend="guc-temp-tablespaces"> if the table is temporary.
 665      </para>
 666     </listitem>
 667    </varlistentry>
 668
 669    <varlistentry>
 670     <term><literal>USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
 671     <listitem>
 672      <para>
 673       This clause allows selection of the tablespace in which the index
 674       associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
 675       KEY</literal> constraint will be created.
 676       If not specified,
 677       <xref linkend="guc-default-tablespace"> is consulted, or
 678       <xref linkend="guc-temp-tablespaces"> if the table is temporary.
 679      </para>
 680     </listitem>
 681    </varlistentry>
 682
 683   </variablelist>
 684
 685   <refsect2 id="SQL-CREATETABLE-storage-parameters">
 686    <title id="SQL-CREATETABLE-storage-parameters-title">Storage Parameters</title>
 687
 688  <indexterm zone="sql-createtable-storage-parameters">
 689   <primary>storage parameters</primary>
 690  </indexterm>
 691
 692    <para>
 693     The <literal>WITH</> clause can specify <firstterm>storage parameters</>
 694     for tables, and for indexes associated with a <literal>UNIQUE</literal> or
 695     <literal>PRIMARY KEY</literal> constraint.  Storage parameters for
 696     indexes are documented in <xref linkend="SQL-CREATEINDEX"
 697     endterm="sql-createindex-title">.  The storage parameters currently
 698     available for tables are listed below.  For each parameter, there is an
 699     additional, identically named parameter, prefixed with
 700     <literal>toast.</literal> which can be used to control the behavior of the
 701     supplementary storage table, if any; see <xref linkend="storage-toast">.
 702     Note that the supplementary storage table inherits the
 703     <literal>autovacuum</literal> values from its parent table, if there are
 704     no <literal>toast.autovacuum_*</literal> settings set.
 705    </para>
 706
 707    <variablelist>
 708
 709    <varlistentry>
 710     <term><literal>fillfactor</>, <literal>toast.fillfactor</literal> (<type>integer</>)</term>
 711     <listitem>
 712      <para>
 713       The fillfactor for a table is a percentage between 10 and 100.
 714       100 (complete packing) is the default.  When a smaller fillfactor
 715       is specified, <command>INSERT</> operations pack table pages only
 716       to the indicated percentage; the remaining space on each page is
 717       reserved for updating rows on that page.  This gives <command>UPDATE</>
 718       a chance to place the updated copy of a row on the same page as the
 719       original, which is more efficient than placing it on a different page.
 720       For a table whose entries are never updated, complete packing is the
 721       best choice, but in heavily updated tables smaller fillfactors are
 722       appropriate.
 723      </para>
 724     </listitem>
 725    </varlistentry>
 726
 727    <varlistentry>
 728     <term><literal>autovacuum_enabled</>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</>)</term>
 729     <listitem>
 730      <para>
 731      Enables or disables the autovacuum daemon on a particular table.
 732      If true, the autovacuum daemon will initiate a <command>VACUUM</> operation
 733      on a particular table when the number of updated or deleted tuples exceeds
 734      <literal>autovacuum_vacuum_threshold</> plus
 735      <literal>autovacuum_vacuum_scale_factor</> times the number of live tuples
 736      currently estimated to be in the relation.
 737      Similarly, it will initiate an <command>ANALYZE</> operation when the
 738      number of inserted, updated or deleted tuples exceeds
 739      <literal>autovacuum_analyze_threshold</> plus
 740      <literal>autovacuum_analyze_scale_factor</> times the number of live tuples
 741      currently estimated to be in the relation.
 742      If false, this table will not be autovacuumed, except to prevent
 743      transaction Id wraparound. See <xref linkend="vacuum-for-wraparound"> for
 744      more about wraparound prevention.
 745      Observe that this variable inherits its value from the <xref
 746      linkend="guc-autovacuum"> setting.
 747      </para>
 748     </listitem>
 749    </varlistentry>
 750
 751    <varlistentry>
 752     <term><literal>autovacuum_vacuum_threshold</>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</>)</term>
 753     <listitem>
 754      <para>
 755      Minimum number of updated or deleted tuples before initiate a
 756      <command>VACUUM</> operation on a particular table.
 757      </para>
 758     </listitem>
 759    </varlistentry>
 760
 761    <varlistentry>
 762     <term><literal>autovacuum_vacuum_scale_factor</>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>float4</>)</term>
 763     <listitem>
 764      <para>
 765      Multiplier for <structfield>reltuples</> to add to
 766      <literal>autovacuum_vacuum_threshold</>.
 767      </para>
 768     </listitem>
 769    </varlistentry>
 770
 771    <varlistentry>
 772     <term><literal>autovacuum_analyze_threshold</>, <literal>toast.autovacuum_analyze_threshold</literal> (<type>integer</>)</term>
 773     <listitem>
 774      <para>
 775      Minimum number of inserted, updated, or deleted tuples before initiate an
 776      <command>ANALYZE</> operation on a particular table.
 777      </para>
 778     </listitem>
 779    </varlistentry>
 780
 781    <varlistentry>
 782     <term><literal>autovacuum_analyze_scale_factor</>, <literal>toast.autovacuum_analyze_scale_factor</literal> (<type>float4</>)</term>
 783     <listitem>
 784      <para>
 785      Multiplier for <structfield>reltuples</> to add to
 786      <literal>autovacuum_analyze_threshold</>.
 787      </para>
 788     </listitem>
 789    </varlistentry>
 790
 791    <varlistentry>
 792     <term><literal>autovacuum_vacuum_cost_delay</>, <literal>toast.autovacuum_vacuum_cost_delay</literal> (<type>integer</>)</term>
 793     <listitem>
 794      <para>
 795      Custom <xref linkend="guc-autovacuum-vacuum-cost-delay"> parameter.
 796      </para>
 797     </listitem>
 798    </varlistentry>
 799
 800    <varlistentry>
 801     <term><literal>autovacuum_vacuum_cost_limit</>, <literal>toast.autovacuum_vacuum_cost_limit</literal> (<type>integer</>)</term>
 802     <listitem>
 803      <para>
 804      Custom <xref linkend="guc-autovacuum-vacuum-cost-limit"> parameter.
 805      </para>
 806     </listitem>
 807    </varlistentry>
 808
 809    <varlistentry>
 810     <term><literal>autovacuum_freeze_min_age</>, <literal>toast.autovacuum_freeze_min_age</literal> (<type>integer</>)</term>
 811     <listitem>
 812      <para>
 813      Custom <xref linkend="guc-vacuum-freeze-min-age"> parameter. Note that
 814      autovacuum will ignore attempts to set a per-table
 815      <literal>autovacuum_freeze_min_age</> larger than the half system-wide
 816      <xref linkend="guc-autovacuum-freeze-max-age"> setting.
 817      </para>
 818     </listitem>
 819    </varlistentry>
 820
 821    <varlistentry>
 822     <term><literal>autovacuum_freeze_max_age</>, <literal>toast.autovacuum_freeze_max_age</literal> (<type>integer</>)</term>
 823     <listitem>
 824      <para>
 825      Custom <xref linkend="guc-autovacuum-freeze-max-age"> parameter. Note that
 826      autovacuum will ignore attempts to set a per-table
 827      <literal>autovacuum_freeze_max_age</> larger than the system-wide setting
 828      (it can only be set smaller). Note that while you can set
 829      <literal>autovacuum_freeze_max_age</> very small, or even zero, this is
 830      usually unwise since it will force frequent vacuuming.
 831      </para>
 832     </listitem>
 833    </varlistentry>
 834
 835    <varlistentry>
 836     <term><literal>autovacuum_freeze_table_age</literal> (<type>integer</type>)</term>
 837     <listitem>
 838      <para>
 839       Custom <xref linkend="guc-vacuum-freeze-table-age"> parameter.
 840      </para>
 841     </listitem>
 842    </varlistentry>
 843
 844    </variablelist>
 845
 846   </refsect2>
 847  </refsect1>
 848
 849  <refsect1 id="SQL-CREATETABLE-notes">
 850   <title>Notes</title>
 851
 852     <para>
 853      Using OIDs in new applications is not recommended: where
 854      possible, using a <literal>SERIAL</literal> or other sequence
 855      generator as the table's primary key is preferred. However, if
 856      your application does make use of OIDs to identify specific
 857      rows of a table, it is recommended to create a unique constraint
 858      on the <structfield>oid</> column of that table, to ensure that
 859      OIDs in the table will indeed uniquely identify rows even after
 860      counter wraparound.  Avoid assuming that OIDs are unique across
 861      tables; if you need a database-wide unique identifier, use the
 862      combination of <structfield>tableoid</> and row OID for the
 863      purpose.
 864     </para>
 865
 866     <tip>
 867      <para>
 868       The use of <literal>OIDS=FALSE</literal> is not recommended
 869       for tables with no primary key, since without either an OID or a
 870       unique data key, it is difficult to identify specific rows.
 871      </para>
 872     </tip>
 873
 874     <para>
 875      <productname>PostgreSQL</productname> automatically creates an
 876      index for each unique constraint and primary key constraint to
 877      enforce uniqueness.  Thus, it is not necessary to create an
 878      index explicitly for primary key columns.  (See <xref
 879      linkend="sql-createindex" endterm="sql-createindex-title"> for more information.)
 880     </para>
 881
 882     <para>
 883      Unique constraints and primary keys are not inherited in the
 884      current implementation.  This makes the combination of
 885      inheritance and unique constraints rather dysfunctional.
 886     </para>
 887
 888     <para>
 889      A table cannot have more than 1600 columns.  (In practice, the
 890      effective limit is usually lower because of tuple-length constraints.)
 891     </para>
 892
 893  </refsect1>
 894
 895
 896  <refsect1 id="SQL-CREATETABLE-examples">
 897   <title>Examples</title>
 898
 899   <para>
 900    Create table <structname>films</> and table
 901    <structname>distributors</>:
 902
 903 <programlisting>
 904 CREATE TABLE films (
 905     code        char(5) CONSTRAINT firstkey PRIMARY KEY,
 906     title       varchar(40) NOT NULL,
 907     did         integer NOT NULL,
 908     date_prod   date,
 909     kind        varchar(10),
 910     len         interval hour to minute
 911 );
 912 </programlisting>
 913
 914 <programlisting>
 915 CREATE TABLE distributors (
 916      did    integer PRIMARY KEY DEFAULT nextval('serial'),
 917      name   varchar(40) NOT NULL CHECK (name &lt;&gt; '')
 918 );
 919 </programlisting>
 920   </para>
 921
 922   <para>
 923    Create a table with a 2-dimensional array:
 924
 925 <programlisting>
 926 CREATE TABLE array_int (
 927     vector  int[][]
 928 );
 929 </programlisting>
 930   </para>
 931
 932   <para>
 933    Define a unique table constraint for the table
 934    <literal>films</literal>.  Unique table constraints can be defined
 935    on one or more columns of the table:
 936
 937 <programlisting>
 938 CREATE TABLE films (
 939     code        char(5),
 940     title       varchar(40),
 941     did         integer,
 942     date_prod   date,
 943     kind        varchar(10),
 944     len         interval hour to minute,
 945     CONSTRAINT production UNIQUE(date_prod)
 946 );
 947 </programlisting>
 948   </para>
 949
 950   <para>
 951    Define a check column constraint:
 952
 953 <programlisting>
 954 CREATE TABLE distributors (
 955     did     integer CHECK (did &gt; 100),
 956     name    varchar(40)
 957 );
 958 </programlisting>
 959   </para>
 960
 961   <para>
 962    Define a check table constraint:
 963
 964 <programlisting>
 965 CREATE TABLE distributors (
 966     did     integer,
 967     name    varchar(40)
 968     CONSTRAINT con1 CHECK (did &gt; 100 AND name &lt;&gt; '')
 969 );
 970 </programlisting>
 971   </para>
 972
 973   <para>
 974    Define a primary key table constraint for the table
 975    <structname>films</>:
 976
 977 <programlisting>
 978 CREATE TABLE films (
 979     code        char(5),
 980     title       varchar(40),
 981     did         integer,
 982     date_prod   date,
 983     kind        varchar(10),
 984     len         interval hour to minute,
 985     CONSTRAINT code_title PRIMARY KEY(code,title)
 986 );
 987 </programlisting>
 988   </para>
 989
 990   <para>
 991    Define a primary key constraint for table
 992    <structname>distributors</>.  The following two examples are
 993    equivalent, the first using the table constraint syntax, the second
 994    the column constraint syntax:
 995
 996 <programlisting>
 997 CREATE TABLE distributors (
 998     did     integer,
 999     name    varchar(40),
1000     PRIMARY KEY(did)
1001 );
1002 </programlisting>
1003
1004 <programlisting>
1005 CREATE TABLE distributors (
1006     did     integer PRIMARY KEY,
1007     name    varchar(40)
1008 );
1009 </programlisting>
1010   </para>
1011
1012   <para>
1013    Assign a literal constant default value for the column
1014    <literal>name</literal>, arrange for the default value of column
1015    <literal>did</literal> to be generated by selecting the next value
1016    of a sequence object, and make the default value of
1017    <literal>modtime</literal> be the time at which the row is
1018    inserted:
1019
1020 <programlisting>
1021 CREATE TABLE distributors (
1022     name      varchar(40) DEFAULT 'Luso Films',
1023     did       integer DEFAULT nextval('distributors_serial'),
1024     modtime   timestamp DEFAULT current_timestamp
1025 );
1026 </programlisting>
1027   </para>
1028
1029   <para>
1030    Define two <literal>NOT NULL</> column constraints on the table
1031    <classname>distributors</classname>, one of which is explicitly
1032    given a name:
1033
1034 <programlisting>
1035 CREATE TABLE distributors (
1036     did     integer CONSTRAINT no_null NOT NULL,
1037     name    varchar(40) NOT NULL
1038 );
1039 </programlisting>
1040     </para>
1041
1042     <para>
1043      Define a unique constraint for the <literal>name</literal> column:
1044
1045 <programlisting>
1046 CREATE TABLE distributors (
1047     did     integer,
1048     name    varchar(40) UNIQUE
1049 );
1050 </programlisting>
1051
1052      The same, specified as a table constraint:
1053
1054 <programlisting>
1055 CREATE TABLE distributors (
1056     did     integer,
1057     name    varchar(40),
1058     UNIQUE(name)
1059 );
1060 </programlisting>
1061   </para>
1062
1063   <para>
1064    Create the same table, specifying 70% fill factor for both the table
1065    and its unique index:
1066
1067 <programlisting>
1068 CREATE TABLE distributors (
1069     did     integer,
1070     name    varchar(40),
1071     UNIQUE(name) WITH (fillfactor=70)
1072 )
1073 WITH (fillfactor=70);
1074 </programlisting>
1075   </para>
1076
1077   <para>
1078    Create table <structname>cinemas</> in tablespace <structname>diskvol1</>:
1079
1080 <programlisting>
1081 CREATE TABLE cinemas (
1082         id serial,
1083         name text,
1084         location text
1085 ) TABLESPACE diskvol1;
1086 </programlisting>
1087   </para>
1088
1089  </refsect1>
1090
1091  <refsect1 id="SQL-CREATETABLE-compatibility">
1092   <title id="SQL-CREATETABLE-compatibility-title">Compatibility</title>
1093
1094   <para>
1095    The <command>CREATE TABLE</command> command conforms to the
1096    <acronym>SQL</acronym> standard, with exceptions listed below.
1097   </para>
1098
1099   <refsect2>
1100    <title>Temporary Tables</title>
1101
1102    <para>
1103     Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
1104     resembles that of the SQL standard, the effect is not the same.  In the
1105     standard,
1106     temporary tables are defined just once and automatically exist (starting
1107     with empty contents) in every session that needs them.
1108     <productname>PostgreSQL</productname> instead
1109     requires each session to issue its own <literal>CREATE TEMPORARY
1110     TABLE</literal> command for each temporary table to be used.  This allows
1111     different sessions to use the same temporary table name for different
1112     purposes, whereas the standard's approach constrains all instances of a
1113     given temporary table name to have the same table structure.
1114    </para>
1115
1116    <para>
1117     The standard's definition of the behavior of temporary tables is
1118     widely ignored.  <productname>PostgreSQL</productname>'s behavior
1119     on this point is similar to that of several other SQL databases.
1120    </para>
1121
1122    <para>
1123     The standard's distinction between global and local temporary tables
1124     is not in <productname>PostgreSQL</productname>, since that distinction
1125     depends on the concept of modules, which
1126     <productname>PostgreSQL</productname> does not have.
1127     For compatibility's sake, <productname>PostgreSQL</productname> will
1128     accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
1129     in a temporary table declaration, but they have no effect.
1130    </para>
1131
1132    <para>
1133     The <literal>ON COMMIT</literal> clause for temporary tables
1134     also resembles the SQL standard, but has some differences.
1135     If the <literal>ON COMMIT</> clause is omitted, SQL specifies that the
1136     default behavior is <literal>ON COMMIT DELETE ROWS</>.  However, the
1137     default behavior in <productname>PostgreSQL</productname> is
1138     <literal>ON COMMIT PRESERVE ROWS</literal>.  The <literal>ON COMMIT
1139     DROP</literal> option does not exist in SQL.
1140    </para>
1141   </refsect2>
1142
1143   <refsect2>
1144    <title>Column Check Constraints</title>
1145
1146    <para>
1147     The SQL standard says that <literal>CHECK</> column constraints
1148     can only refer to the column they apply to; only <literal>CHECK</>
1149     table constraints can refer to multiple columns.
1150     <productname>PostgreSQL</productname> does not enforce this
1151     restriction; it treats column and table check constraints alike.
1152    </para>
1153   </refsect2>
1154
1155   <refsect2>
1156    <title><literal>NULL</literal> <quote>Constraint</quote></title>
1157
1158    <para>
1159     The <literal>NULL</> <quote>constraint</quote> (actually a
1160     non-constraint) is a <productname>PostgreSQL</productname>
1161     extension to the SQL standard that is included for compatibility with some
1162     other database systems (and for symmetry with the <literal>NOT
1163     NULL</literal> constraint).  Since it is the default for any
1164     column, its presence is simply noise.
1165    </para>
1166   </refsect2>
1167
1168   <refsect2>
1169    <title>Inheritance</title>
1170
1171    <para>
1172     Multiple inheritance via the <literal>INHERITS</literal> clause is
1173     a <productname>PostgreSQL</productname> language extension.
1174     SQL:1999 and later define single inheritance using a
1175     different syntax and different semantics.  SQL:1999-style
1176     inheritance is not yet supported by
1177     <productname>PostgreSQL</productname>.
1178    </para>
1179   </refsect2>
1180
1181   <refsect2>
1182    <title>Zero-column tables</title>
1183
1184    <para>
1185     <productname>PostgreSQL</productname> allows a table of no columns
1186     to be created (for example, <literal>CREATE TABLE foo();</>).  This
1187     is an extension from the SQL standard, which does not allow zero-column
1188     tables.  Zero-column tables are not in themselves very useful, but
1189     disallowing them creates odd special cases for <command>ALTER TABLE
1190     DROP COLUMN</>, so it seems cleaner to ignore this spec restriction.
1191    </para>
1192   </refsect2>
1193
1194   <refsect2>
1195    <title><literal>WITH</> clause</title>
1196
1197    <para>
1198     The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
1199     extension; neither storage parameters nor OIDs are in the standard.
1200    </para>
1201   </refsect2>
1202
1203   <refsect2>
1204    <title>Tablespaces</title>
1205
1206    <para>
1207     The <productname>PostgreSQL</productname> concept of tablespaces is not
1208     part of the standard.  Hence, the clauses <literal>TABLESPACE</literal>
1209     and <literal>USING INDEX TABLESPACE</literal> are extensions.
1210    </para>
1211   </refsect2>
1212  </refsect1>
1213
1214
1215  <refsect1>
1216   <title>See Also</title>
1217
1218   <simplelist type="inline">
1219    <member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
1220    <member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
1221    <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
1222   </simplelist>
1223  </refsect1>
1224 </refentry>