| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.38 2006/09/16 00:30:20 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-SELECTINTO"> |
| <refmeta> |
| <refentrytitle id="SQL-SELECTINTO-TITLE">SELECT INTO</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>SELECT INTO</refname> |
| <refpurpose>define a new table from the results of a query</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-selectinto"> |
| <primary>SELECT INTO</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ] |
| * | <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...] |
| INTO [ TEMPORARY | TEMP ] [ 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">group_elem</replaceable> [, ...] ] |
| [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ] |
| [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ] |
| [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ] |
| [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ] |
| [ OFFSET <replaceable class="PARAMETER">start</replaceable> ] |
| [ 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" |
| endterm="sql-createtable-title"> 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" endterm="sql-select-title">. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| <xref linkend="sql-createtableas" |
| endterm="sql-createtableas-title"> 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> |
| Prior to <productname>PostgreSQL</> 8.1, the table created by |
| <command>SELECT INTO</command> included OIDs by default. In |
| <productname>PostgreSQL</productname> 8.1, this is not the case |
| — to include OIDs in the new table, the <xref |
| linkend="guc-default-with-oids"> configuration variable must be |
| enabled. Alternatively, <command>CREATE TABLE AS</command> can be |
| used with the <literal>WITH OIDS</literal> clause. |
| </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" endterm="sql-createtableas-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |