Debian Codegraph @

doc/src/sgml/ref/create_table_as.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_table_as.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createtableas">
7 <indexterm zone="sql-createtableas">
8 <primary>CREATE TABLE AS</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE TABLE AS</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE TABLE AS</refname>
19 <refpurpose>define a new table from the results of a query</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
25 [ (<replaceable>column_name</replaceable> [, ...] ) ]
26 [ USING <replaceable class="parameter">method</replaceable> ]
27 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
28 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
29 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
30 AS <replaceable>query</replaceable>
31 [ WITH [ NO ] DATA ]
32 </synopsis>
33 </refsynopsisdiv>
34
35 <refsect1>
36 <title>Description</title>
37
38 <para>
39 <command>CREATE TABLE AS</command> creates a table and fills it
40 with data computed by a <command>SELECT</command> command.
41 The table columns have the
42 names and data types associated with the output columns of the
43 <command>SELECT</command> (except that you can override the column
44 names by giving an explicit list of new column names).
45 </para>
46
47 <para>
48 <command>CREATE TABLE AS</command> bears some resemblance to
49 creating a view, but it is really quite different: it creates a new
50 table and evaluates the query just once to fill the new table
51 initially. The new table will not track subsequent changes to the
52 source tables of the query. In contrast, a view re-evaluates its
53 defining <command>SELECT</command> statement whenever it is
54 queried.
55 </para>
56
57 <para>
58 <command>CREATE TABLE AS</command> requires <literal>CREATE</literal>
59 privilege on the schema used for the table.
60 </para>
61 </refsect1>
62
63 <refsect1>
64 <title>Parameters</title>
65
66 <variablelist>
67 <varlistentry>
68 <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
69 <listitem>
70 <para>
71 Ignored for compatibility. Use of these keywords is deprecated;
72 refer to <xref linkend="sql-createtable"/> for details.
73 </para>
74 </listitem>
75 </varlistentry>
76 </variablelist>
77
78 <variablelist>
79 <varlistentry>
80 <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
81 <listitem>
82 <para>
83 If specified, the table is created as a temporary table.
84 Refer to <xref linkend="sql-createtable"/> for details.
85 </para>
86 </listitem>
87 </varlistentry>
88
89 <varlistentry>
90 <term><literal>UNLOGGED</literal></term>
91 <listitem>
92 <para>
93 If specified, the table is created as an unlogged table.
94 Refer to <xref linkend="sql-createtable"/> for details.
95 </para>
96 </listitem>
97 </varlistentry>
98
99 <varlistentry>
100 <term><literal>IF NOT EXISTS</literal></term>
101 <listitem>
102 <para>
103 Do not throw an error if a relation with the same name already
104 exists; simply issue a notice and leave the table unmodified.
105 </para>
106 </listitem>
107 </varlistentry>
108
109 <varlistentry>
110 <term><replaceable>table_name</replaceable></term>
111 <listitem>
112 <para>
113 The name (optionally schema-qualified) of the table to be created.
114 </para>
115 </listitem>
116 </varlistentry>
117
118 <varlistentry>
119 <term><replaceable>column_name</replaceable></term>
120 <listitem>
121 <para>
122 The name of a column in the new table. If column names are not
123 provided, they are taken from the output column names of the query.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
130 <listitem>
131 <para>
132 This optional clause specifies the table access method to use to store
133 the contents for the new table; the method needs be an access method of
134 type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
135 information. If this option is not specified, the default table access
136 method is chosen for the new table. See <xref
137 linkend="guc-default-table-access-method"/> for more information.
138 </para>
139 </listitem>
140 </varlistentry>
141
142 <varlistentry>
143 <term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
144 <listitem>
145 <para>
146 This clause specifies optional storage parameters for the new table;
147 see <xref linkend="sql-createtable-storage-parameters"/> in the
148 <xref linkend="sql-createtable"/> documentation for more
149 information. For backward-compatibility the <literal>WITH</literal>
150 clause for a table can also include <literal>OIDS=FALSE</literal> to
151 specify that rows of the new table should contain no OIDs (object
152 identifiers), <literal>OIDS=TRUE</literal> is not supported anymore.
153 </para>
154 </listitem>
155 </varlistentry>
156
157 <varlistentry>
158 <term><literal>WITHOUT OIDS</literal></term>
159 <listitem>
160 <para>
161 This is backward-compatible syntax for declaring a table
162 <literal>WITHOUT OIDS</literal>, creating a table <literal>WITH
163 OIDS</literal> is not supported anymore.
164 </para>
165 </listitem>
166 </varlistentry>
167
168 <varlistentry>
169 <term><literal>ON COMMIT</literal></term>
170 <listitem>
171 <para>
172 The behavior of temporary tables at the end of a transaction
173 block can be controlled using <literal>ON COMMIT</literal>.
174 The three options are:
175
176 <variablelist>
177 <varlistentry>
178 <term><literal>PRESERVE ROWS</literal></term>
179 <listitem>
180 <para>
181 No special action is taken at the ends of transactions.
182 This is the default behavior.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry>
188 <term><literal>DELETE ROWS</literal></term>
189 <listitem>
190 <para>
191 All rows in the temporary table will be deleted at the end
192 of each transaction block. Essentially, an automatic <link
193 linkend="sql-truncate"><command>TRUNCATE</command></link> is done
194 at each commit.
195 </para>
196 </listitem>
197 </varlistentry>
198
199 <varlistentry>
200 <term><literal>DROP</literal></term>
201 <listitem>
202 <para>
203 The temporary table will be dropped at the end of the current
204 transaction block.
205 </para>
206 </listitem>
207 </varlistentry>
208 </variablelist></para>
209 </listitem>
210 </varlistentry>
211
212 <varlistentry>
213 <term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
214 <listitem>
215 <para>
216 The <replaceable class="parameter">tablespace_name</replaceable> is the name
217 of the tablespace in which the new table is to be created.
218 If not specified,
219 <xref linkend="guc-default-tablespace"/> is consulted, or
220 <xref linkend="guc-temp-tablespaces"/> if the table is temporary.
221 </para>
222 </listitem>
223 </varlistentry>
224
225 <varlistentry>
226 <term><replaceable>query</replaceable></term>
227 <listitem>
228 <para>
229 A <link linkend="sql-select"><command>SELECT</command></link>, <link
230 linkend="sql-table"><command>TABLE</command></link>, or <link linkend="sql-values"><command>VALUES</command></link>
231 command, or an <link linkend="sql-execute"><command>EXECUTE</command></link> command that runs a
232 prepared <command>SELECT</command>, <command>TABLE</command>, or
233 <command>VALUES</command> query.
234 </para>
235 </listitem>
236 </varlistentry>
237
238 <varlistentry>
239 <term><literal>WITH [ NO ] DATA</literal></term>
240 <listitem>
241 <para>
242 This clause specifies whether or not the data produced by the query
243 should be copied into the new table. If not, only the table structure
244 is copied. The default is to copy the data.
245 </para>
246 </listitem>
247 </varlistentry>
248
249 </variablelist>
250 </refsect1>
251
252 <refsect1>
253 <title>Notes</title>
254
255 <para>
256 This command is functionally similar to <xref
257 linkend="sql-selectinto"/>, but it is
258 preferred since it is less likely to be confused with other uses of
259 the <command>SELECT INTO</command> syntax. Furthermore, <command>CREATE
260 TABLE AS</command> offers a superset of the functionality offered
261 by <command>SELECT INTO</command>.
262 </para>
263 </refsect1>
264
265 <refsect1>
266 <title>Examples</title>
267
268 <para>
269 Create a new table <literal>films_recent</literal> consisting of only
270 recent entries from the table <literal>films</literal>:
271
272 <programlisting>
273 CREATE TABLE films_recent AS
274 SELECT * FROM films WHERE date_prod &gt;= '2002-01-01';
275 </programlisting>
276 </para>
277
278 <para>
279 To copy a table completely, the short form using
280 the <literal>TABLE</literal> command can also be used:
281
282 <programlisting>
283 CREATE TABLE films2 AS
284 TABLE films;
285 </programlisting>
286 </para>
287
288 <para>
289 Create a new temporary table <literal>films_recent</literal>, consisting of
290 only recent entries from the table <literal>films</literal>, using a
291 prepared statement. The new table will be dropped at commit:
292
293 <programlisting>
294 PREPARE recentfilms(date) AS
295 SELECT * FROM films WHERE date_prod &gt; $1;
296 CREATE TEMP TABLE films_recent ON COMMIT DROP AS
297 EXECUTE recentfilms('2002-01-01');
298 </programlisting></para>
299 </refsect1>
300
301 <refsect1>
302 <title>Compatibility</title>
303
304 <para>
305 <command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
306 standard. The following are nonstandard extensions:
307
308 <itemizedlist spacing="compact">
309 <listitem>
310 <para>
311 The standard requires parentheses around the subquery clause; in
312 <productname>PostgreSQL</productname>, these parentheses are
313 optional.
314 </para>
315 </listitem>
316
317 <listitem>
318 <para>
319 In the standard, the <literal>WITH [ NO ] DATA</literal> clause
320 is required; in PostgreSQL it is optional.
321 </para>
322 </listitem>
323
324 <listitem>
325 <para><productname>PostgreSQL</productname> handles temporary tables in a way
326 rather different from the standard; see
327 <xref linkend="sql-createtable"/>
328 for details.
329 </para>
330 </listitem>
331
332 <listitem>
333 <para>
334 The <literal>WITH</literal> clause is a <productname>PostgreSQL</productname>
335 extension; storage parameters are not in the standard.
336 </para>
337 </listitem>
338
339 <listitem>
340 <para>
341 The <productname>PostgreSQL</productname> concept of tablespaces is not
342 part of the standard. Hence, the clause <literal>TABLESPACE</literal>
343 is an extension.
344 </para>
345 </listitem>
346 </itemizedlist></para>
347 </refsect1>
348
349 <refsect1>
350 <title>See Also</title>
351
352 <simplelist type="inline">
353 <member><xref linkend="sql-creatematerializedview"/></member>
354 <member><xref linkend="sql-createtable"/></member>
355 <member><xref linkend="sql-execute"/></member>
356 <member><xref linkend="sql-select"/></member>
357 <member><xref linkend="sql-selectinto"/></member>
358 <member><xref linkend="sql-values"/></member>
359 </simplelist>
360 </refsect1>
361
362 </refentry>