blob: ed8d7449c93cc6508683775fb15dc5915407d4aa [file] [log] [blame]
$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">
<refentrytitle id="SQL-SELECTINTO-TITLE">SELECT INTO</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
<refname>SELECT INTO</refname>
<refpurpose>define a new table from the results of a query</refpurpose>
<indexterm zone="sql-selectinto">
<primary>SELECT INTO</primary>
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 ] [...] ]
<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>.
<term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
If specified, the table is created as a temporary table. Refer
to <xref linkend="sql-createtable"
endterm="sql-createtable-title"> for details.
<term><replaceable class="PARAMETER">new_table</replaceable></term>
The name (optionally schema-qualified) of the table to be created.
All other parameters are described in detail under <xref
linkend="sql-select" endterm="sql-select-title">.
<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>.
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
&mdash; 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.
Create a new table <literal>films_recent</literal> consisting of only
recent entries from the table <literal>films</literal>:
SELECT * INTO films_recent FROM films WHERE date_prod &gt;= '2002-01-01';
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.
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-createtableas" endterm="sql-createtableas-title"></member>