2 $PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.28 2004/03/09 16:57:47 neilc Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-SELECTINTO">
8 <refentrytitle id="SQL-SELECTINTO-TITLE">SELECT INTO</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>SELECT INTO</refname>
14 <refpurpose>create a new table from the results of a query</refpurpose>
17 <indexterm zone="sql-selectinto">
18 <primary>SELECT INTO</primary>
23 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
24 * | <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
25 INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable>
26 [ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
27 [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
28 [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
29 [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
30 [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
31 [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
32 [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
33 [ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
34 [ FOR UPDATE [ OF <replaceable class="PARAMETER">tablename</replaceable> [, ...] ] ]
39 <title>Description</title>
42 <command>SELECT INTO</command> creates a new table and fills it
43 with data computed by a query. The data is not returned to the
44 client, as it is with a normal <command>SELECT</command>. The new
45 table's columns have the names and data types associated with the
46 output columns of the <command>SELECT</command>.
51 <title>Parameters</title>
55 <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
58 If specified, the table is created as a temporary table. Refer
59 to <xref linkend="sql-createtable"
60 endterm="sql-createtable-title"> for details.
66 <term><replaceable class="PARAMETER">new_table</replaceable></term>
69 The name (optionally schema-qualified) of the table to be created.
76 All other parameters are described in detail under <xref
77 linkend="sql-select" endterm="sql-select-title">.
85 <xref linkend="sql-createtableas"
86 endterm="sql-createtableas-title"> is functionally similar to
87 <command>SELECT INTO</command>. <command>CREATE TABLE AS</command>
88 is the recommended syntax, since this form of <command>SELECT
89 INTO</command> is not available in <application>ECPG</application>
90 or <application>PL/pgSQL</application>, because they interpret the
91 <literal>INTO</literal> clause differently. Furthermore,
92 <command>CREATE TABLE AS</command> offers a superset of the
93 functionality provided by <command>SELECT INTO</command>.
97 Prior to <productname>PostgreSQL</> 7.5, the table created by
98 <command>SELECT INTO</command> always included OIDs. Furthermore,
99 these OIDs were newly generated: they were distinct from the OIDs
100 of any of the rows in the source tables of the <command>SELECT
101 INTO</command> statement. Therefore, if <command>SELECT
102 INTO</command> was frequently executed, the OID counter would be
103 rapidly incremented. As of <productname>PostgreSQL</> 7.5, the
104 inclusion of OIDs in the table created by <command>SELECT
105 INTO</command> is controlled by the
106 <xref linkend="guc-default-with-oids"> configuration variable. This
107 variable currently defaults to true, but will likely default to
108 false in a future release of <productname>PostgreSQL</>.
113 <title>Compatibility</title>
116 The SQL standard uses <command>SELECT INTO</command> to
117 represent selecting values into scalar variables of a host program,
118 rather than creating a new table. This indeed is the usage found
119 in <application>ECPG</application> (see <xref linkend="ecpg">) and
120 <application>PL/pgSQL</application> (see <xref linkend="plpgsql">).
121 The <productname>PostgreSQL</productname> usage of <command>SELECT
122 INTO</command> to represent table creation is historical. It's
123 best to use <command>CREATE TABLE AS</command> for this purpose in
124 new code. (<command>CREATE TABLE AS</command> isn't standard
125 either, but it's less likely to cause confusion.)
130 <!-- Keep this comment at the end of the file
135 sgml-minimize-attributes:nil
136 sgml-always-quote-attributes:t
139 sgml-parent-document:nil
140 sgml-default-dtd-file:"../reference.ced"
141 sgml-exposed-tags:nil
142 sgml-local-catalogs:"/usr/lib/sgml/catalog"
143 sgml-local-ecat-files:nil