doc/src/sgml/ref/create_table_as.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATETABLEAS">
   7  <refmeta>
   8   <refentrytitle id="sql-createtableas-title">CREATE TABLE AS</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE TABLE AS</refname>
  15   <refpurpose>define a new table from the results of a query</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createtableas">
  19   <primary>CREATE TABLE AS</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable>
  25     [ (<replaceable>column_name</replaceable> [, ...] ) ]
  26     [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
  27     [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  28     [ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
  29     AS <replaceable>query</replaceable>
  30     [ WITH [ NO ] DATA ]
  31 </synopsis>
  32  </refsynopsisdiv>
  33
  34  <refsect1>
  35   <title>Description</title>
  36
  37   <para>
  38    <command>CREATE TABLE AS</command> creates a table and fills it
  39    with data computed by a <command>SELECT</command> command.
  40    The table columns have the
  41    names and data types associated with the output columns of the
  42    <command>SELECT</command> (except that you can override the column
  43    names by giving an explicit list of new column names).
  44   </para>
  45
  46   <para>
  47    <command>CREATE TABLE AS</command> bears some resemblance to
  48    creating a view, but it is really quite different: it creates a new
  49    table and evaluates the query just once to fill the new table
  50    initially.  The new table will not track subsequent changes to the
  51    source tables of the query.  In contrast, a view re-evaluates its
  52    defining <command>SELECT</command> statement whenever it is
  53    queried.
  54   </para>
  55  </refsect1>
  56
  57  <refsect1>
  58   <title>Parameters</title>
  59
  60   <variablelist>
  61    <varlistentry>
  62     <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
  63     <listitem>
  64      <para>
  65       Ignored for compatibility. Refer to <xref
  66       linkend="sql-createtable" endterm="sql-createtable-title"> for
  67       details.
  68      </para>
  69     </listitem>
  70    </varlistentry>
  71   </variablelist>
  72
  73   <variablelist>
  74    <varlistentry>
  75     <term><literal>TEMPORARY</> or <literal>TEMP</></term>
  76     <listitem>
  77      <para>
  78       If specified, the table is created as a temporary table.
  79       Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> for details.
  80      </para>
  81     </listitem>
  82    </varlistentry>
  83
  84    <varlistentry>
  85     <term><replaceable>table_name</replaceable></term>
  86     <listitem>
  87      <para>
  88       The name (optionally schema-qualified) of the table to be created.
  89      </para>
  90     </listitem>
  91    </varlistentry>
  92
  93    <varlistentry>
  94     <term><replaceable>column_name</replaceable></term>
  95     <listitem>
  96      <para>
  97       The name of a column in the new table.  If column names are not
  98       provided, they are taken from the output column names of the
  99       query.  If the table is created from an
 100       <command>EXECUTE</command> command, a column name list cannot be
 101       specified.
 102      </para>
 103     </listitem>
 104    </varlistentry>
 105
 106    <varlistentry>
 107     <term><literal>WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term>
 108     <listitem>
 109      <para>
 110       This clause specifies optional storage parameters for the new table;
 111       see <xref linkend="sql-createtable-storage-parameters"
 112       endterm="sql-createtable-storage-parameters-title"> for more
 113       information.  The <literal>WITH</> clause
 114       can also include <literal>OIDS=TRUE</> (or just <literal>OIDS</>)
 115       to specify that rows of the new table
 116       should have OIDs (object identifiers) assigned to them, or
 117       <literal>OIDS=FALSE</> to specify that the rows should not have OIDs.
 118       See <xref linkend="sql-createtable"
 119       endterm="sql-createtable-title"> for more information.
 120      </para>
 121     </listitem>
 122    </varlistentry>
 123
 124    <varlistentry>
 125     <term><literal>WITH OIDS</></term>
 126     <term><literal>WITHOUT OIDS</></term>
 127     <listitem>
 128      <para>
 129       These are obsolescent syntaxes equivalent to <literal>WITH (OIDS)</>
 130       and <literal>WITH (OIDS=FALSE)</>, respectively.  If you wish to give
 131       both an <literal>OIDS</> setting and storage parameters, you must use
 132       the <literal>WITH ( ... )</> syntax; see above.
 133      </para>
 134     </listitem>
 135    </varlistentry>
 136
 137    <varlistentry>
 138     <term><literal>ON COMMIT</literal></term>
 139     <listitem>
 140      <para>
 141       The behavior of temporary tables at the end of a transaction
 142       block can be controlled using <literal>ON COMMIT</literal>.
 143       The three options are:
 144
 145       <variablelist>
 146        <varlistentry>
 147         <term><literal>PRESERVE ROWS</literal></term>
 148         <listitem>
 149          <para>
 150           No special action is taken at the ends of transactions.
 151           This is the default behavior.
 152          </para>
 153         </listitem>
 154        </varlistentry>
 155
 156        <varlistentry>
 157         <term><literal>DELETE ROWS</literal></term>
 158         <listitem>
 159          <para>
 160           All rows in the temporary table will be deleted at the end
 161           of each transaction block.  Essentially, an automatic <xref
 162           linkend="sql-truncate" endterm="sql-truncate-title"> is done
 163           at each commit.
 164          </para>
 165         </listitem>
 166        </varlistentry>
 167
 168        <varlistentry>
 169         <term><literal>DROP</literal></term>
 170         <listitem>
 171          <para>
 172           The temporary table will be dropped at the end of the current
 173           transaction block.
 174          </para>
 175         </listitem>
 176        </varlistentry>
 177       </variablelist>
 178      </para>
 179     </listitem>
 180    </varlistentry>
 181
 182    <varlistentry>
 183     <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
 184     <listitem>
 185      <para>
 186       The <replaceable class="PARAMETER">tablespace</replaceable> is the name
 187       of the tablespace in which the new table is to be created.
 188       If not specified,
 189       <xref linkend="guc-default-tablespace"> is consulted, or
 190       <xref linkend="guc-temp-tablespaces"> if the table is temporary.
 191      </para>
 192     </listitem>
 193    </varlistentry>
 194
 195    <varlistentry>
 196     <term><replaceable>query</replaceable></term>
 197     <listitem>
 198      <para>
 199       A <xref linkend="sql-select"
 200       endterm="sql-select-title">, <link linkend="sql-table">TABLE</link>,
 201       or
 202       <xref linkend="sql-values" endterm="sql-values-title"> command,
 203       or an <xref linkend="sql-execute" endterm="sql-execute-title"> command
 204       that runs a prepared <command>SELECT</>, <command>TABLE</>, or <command>VALUES</> query.
 205      </para>
 206     </listitem>
 207    </varlistentry>
 208
 209    <varlistentry>
 210     <term><literal>WITH [ NO ] DATA</></term>
 211     <listitem>
 212      <para>
 213       This clause specifies whether or not the data produced by the query
 214       should be copied into the new table.  If not, only the table structure
 215       is copied.  The default is to copy the data.
 216      </para>
 217     </listitem>
 218    </varlistentry>
 219
 220   </variablelist>
 221  </refsect1>
 222
 223  <refsect1>
 224   <title>Notes</title>
 225
 226   <para>
 227    This command is functionally similar to <xref
 228    linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is
 229    preferred since it is less likely to be confused with other uses of
 230    the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE
 231    TABLE AS</command> offers a superset of the functionality offered
 232    by <command>SELECT INTO</command>.
 233   </para>
 234
 235   <para>
 236    Prior to <productname>PostgreSQL</productname> 8.0, <command>CREATE
 237    TABLE AS</command> always included OIDs in the table it
 238    created.  As of <productname>PostgreSQL</productname> 8.0,
 239    the <command>CREATE TABLE AS</command> command allows the user to
 240    explicitly specify whether OIDs should be included. If the
 241    presence of OIDs is not explicitly specified,
 242    the <xref linkend="guc-default-with-oids"> configuration variable is
 243    used.  As of <productname>PostgreSQL</productname> 8.1,
 244    this variable is false by default, so the default behavior is not
 245    identical to pre-8.0 releases.  Applications that
 246    require OIDs in the table created by <command>CREATE TABLE
 247    AS</command> should explicitly specify <literal>WITH (OIDS)</literal>
 248    to ensure proper behavior.
 249   </para>
 250  </refsect1>
 251
 252  <refsect1>
 253   <title>Examples</title>
 254
 255   <para>
 256    Create a new table <literal>films_recent</literal> consisting of only
 257    recent entries from the table <literal>films</literal>:
 258
 259 <programlisting>
 260 CREATE TABLE films_recent AS
 261   SELECT * FROM films WHERE date_prod &gt;= '2002-01-01';
 262 </programlisting>
 263   </para>
 264
 265   <para>
 266    To copy a table completely, the short form using
 267    the <literal>TABLE</literal> command can also be used:
 268
 269 <programlisting>
 270 CREATE TABLE films2 AS
 271   TABLE films;
 272 </programlisting>
 273   </para>
 274
 275   <para>
 276    Create a new temporary table <literal>films_recent</literal>, consisting of
 277    only recent entries from the table <literal>films</literal>, using a
 278    prepared statement.  The new table has OIDs and will be dropped at commit:
 279
 280 <programlisting>
 281 PREPARE recentfilms(date) AS
 282   SELECT * FROM films WHERE date_prod &gt; $1;
 283 CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
 284   EXECUTE recentfilms('2002-01-01');
 285 </programlisting>
 286   </para>
 287  </refsect1>
 288
 289  <refsect1>
 290   <title>Compatibility</title>
 291
 292   <para>
 293    <command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
 294    standard.  The following are nonstandard extensions:
 295
 296    <itemizedlist spacing="compact">
 297     <listitem>
 298      <para>
 299       The standard requires parentheses around the subquery clause; in
 300       <productname>PostgreSQL</productname>, these parentheses are
 301       optional.
 302      </para>
 303     </listitem>
 304
 305     <listitem>
 306      <para>
 307       In the standard, the <literal>WITH [ NO ] DATA</literal> clause
 308       is required; in PostgreSQL it is optional.
 309      </para>
 310     </listitem>
 311
 312     <listitem>
 313      <para>
 314       <productname>PostgreSQL</> handles temporary tables in a way
 315       rather different from the standard; see
 316       <xref linkend="sql-createtable" endterm="sql-createtable-title">
 317       for details.
 318      </para>
 319     </listitem>
 320
 321     <listitem>
 322      <para>
 323       The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
 324       extension; neither storage parameters nor OIDs are in the standard.
 325      </para>
 326     </listitem>
 327
 328     <listitem>
 329      <para>
 330       The <productname>PostgreSQL</productname> concept of tablespaces is not
 331       part of the standard.  Hence, the clause <literal>TABLESPACE</literal>
 332       is an extension.
 333      </para>
 334     </listitem>
 335    </itemizedlist>
 336   </para>
 337  </refsect1>
 338
 339  <refsect1>
 340   <title>See Also</title>
 341
 342   <simplelist type="inline">
 343    <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
 344    <member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
 345    <member><xref linkend="sql-select" endterm="sql-select-title"></member>
 346    <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
 347    <member><xref linkend="sql-values" endterm="sql-values-title"></member>
 348   </simplelist>
 349  </refsect1>
 350
 351 </refentry>