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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>CREATE TABLE AS</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-createtable.html" title="CREATE TABLE">
<link rel="next" href="sql-createtablespace.html" title="CREATE TABLESPACE">
<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-createtableas"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>CREATE TABLE AS — define a new table from the results of a query</p>
</div>
<a name="id764363"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <em class="replaceable"><code>table_name</code></em>
[ (<em class="replaceable"><code>column_name</code></em> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS <em class="replaceable"><code>query</code></em></pre>
</div>
<div class="refsect1" lang="en">
<a name="id764394"></a><h2>Description</h2>
<p> <code class="command">CREATE TABLE AS</code> creates a table and fills it
with data computed by a <code class="command">SELECT</code> command or an
<code class="command">EXECUTE</code> that runs a prepared
<code class="command">SELECT</code> command. The table columns have the
names and data types associated with the output columns of the
<code class="command">SELECT</code> (except that you can override the column
names by giving an explicit list of new column names).
</p>
<p> <code class="command">CREATE TABLE AS</code> bears some resemblance to
creating a view, but it is really quite different: it creates a new
table and evaluates the query just once to fill the new table
initially. The new table will not track subsequent changes to the
source tables of the query. In contrast, a view re-evaluates its
defining <code class="command">SELECT</code> statement whenever it is
queried.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id764460"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">GLOBAL</code> or <code class="literal">LOCAL</code></span></dt>
<dd><p> Ignored for compatibility. Refer to <a href="sql-createtable.html">CREATE TABLE</a> for
details.
</p></dd>
</dl></div>
<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>table_name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of the table to be created.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt>
<dd><p> The name of a column in the new table. If column names are not
provided, they are taken from the output column names of the
query. If the table is created from an
<code class="command">EXECUTE</code> command, a column name list cannot be
specified.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">WITH OIDS</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">WITHOUT OIDS</code></span>
</dt>
<dd><p> This optional clause specifies whether the table created by
<code class="command">CREATE TABLE AS</code> should include OIDs. If
neither form of this clause is specified, the value of the
<a href="runtime-config-compatible.html#guc-default-with-oids">default_with_oids</a> configuration parameter is
used.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt>
<dd><p> A query statement (that is, a <code class="command">SELECT</code> command
or an <code class="command">EXECUTE</code> command that runs a prepared
<code class="command">SELECT</code> command). Refer to <a href="sql-select.html">SELECT</a> or <a href="sql-execute.html">EXECUTE</a>,
respectively, for a description of the allowed syntax.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="id764647"></a><h2>Notes</h2>
<p> This command is functionally similar to <a href="sql-selectinto.html">SELECT INTO</a>, but it is
preferred since it is less likely to be confused with other uses of
the <code class="command">SELECT INTO</code> syntax. Furthermore, <code class="command">CREATE
TABLE AS</code> offers a superset of the functionality offered
by <code class="command">SELECT INTO</code>.
</p>
<p> Prior to <span class="productname">PostgreSQL</span> 8.0, <code class="command">CREATE
TABLE AS</code> always included OIDs in the table it
created. As of <span class="productname">PostgreSQL</span> 8.0,
the <code class="command">CREATE TABLE AS</code> command allows the user to
explicitly specify whether OIDs should be included. If the
presence of OIDs is not explicitly specified,
the <a href="runtime-config-compatible.html#guc-default-with-oids">default_with_oids</a> configuration variable is
used. As of <span class="productname">PostgreSQL</span> 8.1,
this variable is false by default, so the default behavior is not
identical to pre-8.0 releases. Applications that
require OIDs in the table created by <code class="command">CREATE TABLE
AS</code> should explicitly specify <code class="literal">WITH OIDS</code>
to ensure proper behavior.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id764755"></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">CREATE TABLE films_recent AS
SELECT * FROM films WHERE date_prod >= '2002-01-01';</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id764784"></a><h2>Compatibility</h2>
<p> <code class="command">CREATE TABLE AS</code> conforms to the <acronym class="acronym">SQL</acronym>
standard, with the following exceptions:
</p>
<div class="itemizedlist"><ul type="disc" compact>
<li><p> The standard requires parentheses around the subquery clause; in
<span class="productname">PostgreSQL</span>, these parentheses are
optional.
</p></li>
<li><p> The standard defines an <code class="literal">ON COMMIT</code> clause;
this is not currently implemented by <span class="productname">PostgreSQL</span>.
</p></li>
<li><p> The standard defines a <code class="literal">WITH [ NO ] DATA</code> clause;
this is not currently implemented by <span class="productname">PostgreSQL</span>.
The behavior provided by <span class="productname">PostgreSQL</span> is equivalent
to the standard's <code class="literal">WITH DATA</code> case.
</p></li>
<li><p> <code class="literal">WITH/WITHOUT OIDS</code> is a <span class="productname">PostgreSQL</span>
extension.
</p></li>
<li><p> <span class="productname">PostgreSQL</span> handles temporary tables in a way
rather different from the standard; see
<a href="sql-createtable.html">CREATE TABLE</a>
for details.
</p></li>
</ul></div>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id764911"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-createtable.html">CREATE TABLE</a>, <a href="sql-execute.html">EXECUTE</a>, <a href="sql-select.html">SELECT</a>, <a href="sql-selectinto.html">SELECT INTO</a></span>
</div>
</div></body>
</html>
|
