1CREATE TABLE AS() SQL Commands CREATE TABLE AS()
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
19 CREATE TABLE AS creates a table and fills it with data computed by a
20 SELECT command. The table columns have the names and data types asso‐
21 ciated with the output columns of the SELECT (except that you can over‐
22 ride the column names by giving an explicit list of new column names).
23 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
31 GLOBAL or LOCAL
32 Ignored for compatibility. Refer to CREATE TABLE [create_ta‐
33 ble(7)] for details.
34 TEMPORARY or TEMP
36 If specified, the table is created as a temporary table. Refer
37 to CREATE TABLE [create_table(7)] for details.
38 table_name
40 The name (optionally schema-qualified) of the table to be cre‐
41 ated.
42 column_name
44 The name of a column in the new table. If column names are not
45 provided, they are taken from the output column names of the
46 query. If the table is created from an EXECUTE command, a column
47 name list cannot be specified.
48 WITH ( storage_parameter [= value] [, ... ] )
50 This clause specifies optional storage parameters for the new
51 table; see Storage Parameters [create_table(7)] for more infor‐
52 mation. The WITH clause can also include OIDS=TRUE (or just
53 OIDS) to specify that rows of the new table should have OIDs
54 (object identifiers) assigned to them, or OIDS=FALSE to specify
55 that the rows should not have OIDs. See CREATE TABLE [cre‐
56 ate_table(7)] for more information.
57 WITH OIDS
59 WITHOUT OIDS
61 These are obsolescent syntaxes equivalent to WITH (OIDS) and
62 WITH (OIDS=FALSE), respectively. If you wish to give both an
63 OIDS setting and storage parameters, you must use the WITH ( ...
64 ) syntax; see above.
65 ON COMMIT
67 The behavior of temporary tables at the end of a transaction
68 block can be controlled using ON COMMIT. The three options are:
69 PRESERVE ROWS
71 No special action is taken at the ends of transactions.
72 This is the default behavior.
73 DELETE ROWS
75 All rows in the temporary table will be deleted at the
76 end of each transaction block. Essentially, an automatic
77 TRUNCATE [truncate(7)] is done at each commit.
78 DROP The temporary table will be dropped at the end of the
80 current transaction block.
81 TABLESPACE tablespace
83 The tablespace is the name of the tablespace in which the new
84 table is to be created. If not specified, default_tablespace is
85 used, or the database's default tablespace if default_tablespace
86 is an empty string.
87 query A SELECT [select(7)] or VALUES [values(7)] command, or an EXE‐
89 CUTE [execute(7)] command that runs a prepared SELECT or VALUES
90 query.
91
93 This command is functionally similar to SELECT INTO [select_into(7)],
94 but it is preferred since it is less likely to be confused with other
95 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
96 superset of the functionality offered by SELECT INTO.
97 Prior to PostgreSQL 8.0, CREATE TABLE AS always included OIDs in the
99 table it created. As of PostgreSQL 8.0, the CREATE TABLE AS command
100 allows the user to explicitly specify whether OIDs should be included.
101 If the presence of OIDs is not explicitly specified, the
102 default_with_oids configuration variable is used. As of PostgreSQL 8.1,
103 this variable is false by default, so the default behavior is not iden‐
104 tical to pre-8.0 releases. Applications that require OIDs in the table
105 created by CREATE TABLE AS should explicitly specify WITH (OIDS) to
106 ensure proper behavior.
107
109 Create a new table films_recent consisting of only recent entries from
110 the table films:
111 CREATE TABLE films_recent AS
113 SELECT * FROM films WHERE date_prod >= '2002-01-01';
114 Create a new temporary table films_recent, consisting of only recent
117 entries from the table films, using a prepared statement. The new table
118 has OIDs and will be dropped at commit:
119 PREPARE recentfilms(date) AS
121 SELECT * FROM films WHERE date_prod > $1;
122 CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
123 EXECUTE recentfilms('2002-01-01');
124
127 CREATE TABLE AS conforms to the SQL standard, with the following excep‐
128 tions:
129 · The standard requires parentheses around the subquery clause; in
131 PostgreSQL, these parentheses are optional.
132 · The standard defines a WITH [ NO ] DATA clause; this is not currently
134 implemented by PostgreSQL. The behavior provided by PostgreSQL is
135 equivalent to the standard's WITH DATA case. WITH NO DATA can be
136 simulated by appending LIMIT 0 to the query.
137 · PostgreSQL handles temporary tables in a way rather different from
139 the standard; see CREATE TABLE [create_table(7)] for details.
140 · The WITH clause is a PostgreSQL extension; neither storage parameters
142 nor OIDs are in the standard.
143 · The PostgreSQL concept of tablespaces is not part of the standard.
145 Hence, the clause TABLESPACE is an extension.
146
148 CREATE TABLE [create_table(7)], EXECUTE [execute(l)], SELECT
149 [select(l)], SELECT INTO [select_into(l)], VALUES [values(l)]
150SQL - Language Statements 2008-06-08 CREATE TABLE AS()