1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SELECT INTO</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-select.html" title="SELECT">
<link rel="next" href="sql-set.html" title="SET">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-selectinto"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>SELECT INTO — define a new table from the results of a query</p>
</div>
<a name="id786610"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">SELECT [ ALL | DISTINCT [ ON ( <em class="replaceable"><code>expression</code></em> [, ...] ) ] ]
* | <em class="replaceable"><code>expression</code></em> [ AS <em class="replaceable"><code>output_name</code></em> ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] <em class="replaceable"><code>new_table</code></em>
[ FROM <em class="replaceable"><code>from_item</code></em> [, ...] ]
[ WHERE <em class="replaceable"><code>condition</code></em> ]
[ GROUP BY <em class="replaceable"><code>expression</code></em> [, ...] ]
[ HAVING <em class="replaceable"><code>condition</code></em> [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <em class="replaceable"><code>select</code></em> ]
[ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [, ...] ]
[ LIMIT { <em class="replaceable"><code>count</code></em> | ALL } ]
[ OFFSET <em class="replaceable"><code>start</code></em> ]
[ FOR { UPDATE | SHARE } [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT ] ]</pre>
</div>
<div class="refsect1" lang="en">
<a name="id786727"></a><h2>Description</h2>
<p> <code class="command">SELECT INTO</code> 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 <code class="command">SELECT</code>. The new
table's columns have the names and data types associated with the
output columns of the <code class="command">SELECT</code>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id786757"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code></span></dt>
<dd><p> If specified, the table is created as a temporary table. Refer
to <a href="sql-createtable.html">CREATE TABLE</a> for details.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>new_table</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of the table to be created.
</p></dd>
</dl></div>
<p> All other parameters are described in detail under <a href="sql-select.html">SELECT</a>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id786819"></a><h2>Notes</h2>
<p> <a href="sql-createtableas.html">CREATE TABLE AS</a> is functionally similar to
<code class="command">SELECT INTO</code>. <code class="command">CREATE TABLE AS</code>
is the recommended syntax, since this form of <code class="command">SELECT
INTO</code> is not available in <span class="application">ECPG</span>
or <span class="application">PL/pgSQL</span>, because they interpret the
<code class="literal">INTO</code> clause differently. Furthermore,
<code class="command">CREATE TABLE AS</code> offers a superset of the
functionality provided by <code class="command">SELECT INTO</code>.
</p>
<p> Prior to <span class="productname">PostgreSQL</span> 8.1, the table created by
<code class="command">SELECT INTO</code> included OIDs by default. In
<span class="productname">PostgreSQL</span> 8.1, this is not the case
[mdash ] to include OIDs in the new table, the <a href="runtime-config-compatible.html#guc-default-with-oids">default_with_oids</a> configuration variable must be
enabled. Alternatively, <code class="command">CREATE TABLE AS</code> can be
used with the <code class="literal">WITH OIDS</code> clause.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id786941"></a><h2>Examples</h2>
<p> Create a new table <code class="literal">films_recent</code> consisting of only
recent entries from the table <code class="literal">films</code>:
</p>
<pre class="programlisting">SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id786969"></a><h2>Compatibility</h2>
<p> The SQL standard uses <code class="command">SELECT INTO</code> to
represent selecting values into scalar variables of a host program,
rather than creating a new table. This indeed is the usage found
in <span class="application">ECPG</span> (see <a href="ecpg.html" title="Chapter30.ECPG - Embedded SQL in C">Chapter30, <i><span class="application">ECPG</span> - Embedded <acronym class="acronym">SQL</acronym> in C</i></a>) and
<span class="application">PL/pgSQL</span> (see <a href="plpgsql.html" title="Chapter36.PL/pgSQL - SQL Procedural Language">Chapter36, <i><span class="application">PL/pgSQL</span> - <acronym class="acronym">SQL</acronym> Procedural Language</i></a>).
The <span class="productname">PostgreSQL</span> usage of <code class="command">SELECT
INTO</code> to represent table creation is historical. It is
best to use <code class="command">CREATE TABLE AS</code> for this purpose in
new code.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id787033"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-createtableas.html">CREATE TABLE AS</a></span>
</div>
</div></body>
</html>
|
