1CREATE TABLE AS(7) PostgreSQL 9.2.24 Documentation CREATE TABLE AS(7)
2
6 CREATE_TABLE_AS - define a new table from the results of a query
7
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
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 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. Use of these keywords is deprecated;
33 refer to CREATE TABLE (CREATE_TABLE(7)) for details.
34 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 UNLOGGED
40 If specified, the table is created as an unlogged table. Refer to
41 CREATE TABLE (CREATE_TABLE(7)) for details.
42 table_name
44 The name (optionally schema-qualified) of the table to be created.
45 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 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 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 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 PRESERVE ROWS
69 No special action is taken at the ends of transactions. This is
70 the default behavior.
71 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 DROP
78 The temporary table will be dropped at the end of the current
79 transaction block.
80 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 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 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
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 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
112 Create a new table films_recent consisting of only recent entries from
113 the table films:
114 CREATE TABLE films_recent AS
116 SELECT * FROM films WHERE date_prod >= '2002-01-01';
117 To copy a table completely, the short form using the TABLE command can
119 also be used:
120 CREATE TABLE films2 AS
122 TABLE films;
123 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 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
134 CREATE TABLE AS conforms to the SQL standard. The following are
135 nonstandard extensions:
136 · The standard requires parentheses around the subquery clause; in
138 PostgreSQL, these parentheses are optional.
139 · In the standard, the WITH [ NO ] DATA clause is required; in
141 PostgreSQL it is optional.
142 · PostgreSQL handles temporary tables in a way rather different from
144 the standard; see CREATE TABLE (CREATE_TABLE(7)) for details.
145 · The WITH clause is a PostgreSQL extension; neither storage
147 parameters nor OIDs are in the standard.
148 · The PostgreSQL concept of tablespaces is not part of the standard.
150 Hence, the clause TABLESPACE is an extension.
151
153 CREATE TABLE (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO
154 (SELECT_INTO(7)), VALUES(7)
155PostgreSQL 9.2.24 2017-11-06 CREATE TABLE AS(7)