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