granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/create_table_as.sgml

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