1CREATE TABLE AS(7) PostgreSQL 11.3 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 [ IF NOT EXISTS ] 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 IF NOT EXISTS
44 Do not throw an error if a relation with the same name already
45 exists. A notice is issued in this case. Refer to CREATE TABLE
46 (CREATE_TABLE(7)) for details.
47 table_name
49 The name (optionally schema-qualified) of the table to be created.
50 column_name
52 The name of a column in the new table. If column names are not
53 provided, they are taken from the output column names of the query.
54 WITH ( storage_parameter [= value] [, ... ] )
56 This clause specifies optional storage parameters for the new
57 table; see Storage Parameters for more information. The WITH clause
58 can also include OIDS=TRUE (or just OIDS) to specify that rows of
59 the new table should have OIDs (object identifiers) assigned to
60 them, or OIDS=FALSE to specify that the rows should not have OIDs.
61 See CREATE TABLE (CREATE_TABLE(7)) for more information.
62 WITH OIDS
64 WITHOUT OIDS
65 These are obsolescent syntaxes equivalent to WITH (OIDS) and WITH
66 (OIDS=FALSE), respectively. If you wish to give both an OIDS
67 setting and storage parameters, you must use the WITH ( ... )
68 syntax; see above.
69 ON COMMIT
71 The behavior of temporary tables at the end of a transaction block
72 can be controlled using ON COMMIT. The three options are:
73 PRESERVE ROWS
75 No special action is taken at the ends of transactions. This is
76 the default behavior.
77 DELETE ROWS
79 All rows in the temporary table will be deleted at the end of
80 each transaction block. Essentially, an automatic TRUNCATE(7)
81 is done at each commit.
82 DROP
84 The temporary table will be dropped at the end of the current
85 transaction block.
86 TABLESPACE tablespace_name
88 The tablespace_name is the name of the tablespace in which the new
89 table is to be created. If not specified, default_tablespace is
90 consulted, or temp_tablespaces if the table is temporary.
91 query
93 A SELECT(7), TABLE, or VALUES(7) command, or an EXECUTE(7) command
94 that runs a prepared SELECT, TABLE, or VALUES query.
95 WITH [ NO ] DATA
97 This clause specifies whether or not the data produced by the query
98 should be copied into the new table. If not, only the table
99 structure is copied. The default is to copy the data.
100
102 This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
103 but it is preferred since it is less likely to be confused with other
104 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
105 superset of the functionality offered by SELECT INTO.
106 The CREATE TABLE AS command allows the user to explicitly specify
108 whether OIDs should be included. If the presence of OIDs is not
109 explicitly specified, the default_with_oids configuration variable is
110 used.
111
113 Create a new table films_recent consisting of only recent entries from
114 the table films:
115 CREATE TABLE films_recent AS
117 SELECT * FROM films WHERE date_prod >= '2002-01-01';
118 To copy a table completely, the short form using the TABLE command can
120 also be used:
121 CREATE TABLE films2 AS
123 TABLE films;
124 Create a new temporary table films_recent, consisting of only recent
126 entries from the table films, using a prepared statement. The new table
127 has OIDs and will be dropped at commit:
128 PREPARE recentfilms(date) AS
130 SELECT * FROM films WHERE date_prod > $1;
131 CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
132 EXECUTE recentfilms('2002-01-01');
133
135 CREATE TABLE AS conforms to the SQL standard. The following are
136 nonstandard extensions:
137 · The standard requires parentheses around the subquery clause; in
139 PostgreSQL, these parentheses are optional.
140 · In the standard, the WITH [ NO ] DATA clause is required; in
142 PostgreSQL it is optional.
143 · PostgreSQL handles temporary tables in a way rather different from
145 the standard; see CREATE TABLE (CREATE_TABLE(7)) for details.
146 · The WITH clause is a PostgreSQL extension; neither storage
148 parameters nor OIDs are in the standard.
149 · The PostgreSQL concept of tablespaces is not part of the standard.
151 Hence, the clause TABLESPACE is an extension.
152
154 CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), CREATE TABLE
155 (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO (SELECT_INTO(7)),
156 VALUES(7)
157PostgreSQL 11.3 2019 CREATE TABLE AS(7)