1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.14 2003/07/01 00:04:31 petere Exp $
PostgreSQL documentation
-->
<refentry id="SQL-CREATETABLEAS">
<refmeta>
<refentrytitle id="sql-createtableas-title">CREATE TABLE AS</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>CREATE TABLE AS</refname>
<refpurpose>create a new table from the results of a query</refpurpose>
</refnamediv>
<refsynopsisdiv>
<synopsis>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ]
AS <replaceable>query</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>CREATE TABLE AS</command> creates a table and fills it
with data computed by a <command>SELECT</command> command or an
<command>EXECUTE</command> that runs a prepared
<command>SELECT</command> command. The table columns have the
names and data types associated with the output columns of the
<command>SELECT</command> (except that you can override the column
names by giving an explicit list of new column names).
</para>
<para>
<command>CREATE TABLE AS</command> bears some resemblance to
creating a view, but it is really quite different: it creates a new
table and evaluates the query just once to fill the new table
initially. The new table will not track subsequent changes to the
source tables of the query. In contrast, a view re-evaluates its
defining <command>SELECT</command> statement whenever it is
queried.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><literal>TEMPORARY</> or <literal>TEMP</></term>
<listitem>
<para>
If specified, the table is created as a temporary table.
Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> for details.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>table_name</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of the table to be created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>column_name</replaceable></term>
<listitem>
<para>
The name of a column in the new table. If column names are not
provided, they are taken from the output column names of the
query. If the table is created out of an
<command>EXECUTE</command> command, a column name list can
currently not be specified.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>query</replaceable></term>
<listitem>
<para>
A query statement (that is, a <command>SELECT</command> command
or an <command>EXECUTE</command> command that runs a prepared
<command>SELECT</command> command). Refer to <xref
linkend="sql-select" endterm="sql-select-title"> or <xref
linkend="sql-execute" endterm="sql-execute-title">,
respectively, for a description of the allowed syntax.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Diagnostics</title>
<para>
Refer to <xref linkend="sql-createtable"
endterm="sql-createtable-title">, <xref linkend="sql-select"
endterm="sql-select-title">, and <xref linkend="sql-execute"
endterm="sql-execute-title"> for a summary of possible output
messages.
</para>
</refsect1>
<refsect1>
<title>Notes</title>
<para>
This command is functionally equivalent to <xref
linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is preferred since it is less
likely to be confused with other uses of the <command>SELECT
... INTO</command> syntax.
</para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
This command is modeled after an <productname>Oracle</productname>
feature. There is no command with equivalent functionality in
the SQL standard. However, a combination of <literal>CREATE
TABLE</literal> and <literal>INSERT ... SELECT</literal> can
accomplish the same thing with little more effort.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
<member><xref linkend="sql-createview" endterm="sql-createview-title"></member>
<member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
<member><xref linkend="sql-select" endterm="sql-select-title"></member>
<member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
</simplelist>
</refsect1>
</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->
|
