1<!--
2doc/src/sgml/ref/select_into.sgml
3PostgreSQL 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> [, ...] ]
25SELECT [ 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   <xref linkend="sql-createtableas"> 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   To add OIDs to the table created by <command>SELECT INTO</command>,
110   enable the <xref linkend="guc-default-with-oids"> configuration
111   variable.  Alternatively, <command>CREATE TABLE AS</command> can be
112   used with the <literal>WITH OIDS</literal> clause.
113  </para>
114 </refsect1>
115
116 <refsect1>
117  <title>Examples</title>
118
119  <para>
120   Create a new table <literal>films_recent</literal> consisting of only
121   recent entries from the table <literal>films</literal>:
122
123<programlisting>
124SELECT * INTO films_recent FROM films WHERE date_prod &gt;= '2002-01-01';
125</programlisting></para>
126 </refsect1>
127
128 <refsect1>
129  <title>Compatibility</title>
130
131  <para>
132   The SQL standard uses <command>SELECT INTO</command> to
133   represent selecting values into scalar variables of a host program,
134   rather than creating a new table.  This indeed is the usage found
135   in <application>ECPG</application> (see <xref linkend="ecpg">) and
136   <application>PL/pgSQL</application> (see <xref linkend="plpgsql">).
137   The <productname>PostgreSQL</productname> usage of <command>SELECT
138   INTO</command> to represent table creation is historical.  It is
139   best to use <command>CREATE TABLE AS</command> for this purpose in
140   new code.
141  </para>
142 </refsect1>
143
144 <refsect1>
145  <title>See Also</title>
146
147  <simplelist type="inline">
148   <member><xref linkend="sql-createtableas"></member>
149  </simplelist>
150 </refsect1>
151</refentry>
152