1CREATE TABLE AS(7)      PostgreSQL 9.2.24 Documentation     CREATE TABLE AS(7)
2
3
4

NAME

6       CREATE_TABLE_AS - define a new table from the results of a query
7

SYNOPSIS

9       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
10           [ (column_name [, ...] ) ]
11           [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
12           [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
13           [ TABLESPACE tablespace_name ]
14           AS query
15           [ WITH [ NO ] DATA ]
16

DESCRIPTION

18       CREATE TABLE AS creates a table and fills it with data computed by a
19       SELECT command. The table columns have the names and data types
20       associated with the output columns of the SELECT (except that you can
21       override the column names by giving an explicit list of new column
22       names).
23
24       CREATE TABLE AS bears some resemblance to creating a view, but it is
25       really quite different: it creates a new table and evaluates the query
26       just once to fill the new table initially. The new table will not track
27       subsequent changes to the source tables of the query. In contrast, a
28       view re-evaluates its defining SELECT statement whenever it is queried.
29

PARAMETERS

31       GLOBAL or LOCAL
32           Ignored for compatibility. Use of these keywords is deprecated;
33           refer to CREATE TABLE (CREATE_TABLE(7)) for details.
34
35       TEMPORARY or TEMP
36           If specified, the table is created as a temporary table. Refer to
37           CREATE TABLE (CREATE_TABLE(7)) for details.
38
39       UNLOGGED
40           If specified, the table is created as an unlogged table. Refer to
41           CREATE TABLE (CREATE_TABLE(7)) for details.
42
43       table_name
44           The name (optionally schema-qualified) of the table to be created.
45
46       column_name
47           The name of a column in the new table. If column names are not
48           provided, they are taken from the output column names of the query.
49
50       WITH ( storage_parameter [= value] [, ... ] )
51           This clause specifies optional storage parameters for the new
52           table; see Storage Parameters for more information. The WITH clause
53           can also include OIDS=TRUE (or just OIDS) to specify that rows of
54           the new table should have OIDs (object identifiers) assigned to
55           them, or OIDS=FALSE to specify that the rows should not have OIDs.
56           See CREATE TABLE (CREATE_TABLE(7)) for more information.
57
58       WITH OIDS, WITHOUT OIDS
59           These are obsolescent syntaxes equivalent to WITH (OIDS) and WITH
60           (OIDS=FALSE), respectively. If you wish to give both an OIDS
61           setting and storage parameters, you must use the WITH ( ... )
62           syntax; see above.
63
64       ON COMMIT
65           The behavior of temporary tables at the end of a transaction block
66           can be controlled using ON COMMIT. The three options are:
67
68           PRESERVE ROWS
69               No special action is taken at the ends of transactions. This is
70               the default behavior.
71
72           DELETE ROWS
73               All rows in the temporary table will be deleted at the end of
74               each transaction block. Essentially, an automatic TRUNCATE(7)
75               is done at each commit.
76
77           DROP
78               The temporary table will be dropped at the end of the current
79               transaction block.
80
81       TABLESPACE tablespace_name
82           The tablespace_name is the name of the tablespace in which the new
83           table is to be created. If not specified, default_tablespace is
84           consulted, or temp_tablespaces if the table is temporary.
85
86       query
87           A SELECT(7), TABLE, or VALUES(7) command, or an EXECUTE(7) command
88           that runs a prepared SELECT, TABLE, or VALUES query.
89
90       WITH [ NO ] DATA
91           This clause specifies whether or not the data produced by the query
92           should be copied into the new table. If not, only the table
93           structure is copied. The default is to copy the data.
94

NOTES

96       This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
97       but it is preferred since it is less likely to be confused with other
98       uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
99       superset of the functionality offered by SELECT INTO.
100
101       Prior to PostgreSQL 8.0, CREATE TABLE AS always included OIDs in the
102       table it created. As of PostgreSQL 8.0, the CREATE TABLE AS command
103       allows the user to explicitly specify whether OIDs should be included.
104       If the presence of OIDs is not explicitly specified, the
105       default_with_oids configuration variable is used. As of PostgreSQL 8.1,
106       this variable is false by default, so the default behavior is not
107       identical to pre-8.0 releases. Applications that require OIDs in the
108       table created by CREATE TABLE AS should explicitly specify WITH (OIDS)
109       to ensure desired behavior.
110

EXAMPLES

112       Create a new table films_recent consisting of only recent entries from
113       the table films:
114
115           CREATE TABLE films_recent AS
116             SELECT * FROM films WHERE date_prod >= '2002-01-01';
117
118       To copy a table completely, the short form using the TABLE command can
119       also be used:
120
121           CREATE TABLE films2 AS
122             TABLE films;
123
124       Create a new temporary table films_recent, consisting of only recent
125       entries from the table films, using a prepared statement. The new table
126       has OIDs and will be dropped at commit:
127
128           PREPARE recentfilms(date) AS
129             SELECT * FROM films WHERE date_prod > $1;
130           CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
131             EXECUTE recentfilms('2002-01-01');
132

COMPATIBILITY

134       CREATE TABLE AS conforms to the SQL standard. The following are
135       nonstandard extensions:
136
137       ·   The standard requires parentheses around the subquery clause; in
138           PostgreSQL, these parentheses are optional.
139
140       ·   In the standard, the WITH [ NO ] DATA clause is required; in
141           PostgreSQL it is optional.
142
143       ·   PostgreSQL handles temporary tables in a way rather different from
144           the standard; see CREATE TABLE (CREATE_TABLE(7)) for details.
145
146       ·   The WITH clause is a PostgreSQL extension; neither storage
147           parameters nor OIDs are in the standard.
148
149       ·   The PostgreSQL concept of tablespaces is not part of the standard.
150           Hence, the clause TABLESPACE is an extension.
151

SEE ALSO

153       CREATE TABLE (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO
154       (SELECT_INTO(7)), VALUES(7)
155
156
157
158PostgreSQL 9.2.24                 2017-11-06                CREATE TABLE AS(7)
Impressum