2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.26 2004/12/13 18:05:10 petere 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> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
24 AS <replaceable>query</replaceable>
29 <title>Description</title>
32 <command>CREATE TABLE AS</command> creates a table and fills it
33 with data computed by a <command>SELECT</command> command or an
34 <command>EXECUTE</command> that runs a prepared
35 <command>SELECT</command> command. The table columns have the
36 names and data types associated with the output columns of the
37 <command>SELECT</command> (except that you can override the column
38 names by giving an explicit list of new column names).
42 <command>CREATE TABLE AS</command> bears some resemblance to
43 creating a view, but it is really quite different: it creates a new
44 table and evaluates the query just once to fill the new table
45 initially. The new table will not track subsequent changes to the
46 source tables of the query. In contrast, a view re-evaluates its
47 defining <command>SELECT</command> statement whenever it is
53 <title>Parameters</title>
57 <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
60 Ignored for compatibility. Refer to <xref
61 linkend="sql-createtable" endterm="sql-createtable-title"> for
70 <term><literal>TEMPORARY</> or <literal>TEMP</></term>
73 If specified, the table is created as a temporary table.
74 Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> for details.
80 <term><replaceable>table_name</replaceable></term>
83 The name (optionally schema-qualified) of the table to be created.
89 <term><replaceable>column_name</replaceable></term>
92 The name of a column in the new table. If column names are not
93 provided, they are taken from the output column names of the
94 query. If the table is created out of an
95 <command>EXECUTE</command> command, a column name list can
96 currently not be specified.
102 <term><literal>WITH OIDS</literal></term>
103 <term><literal>WITHOUT OIDS</literal></term>
106 This optional clause specifies whether the table created by
107 <command>CREATE TABLE AS</command> should include OIDs. If
108 neither form of this clause if specified, the value of the
109 <xref linkend="guc-default-with-oids"> configuration parameter is
116 <term><replaceable>query</replaceable></term>
119 A query statement (that is, a <command>SELECT</command> command
120 or an <command>EXECUTE</command> command that runs a prepared
121 <command>SELECT</command> command). Refer to <xref
122 linkend="sql-select" endterm="sql-select-title"> or <xref
123 linkend="sql-execute" endterm="sql-execute-title">,
124 respectively, for a description of the allowed syntax.
135 This command is functionally similar to <xref
136 linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is
137 preferred since it is less likely to be confused with other uses of
138 the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE
139 TABLE AS</command> offers a superset of the functionality offered
140 by <command>SELECT INTO</command>.
144 Prior to <productname>PostgreSQL</productname> 8.0, <command>CREATE
145 TABLE AS</command> always included OIDs in the table it
146 produced. Furthermore, these OIDs were newly generated: they were
147 distinct from the OIDs of any of the rows in the source tables of
148 the <command>SELECT</command> or <command>EXECUTE</command>
149 statement. Therefore, if <command>CREATE TABLE AS</command> was
150 frequently executed, the OID counter would be rapidly
151 incremented. As of <productname>PostgresSQL</productname> 8.0,
152 the <command>CREATE TABLE AS</command> command allows the user to
153 explicitly specify whether OIDs should be included. If the
154 presence of OIDs is not explicitly specified,
155 the <xref linkend="guc-default-with-oids"> configuration variable is
156 used. While this variable currently defaults to true, the default
157 value may be changed in the future. Therefore, applications that
158 require OIDs in the table created by <command>CREATE TABLE
159 AS</command> should explicitly specify <literal>WITH
160 OIDS</literal> to ensure compatibility with future versions
161 of <productname>PostgreSQL</productname>.
166 <title>Compatibility</title>
169 <command>CREATE TABLE AS</command> is specified by the SQL:2003
170 standard. There are some small differences between the definition
171 of the command in SQL:2003 and its implementation in
172 <productname>PostgreSQL</>:
174 <itemizedlist spacing="compact">
177 The standard requires parentheses around the subquery clause; in
178 <productname>PostgreSQL</productname>, these parentheses are
185 The standard defines an <literal>ON COMMIT</literal> clause;
186 this is not currently implemented by <productname>PostgreSQL</>.
192 The standard defines a <literal>WITH DATA</literal> clause;
193 this is not currently implemented by <productname>PostgreSQL</>.
201 <title>See Also</title>
203 <simplelist type="inline">
204 <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
205 <member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
206 <member><xref linkend="sql-select" endterm="sql-select-title"></member>
207 <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
213 <!-- Keep this comment at the end of the file
218 sgml-minimize-attributes:nil
219 sgml-always-quote-attributes:t
222 sgml-parent-document:nil
223 sgml-default-dtd-file:"../reference.ced"
224 sgml-exposed-tags:nil
225 sgml-local-catalogs:"/usr/lib/sgml/catalog"
226 sgml-local-ecat-files:nil