2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.28 2005/01/05 14:22:39 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 from an
95 <command>EXECUTE</command> command, a column name list cannot be
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 is 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. As of <productname>PostgresSQL</productname> 8.0,
147 the <command>CREATE TABLE AS</command> command allows the user to
148 explicitly specify whether OIDs should be included. If the
149 presence of OIDs is not explicitly specified,
150 the <xref linkend="guc-default-with-oids"> configuration variable is
151 used. While this variable currently defaults to true, the default
152 value may be changed in the future. Therefore, applications that
153 require OIDs in the table created by <command>CREATE TABLE
154 AS</command> should explicitly specify <literal>WITH
155 OIDS</literal> to ensure compatibility with future versions
156 of <productname>PostgreSQL</productname>.
161 <title>Compatibility</title>
164 <command>CREATE TABLE AS</command> is specified by the SQL:2003
165 standard. There are some small differences between the definition
166 of the command in SQL:2003 and its implementation in
167 <productname>PostgreSQL</>:
169 <itemizedlist spacing="compact">
172 The standard requires parentheses around the subquery clause; in
173 <productname>PostgreSQL</productname>, these parentheses are
180 The standard defines an <literal>ON COMMIT</literal> clause;
181 this is not currently implemented by <productname>PostgreSQL</>.
187 The standard defines a <literal>WITH DATA</literal> clause;
188 this is not currently implemented by <productname>PostgreSQL</>.
196 <title>See Also</title>
198 <simplelist type="inline">
199 <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
200 <member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
201 <member><xref linkend="sql-select" endterm="sql-select-title"></member>
202 <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
208 <!-- Keep this comment at the end of the file
213 sgml-minimize-attributes:nil
214 sgml-always-quote-attributes:t
217 sgml-parent-document:nil
218 sgml-default-dtd-file:"../reference.ced"
219 sgml-exposed-tags:nil
220 sgml-local-catalogs:"/usr/lib/sgml/catalog"
221 sgml-local-ecat-files:nil