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 >= '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