summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/ref/select_into.sgml
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/ref/select_into.sgml')
-rw-r--r--doc/src/sgml/ref/select_into.sgml154
1 files changed, 154 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml
new file mode 100644
index 0000000..e82e416
--- /dev/null
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -0,0 +1,154 @@
+<!--
+doc/src/sgml/ref/select_into.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-selectinto">
+ <indexterm zone="sql-selectinto">
+ <primary>SELECT INTO</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>SELECT INTO</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SELECT INTO</refname>
+ <refpurpose>define a new table from the results of a query</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
+ * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
+ INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] <replaceable class="parameter">new_table</replaceable>
+ [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
+ [ WHERE <replaceable class="parameter">condition</replaceable> ]
+ [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
+ [ HAVING <replaceable class="parameter">condition</replaceable> ]
+ [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
+ [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
+ [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
+ [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
+ [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
+ [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>SELECT INTO</command> creates a new table and fills it
+ with data computed by a query. The data is not returned to the
+ client, as it is with a normal <command>SELECT</command>. The new
+ table's columns have the names and data types associated with the
+ output columns of the <command>SELECT</command>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
+ <listitem>
+ <para>
+ If specified, the table is created as a temporary table. Refer
+ to <xref linkend="sql-createtable"/> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>UNLOGGED</literal></term>
+ <listitem>
+ <para>
+ If specified, the table is created as an unlogged table. Refer
+ to <xref linkend="sql-createtable"/> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_table</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the table to be created.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ All other parameters are described in detail under <xref
+ linkend="sql-select"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ <xref linkend="sql-createtableas"/> is functionally similar to
+ <command>SELECT INTO</command>. <command>CREATE TABLE AS</command>
+ is the recommended syntax, since this form of <command>SELECT
+ INTO</command> is not available in <application>ECPG</application>
+ or <application>PL/pgSQL</application>, because they interpret the
+ <literal>INTO</literal> clause differently. Furthermore,
+ <command>CREATE TABLE AS</command> offers a superset of the
+ functionality provided by <command>SELECT INTO</command>.
+ </para>
+
+ <para>
+ In contrast to <command>CREATE TABLE AS</command>, <command>SELECT
+ INTO</command> does not allow to specify properties like a table's access
+ method with <xref linkend="sql-createtable-method" /> or the table's
+ tablespace with <xref linkend="sql-createtable-tablespace" />. Use <xref
+ linkend="sql-createtableas"/> if necessary. Therefore, the default table
+ access method is chosen for the new table. See <xref
+ linkend="guc-default-table-access-method"/> for more information.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create a new table <literal>films_recent</literal> consisting of only
+ recent entries from the table <literal>films</literal>:
+
+<programlisting>
+SELECT * INTO films_recent FROM films WHERE date_prod &gt;= '2002-01-01';
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The SQL standard uses <command>SELECT INTO</command> to
+ represent selecting values into scalar variables of a host program,
+ rather than creating a new table. This indeed is the usage found
+ in <application>ECPG</application> (see <xref linkend="ecpg"/>) and
+ <application>PL/pgSQL</application> (see <xref linkend="plpgsql"/>).
+ The <productname>PostgreSQL</productname> usage of <command>SELECT
+ INTO</command> to represent table creation is historical. It is
+ best to use <command>CREATE TABLE AS</command> for this purpose in
+ new code.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createtableas"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>