2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.33 2006/07/02 02:23:18 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETABLEAS">
8 <refentrytitle id="sql-createtableas-title">CREATE TABLE AS</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE TABLE AS</refname>
14 <refpurpose>define a new table from the results of a query</refpurpose>
17 <indexterm zone="sql-createtableas">
18 <primary>CREATE TABLE AS</primary>
23 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable>
24 [ (<replaceable>column_name</replaceable> [, ...] ) ]
25 [ WITH OIDS | WITHOUT OIDS ]
26 [ WITH (FILLFACTOR = <replaceable>fillfactor</replaceable>) ]
27 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
28 [ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
29 AS <replaceable>query</replaceable>
34 <title>Description</title>
37 <command>CREATE TABLE AS</command> creates a table and fills it
38 with data computed by a <command>SELECT</command> command or an
39 <command>EXECUTE</command> that runs a prepared
40 <command>SELECT</command> command. 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" endterm="sql-createtable-title"> 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" endterm="sql-createtable-title"> for details.
85 <term><replaceable>table_name</replaceable></term>
88 The name (optionally schema-qualified) of the table to be created.
94 <term><replaceable>column_name</replaceable></term>
97 The name of a column in the new table. If column names are not
98 provided, they are taken from the output column names of the
99 query. If the table is created from an
100 <command>EXECUTE</command> command, a column name list cannot be
107 <term><literal>WITH OIDS</literal></term>
108 <term><literal>WITHOUT OIDS</literal></term>
111 This optional clause specifies whether the table created by
112 <command>CREATE TABLE AS</command> should include OIDs. If
113 neither form of this clause is specified, the value of the
114 <xref linkend="guc-default-with-oids"> configuration parameter is
121 <term><literal>WITH (FILLFACTOR = <replaceable>fillfactor</replaceable>)</literal></term>
124 This optional clause specifies the table's fillfactor in percentage.
130 <term><literal>ON COMMIT</literal></term>
133 The behavior of temporary tables at the end of a transaction
134 block can be controlled using <literal>ON COMMIT</literal>.
135 The three options are:
139 <term><literal>PRESERVE ROWS</literal></term>
142 No special action is taken at the ends of transactions.
143 This is the default behavior.
149 <term><literal>DELETE ROWS</literal></term>
152 All rows in the temporary table will be deleted at the end
153 of each transaction block. Essentially, an automatic <xref
154 linkend="sql-truncate" endterm="sql-truncate-title"> is done
161 <term><literal>DROP</literal></term>
164 The temporary table will be dropped at the end of the current
175 <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
178 The <replaceable class="PARAMETER">tablespace</replaceable> is the name
179 of the tablespace in which the new table is to be created.
181 <xref linkend="guc-default-tablespace"> is used, or the database's
182 default tablespace if <varname>default_tablespace</> is an empty
189 <term><replaceable>query</replaceable></term>
192 A query statement (that is, a <command>SELECT</command> command
193 or an <command>EXECUTE</command> command that runs a prepared
194 <command>SELECT</command> command). Refer to <xref
195 linkend="sql-select" endterm="sql-select-title"> or <xref
196 linkend="sql-execute" endterm="sql-execute-title">,
197 respectively, for a description of the allowed syntax.
208 This command is functionally similar to <xref
209 linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is
210 preferred since it is less likely to be confused with other uses of
211 the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE
212 TABLE AS</command> offers a superset of the functionality offered
213 by <command>SELECT INTO</command>.
217 Prior to <productname>PostgreSQL</productname> 8.0, <command>CREATE
218 TABLE AS</command> always included OIDs in the table it
219 created. As of <productname>PostgreSQL</productname> 8.0,
220 the <command>CREATE TABLE AS</command> command allows the user to
221 explicitly specify whether OIDs should be included. If the
222 presence of OIDs is not explicitly specified,
223 the <xref linkend="guc-default-with-oids"> configuration variable is
224 used. As of <productname>PostgreSQL</productname> 8.1,
225 this variable is false by default, so the default behavior is not
226 identical to pre-8.0 releases. Applications that
227 require OIDs in the table created by <command>CREATE TABLE
228 AS</command> should explicitly specify <literal>WITH OIDS</literal>
229 to ensure proper behavior.
234 <title>Examples</title>
237 Create a new table <literal>films_recent</literal> consisting of only
238 recent entries from the table <literal>films</literal>:
241 CREATE TABLE films_recent AS
242 SELECT * FROM films WHERE date_prod >= '2002-01-01';
247 Create a new temporary table that will be dropped at commit
248 <literal>films_recent</literal> with oids consisting of only
249 recent entries from the table <literal>films</literal> using a
253 PREPARE recentfilms(date) AS
254 SELECT * FROM films WHERE date_prod > $1;
255 CREATE TEMP TABLE films_recent WITH OIDS ON COMMIT DROP AS
256 EXECUTE recentfilms('2002-01-01');
262 <title>Compatibility</title>
265 <command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
266 standard, with the following exceptions:
268 <itemizedlist spacing="compact">
271 The standard requires parentheses around the subquery clause; in
272 <productname>PostgreSQL</productname>, these parentheses are
279 The standard defines a <literal>WITH [ NO ] DATA</literal> clause;
280 this is not currently implemented by <productname>PostgreSQL</>.
281 The behavior provided by <productname>PostgreSQL</> is equivalent
282 to the standard's <literal>WITH DATA</literal> case.
288 <literal>WITH/WITHOUT OIDS</> is a <productname>PostgreSQL</>
295 <productname>PostgreSQL</> handles temporary tables in a way
296 rather different from the standard; see
297 <xref linkend="sql-createtable" endterm="sql-createtable-title">
304 The <productname>PostgreSQL</productname> concept of tablespaces is not
305 part of the standard. Hence, the clause <literal>TABLESPACE</literal>
314 <title>See Also</title>
316 <simplelist type="inline">
317 <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
318 <member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
319 <member><xref linkend="sql-select" endterm="sql-select-title"></member>
320 <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
326 <!-- Keep this comment at the end of the file
331 sgml-minimize-attributes:nil
332 sgml-always-quote-attributes:t
335 sgml-parent-document:nil
336 sgml-default-dtd-file:"../reference.ced"
337 sgml-exposed-tags:nil
338 sgml-local-catalogs:"/usr/lib/sgml/catalog"
339 sgml-local-ecat-files:nil