2 doc/src/sgml/ref/create_table_as.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETABLEAS">
8 <refentrytitle>CREATE TABLE AS</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE TABLE AS</refname>
15 <refpurpose>define a new table from the results of a query</refpurpose>
18 <indexterm zone="sql-createtableas">
19 <primary>CREATE TABLE AS</primary>
24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <replaceable>table_name</replaceable>
25 [ (<replaceable>column_name</replaceable> [, ...] ) ]
26 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
27 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
28 [ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
29 AS <replaceable>query</replaceable>
35 <title>Description</title>
38 <command>CREATE TABLE AS</command> creates a table and fills it
39 with data computed by a <command>SELECT</command> command.
40 The table columns have the
41 names and data types associated with the output columns of the
42 <command>SELECT</command> (except that you can override the column
43 names by giving an explicit list of new column names).
47 <command>CREATE TABLE AS</command> bears some resemblance to
48 creating a view, but it is really quite different: it creates a new
49 table and evaluates the query just once to fill the new table
50 initially. The new table will not track subsequent changes to the
51 source tables of the query. In contrast, a view re-evaluates its
52 defining <command>SELECT</command> statement whenever it is
58 <title>Parameters</title>
62 <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
65 Ignored for compatibility. Refer to <xref
66 linkend="sql-createtable"> for
75 <term><literal>TEMPORARY</> or <literal>TEMP</></term>
78 If specified, the table is created as a temporary table.
79 Refer to <xref linkend="sql-createtable"> for details.
85 <term><literal>UNLOGGED</></term>
88 If specified, the table is created as an unlogged table.
89 Refer to <xref linkend="sql-createtable"> for details.
95 <term><replaceable>table_name</replaceable></term>
98 The name (optionally schema-qualified) of the table to be created.
104 <term><replaceable>column_name</replaceable></term>
107 The name of a column in the new table. If column names are not
108 provided, they are taken from the output column names of the query.
114 <term><literal>WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term>
117 This clause specifies optional storage parameters for the new table;
118 see <xref linkend="sql-createtable-storage-parameters"
119 endterm="sql-createtable-storage-parameters-title"> for more
120 information. The <literal>WITH</> clause
121 can also include <literal>OIDS=TRUE</> (or just <literal>OIDS</>)
122 to specify that rows of the new table
123 should have OIDs (object identifiers) assigned to them, or
124 <literal>OIDS=FALSE</> to specify that the rows should not have OIDs.
125 See <xref linkend="sql-createtable"> for more information.
131 <term><literal>WITH OIDS</></term>
132 <term><literal>WITHOUT OIDS</></term>
135 These are obsolescent syntaxes equivalent to <literal>WITH (OIDS)</>
136 and <literal>WITH (OIDS=FALSE)</>, respectively. If you wish to give
137 both an <literal>OIDS</> setting and storage parameters, you must use
138 the <literal>WITH ( ... )</> syntax; see above.
144 <term><literal>ON COMMIT</literal></term>
147 The behavior of temporary tables at the end of a transaction
148 block can be controlled using <literal>ON COMMIT</literal>.
149 The three options are:
153 <term><literal>PRESERVE ROWS</literal></term>
156 No special action is taken at the ends of transactions.
157 This is the default behavior.
163 <term><literal>DELETE ROWS</literal></term>
166 All rows in the temporary table will be deleted at the end
167 of each transaction block. Essentially, an automatic <xref
168 linkend="sql-truncate"> is done
175 <term><literal>DROP</literal></term>
178 The temporary table will be dropped at the end of the current
183 </variablelist></para>
188 <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
191 The <replaceable class="PARAMETER">tablespace</replaceable> is the name
192 of the tablespace in which the new table is to be created.
194 <xref linkend="guc-default-tablespace"> is consulted, or
195 <xref linkend="guc-temp-tablespaces"> if the table is temporary.
201 <term><replaceable>query</replaceable></term>
204 A <xref linkend="sql-select">, <link
205 linkend="sql-table">TABLE</link>, or <xref linkend="sql-values">
206 command, or an <xref linkend="sql-execute"> command that runs a
207 prepared <command>SELECT</>, <command>TABLE</>, or
208 <command>VALUES</> query.
214 <term><literal>WITH [ NO ] DATA</></term>
217 This clause specifies whether or not the data produced by the query
218 should be copied into the new table. If not, only the table structure
219 is copied. The default is to copy the data.
231 This command is functionally similar to <xref
232 linkend="sql-selectinto">, but it is
233 preferred since it is less likely to be confused with other uses of
234 the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE
235 TABLE AS</command> offers a superset of the functionality offered
236 by <command>SELECT INTO</command>.
240 Prior to <productname>PostgreSQL</productname> 8.0, <command>CREATE
241 TABLE AS</command> always included OIDs in the table it
242 created. As of <productname>PostgreSQL</productname> 8.0,
243 the <command>CREATE TABLE AS</command> command allows the user to
244 explicitly specify whether OIDs should be included. If the
245 presence of OIDs is not explicitly specified,
246 the <xref linkend="guc-default-with-oids"> configuration variable is
247 used. As of <productname>PostgreSQL</productname> 8.1,
248 this variable is false by default, so the default behavior is not
249 identical to pre-8.0 releases. Applications that
250 require OIDs in the table created by <command>CREATE TABLE
251 AS</command> should explicitly specify <literal>WITH (OIDS)</literal>
252 to ensure desired behavior.
257 <title>Examples</title>
260 Create a new table <literal>films_recent</literal> consisting of only
261 recent entries from the table <literal>films</literal>:
264 CREATE TABLE films_recent AS
265 SELECT * FROM films WHERE date_prod >= '2002-01-01';
270 To copy a table completely, the short form using
271 the <literal>TABLE</literal> command can also be used:
274 CREATE TABLE films2 AS
280 Create a new temporary table <literal>films_recent</literal>, consisting of
281 only recent entries from the table <literal>films</literal>, using a
282 prepared statement. The new table has OIDs and will be dropped at commit:
285 PREPARE recentfilms(date) AS
286 SELECT * FROM films WHERE date_prod > $1;
287 CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
288 EXECUTE recentfilms('2002-01-01');
289 </programlisting></para>
293 <title>Compatibility</title>
296 <command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
297 standard. The following are nonstandard extensions:
299 <itemizedlist spacing="compact">
302 The standard requires parentheses around the subquery clause; in
303 <productname>PostgreSQL</productname>, these parentheses are
310 In the standard, the <literal>WITH [ NO ] DATA</literal> clause
311 is required; in PostgreSQL it is optional.
316 <para><productname>PostgreSQL</> handles temporary tables in a way
317 rather different from the standard; see
318 <xref linkend="sql-createtable">
325 The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
326 extension; neither storage parameters nor OIDs are in the standard.
332 The <productname>PostgreSQL</productname> concept of tablespaces is not
333 part of the standard. Hence, the clause <literal>TABLESPACE</literal>
337 </itemizedlist></para>
341 <title>See Also</title>
343 <simplelist type="inline">
344 <member><xref linkend="sql-createtable"></member>
345 <member><xref linkend="sql-execute"></member>
346 <member><xref linkend="sql-select"></member>
347 <member><xref linkend="sql-selectinto"></member>
348 <member><xref linkend="sql-values"></member>