1CREATE TABLE AS(7) SQL Commands CREATE TABLE AS(7)
2
6 CREATE TABLE AS - define a new table from the results of a query
7
10 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
11 [ (column_name [, ...] ) ]
12 [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
13 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
14 [ TABLESPACE tablespace ]
15 AS query
16 [ WITH [ NO ] DATA ]
17
20 CREATE TABLE AS creates a table and fills it with data computed by a
21 SELECT command. The table columns have the names and data types asso‐
22 ciated with the output columns of the SELECT (except that you can over‐
23 ride the column names by giving an explicit list of new column names).
24 CREATE TABLE AS bears some resemblance to creating a view, but it is
26 really quite different: it creates a new table and evaluates the query
27 just once to fill the new table initially. The new table will not track
28 subsequent changes to the source tables of the query. In contrast, a
29 view re-evaluates its defining SELECT statement whenever it is queried.
30
32 GLOBAL or LOCAL
33 Ignored for compatibility. Refer to CREATE TABLE [create_ta‐
34 ble(7)] for details.
35 TEMPORARY or TEMP
37 If specified, the table is created as a temporary table. Refer
38 to CREATE TABLE [create_table(7)] for details.
39 table_name
41 The name (optionally schema-qualified) of the table to be cre‐
42 ated.
43 column_name
45 The name of a column in the new table. If column names are not
46 provided, they are taken from the output column names of the
47 query. If the table is created from an EXECUTE command, a column
48 name list cannot be specified.
49 WITH ( storage_parameter [= value] [, ... ] )
51 This clause specifies optional storage parameters for the new
52 table; see Storage Parameters [create_table(7)] for more infor‐
53 mation. The WITH clause can also include OIDS=TRUE (or just
54 OIDS) to specify that rows of the new table should have OIDs
55 (object identifiers) assigned to them, or OIDS=FALSE to specify
56 that the rows should not have OIDs. See CREATE TABLE [cre‐
57 ate_table(7)] for more information.
58 WITH OIDS
60 WITHOUT OIDS
62 These are obsolescent syntaxes equivalent to WITH (OIDS) and
63 WITH (OIDS=FALSE), respectively. If you wish to give both an
64 OIDS setting and storage parameters, you must use the WITH ( ...
65 ) syntax; see above.
66 ON COMMIT
68 The behavior of temporary tables at the end of a transaction
69 block can be controlled using ON COMMIT. The three options are:
70 PRESERVE ROWS
72 No special action is taken at the ends of transactions.
73 This is the default behavior.
74 DELETE ROWS
76 All rows in the temporary table will be deleted at the
77 end of each transaction block. Essentially, an automatic
78 TRUNCATE [truncate(7)] is done at each commit.
79 DROP The temporary table will be dropped at the end of the
81 current transaction block.
82 TABLESPACE tablespace
84 The tablespace is the name of the tablespace in which the new
85 table is to be created. If not specified, default_tablespace is
86 consulted, or temp_tablespaces if the table is temporary.
87 query A SELECT [select(7)], TABLE, or VALUES [values(7)] command, or
89 an EXECUTE [execute(7)] command that runs a prepared SELECT, TA‐
90 BLE, or VALUES query.
91 WITH [ NO ] DATA
93 This clause specifies whether or not the data produced by the
94 query should be copied into the new table. If not, only the ta‐
95 ble structure is copied. The default is to copy the data.
96
98 This command is functionally similar to SELECT INTO [select_into(7)],
99 but it is preferred since it is less likely to be confused with other
100 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
101 superset of the functionality offered by SELECT INTO.
102 Prior to PostgreSQL 8.0, CREATE TABLE AS always included OIDs in the
104 table it created. As of PostgreSQL 8.0, the CREATE TABLE AS command
105 allows the user to explicitly specify whether OIDs should be included.
106 If the presence of OIDs is not explicitly specified, the
107 default_with_oids configuration variable is used. As of PostgreSQL 8.1,
108 this variable is false by default, so the default behavior is not iden‐
109 tical to pre-8.0 releases. Applications that require OIDs in the table
110 created by CREATE TABLE AS should explicitly specify WITH (OIDS) to
111 ensure proper behavior.
112
114 Create a new table films_recent consisting of only recent entries from
115 the table films:
116 CREATE TABLE films_recent AS
118 SELECT * FROM films WHERE date_prod >= '2002-01-01';
119 To copy a table completely, the short form using the TABLE command can
122 also be used:
123 CREATE TABLE films2 AS
125 TABLE films;
126 Create a new temporary table films_recent, consisting of only recent
129 entries from the table films, using a prepared statement. The new table
130 has OIDs and will be dropped at commit:
131 PREPARE recentfilms(date) AS
133 SELECT * FROM films WHERE date_prod > $1;
134 CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
135 EXECUTE recentfilms('2002-01-01');
136
139 CREATE TABLE AS conforms to the SQL standard. The following are non‐
140 standard extensions:
141 · The standard requires parentheses around the subquery clause; in
143 PostgreSQL, these parentheses are optional.
144 · In the standard, the WITH [ NO ] DATA clause is required; in Post‐
146 greSQL it is optional.
147 · PostgreSQL handles temporary tables in a way rather different from
149 the standard; see CREATE TABLE [create_table(7)] for details.
150 · The WITH clause is a PostgreSQL extension; neither storage parameters
152 nor OIDs are in the standard.
153 · The PostgreSQL concept of tablespaces is not part of the standard.
155 Hence, the clause TABLESPACE is an extension.
156
158 CREATE TABLE [create_table(7)], EXECUTE [execute(7)], SELECT
159 [select(7)], SELECT INTO [select_into(7)], VALUES [values(7)]
160SQL - Language Statements 2011-09-22 CREATE TABLE AS(7)