doc/src/sgml/ref/select_into.sgml

   1 <!--
   2 doc/src/sgml/ref/select_into.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-selectinto">
   7  <indexterm zone="sql-selectinto">
   8   <primary>SELECT INTO</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>SELECT INTO</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>SELECT INTO</refname>
  19   <refpurpose>define a new table from the results of a query</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
  25 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
  26     [ { * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
  27     INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] <replaceable class="parameter">new_table</replaceable>
  28     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
  29     [ WHERE <replaceable class="parameter">condition</replaceable> ]
  30     [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
  31     [ HAVING <replaceable class="parameter">condition</replaceable> ]
  32     [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
  33     [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
  34     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
  35     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
  36     [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
  37     [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
  38     [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
  39 </synopsis>
  40  </refsynopsisdiv>
  41
  42  <refsect1>
  43   <title>Description</title>
  44
  45   <para>
  46    <command>SELECT INTO</command> creates a new table and fills it
  47    with data computed by a query.  The data is not returned to the
  48    client, as it is with a normal <command>SELECT</command>.  The new
  49    table's columns have the names and data types associated with the
  50    output columns of the <command>SELECT</command>.
  51   </para>
  52  </refsect1>
  53
  54  <refsect1>
  55   <title>Parameters</title>
  56
  57   <variablelist>
  58   <varlistentry>
  59    <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
  60    <listitem>
  61     <para>
  62      If specified, the table is created as a temporary table.  Refer
  63      to <xref linkend="sql-createtable"/> for details.
  64     </para>
  65    </listitem>
  66   </varlistentry>
  67
  68   <varlistentry>
  69    <term><literal>UNLOGGED</literal></term>
  70    <listitem>
  71     <para>
  72      If specified, the table is created as an unlogged table.  Refer
  73      to <xref linkend="sql-createtable"/> for details.
  74     </para>
  75    </listitem>
  76   </varlistentry>
  77
  78    <varlistentry>
  79     <term><replaceable class="parameter">new_table</replaceable></term>
  80     <listitem>
  81      <para>
  82       The name (optionally schema-qualified) of the table to be created.
  83      </para>
  84     </listitem>
  85    </varlistentry>
  86   </variablelist>
  87
  88   <para>
  89    All other parameters are described in detail under <xref
  90    linkend="sql-select"/>.
  91   </para>
  92  </refsect1>
  93
  94  <refsect1>
  95   <title>Notes</title>
  96
  97   <para>
  98    <link linkend="sql-createtableas"><command>CREATE TABLE AS</command></link> is functionally similar to
  99    <command>SELECT INTO</command>.  <command>CREATE TABLE AS</command>
 100    is the recommended syntax, since this form of <command>SELECT
 101    INTO</command> is not available in <application>ECPG</application>
 102    or <application>PL/pgSQL</application>, because they interpret the
 103    <literal>INTO</literal> clause differently. Furthermore,
 104    <command>CREATE TABLE AS</command> offers a superset of the
 105    functionality provided by <command>SELECT INTO</command>.
 106   </para>
 107
 108   <para>
 109    In contrast to <command>CREATE TABLE AS</command>, <command>SELECT
 110    INTO</command> does not allow specifying properties like a table's access
 111    method with <xref linkend="sql-createtable-method" /> or the table's
 112    tablespace with <xref linkend="sql-createtable-tablespace" />. Use
 113    <command>CREATE TABLE AS</command> if necessary.  Therefore, the default table
 114    access method is chosen for the new table. See <xref
 115    linkend="guc-default-table-access-method"/> for more information.
 116   </para>
 117  </refsect1>
 118
 119  <refsect1>
 120   <title>Examples</title>
 121
 122   <para>
 123    Create a new table <literal>films_recent</literal> consisting of only
 124    recent entries from the table <literal>films</literal>:
 125
 126 <programlisting>
 127 SELECT * INTO films_recent FROM films WHERE date_prod &gt;= '2002-01-01';
 128 </programlisting></para>
 129  </refsect1>
 130
 131  <refsect1>
 132   <title>Compatibility</title>
 133
 134   <para>
 135    The SQL standard uses <command>SELECT INTO</command> to
 136    represent selecting values into scalar variables of a host program,
 137    rather than creating a new table.  This indeed is the usage found
 138    in <application>ECPG</application> (see <xref linkend="ecpg"/>) and
 139    <application>PL/pgSQL</application> (see <xref linkend="plpgsql"/>).
 140    The <productname>PostgreSQL</productname> usage of <command>SELECT
 141    INTO</command> to represent table creation is historical.  Some other SQL
 142    implementations also use <command>SELECT INTO</command> in this way (but
 143    most SQL implementations support <command>CREATE TABLE AS</command>
 144    instead).  Apart from such compatibility considerations, it is best to use
 145    <command>CREATE TABLE AS</command> for this purpose in new code.
 146   </para>
 147  </refsect1>
 148
 149  <refsect1>
 150   <title>See Also</title>
 151
 152   <simplelist type="inline">
 153    <member><xref linkend="sql-createtableas"/></member>
 154   </simplelist>
 155  </refsect1>
 156 </refentry>