diff options
Diffstat (limited to 'doc/src/sgml/ref/select_into.sgml')
-rw-r--r-- | doc/src/sgml/ref/select_into.sgml | 154 |
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 >= '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> |