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   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>SELECT INTO</refname>
  15   <refpurpose>define a new table from the results of a query</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-selectinto">
  19   <primary>SELECT INTO</primary>
  20  </indexterm>
  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 ] [ 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 ] <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"
  64      endterm="sql-createtable-title"> for details.
  65     </para>
  66    </listitem>
  67   </varlistentry>
  68
  69    <varlistentry>
  70     <term><replaceable class="PARAMETER">new_table</replaceable></term>
  71     <listitem>
  72      <para>
  73       The name (optionally schema-qualified) of the table to be created.
  74      </para>
  75     </listitem>
  76    </varlistentry>
  77   </variablelist>
  78
  79   <para>
  80    All other parameters are described in detail under <xref
  81    linkend="sql-select" endterm="sql-select-title">.
  82   </para>
  83  </refsect1>
  84
  85  <refsect1>
  86   <title>Notes</title>
  87
  88   <para>
  89    <xref linkend="sql-createtableas"
  90    endterm="sql-createtableas-title"> is functionally similar to
  91    <command>SELECT INTO</command>.  <command>CREATE TABLE AS</command>
  92    is the recommended syntax, since this form of <command>SELECT
  93    INTO</command> is not available in <application>ECPG</application>
  94    or <application>PL/pgSQL</application>, because they interpret the
  95    <literal>INTO</literal> clause differently. Furthermore,
  96    <command>CREATE TABLE AS</command> offers a superset of the
  97    functionality provided by <command>SELECT INTO</command>.
  98   </para>
  99
 100   <para>
 101    Prior to <productname>PostgreSQL</> 8.1, the table created by
 102    <command>SELECT INTO</command> included OIDs by default. In
 103    <productname>PostgreSQL</productname> 8.1, this is not the case
 104    &mdash; to include OIDs in the new table, the <xref
 105    linkend="guc-default-with-oids"> configuration variable must be
 106    enabled. Alternatively, <command>CREATE TABLE AS</command> can be
 107    used with the <literal>WITH OIDS</literal> clause.
 108   </para>
 109  </refsect1>
 110
 111  <refsect1>
 112   <title>Examples</title>
 113
 114   <para>
 115    Create a new table <literal>films_recent</literal> consisting of only
 116    recent entries from the table <literal>films</literal>:
 117
 118 <programlisting>
 119 SELECT * INTO films_recent FROM films WHERE date_prod &gt;= '2002-01-01';
 120 </programlisting>
 121   </para>
 122  </refsect1>
 123
 124  <refsect1>
 125   <title>Compatibility</title>
 126
 127   <para>
 128    The SQL standard uses <command>SELECT INTO</command> to
 129    represent selecting values into scalar variables of a host program,
 130    rather than creating a new table.  This indeed is the usage found
 131    in <application>ECPG</application> (see <xref linkend="ecpg">) and
 132    <application>PL/pgSQL</application> (see <xref linkend="plpgsql">).
 133    The <productname>PostgreSQL</productname> usage of <command>SELECT
 134    INTO</command> to represent table creation is historical.  It is
 135    best to use <command>CREATE TABLE AS</command> for this purpose in
 136    new code.
 137   </para>
 138  </refsect1>
 139
 140  <refsect1>
 141   <title>See Also</title>
 142
 143   <simplelist type="inline">
 144    <member><xref linkend="sql-createtableas" endterm="sql-createtableas-title"></member>
 145   </simplelist>
 146  </refsect1>
 147 </refentry>