doc/src/sgml/ref/select_into.sgml

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