1CREATE TABLE AS(7) PostgreSQL 13.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 [ USING method ]
12 [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
13 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
14 [ TABLESPACE tablespace_name ]
15 AS query
16 [ WITH [ NO ] DATA ]
17
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
21 associated with the output columns of the SELECT (except that you can
22 override the column names by giving an explicit list of new column
23 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. Use of these keywords is deprecated;
34 refer to CREATE TABLE (CREATE_TABLE(7)) for details.
35 TEMPORARY or TEMP
37 If specified, the table is created as a temporary table. Refer to
38 CREATE TABLE (CREATE_TABLE(7)) for details.
39 UNLOGGED
41 If specified, the table is created as an unlogged table. Refer to
42 CREATE TABLE (CREATE_TABLE(7)) for details.
43 IF NOT EXISTS
45 Do not throw an error if a relation with the same name already
46 exists. A notice is issued in this case. Refer to CREATE TABLE
47 (CREATE_TABLE(7)) for details.
48 table_name
50 The name (optionally schema-qualified) of the table to be created.
51 column_name
53 The name of a column in the new table. If column names are not
54 provided, they are taken from the output column names of the query.
55 USING method
57 This optional clause specifies the table access method to use to
58 store the contents for the new table; the method needs be an access
59 method of type TABLE. See Chapter 60 for more information. If this
60 option is not specified, the default table access method is chosen
61 for the new table. See default_table_access_method for more
62 information.
63 WITH ( storage_parameter [= value] [, ... ] )
65 This clause specifies optional storage parameters for the new
66 table; see Storage Parameters in the CREATE TABLE (CREATE_TABLE(7))
67 documentation for more information. For backward-compatibility the
68 WITH clause for a table can also include OIDS=FALSE to specify that
69 rows of the new table should contain no OIDs (object identifiers),
70 OIDS=TRUE is not supported anymore.
71 WITHOUT OIDS
73 This is backward-compatible syntax for declaring a table WITHOUT
74 OIDS, creating a table WITH OIDS is not supported anymore.
75 ON COMMIT
77 The behavior of temporary tables at the end of a transaction block
78 can be controlled using ON COMMIT. The three options are:
79 PRESERVE ROWS
81 No special action is taken at the ends of transactions. This is
82 the default behavior.
83 DELETE ROWS
85 All rows in the temporary table will be deleted at the end of
86 each transaction block. Essentially, an automatic TRUNCATE(7)
87 is done at each commit.
88 DROP
90 The temporary table will be dropped at the end of the current
91 transaction block.
92 TABLESPACE tablespace_name
94 The tablespace_name is the name of the tablespace in which the new
95 table is to be created. If not specified, default_tablespace is
96 consulted, or temp_tablespaces if the table is temporary.
97 query
99 A SELECT(7), TABLE, or VALUES(7) command, or an EXECUTE(7) command
100 that runs a prepared SELECT, TABLE, or VALUES query.
101 WITH [ NO ] DATA
103 This clause specifies whether or not the data produced by the query
104 should be copied into the new table. If not, only the table
105 structure is copied. The default is to copy the data.
106
108 This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
109 but it is preferred since it is less likely to be confused with other
110 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
111 superset of the functionality offered by SELECT INTO.
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
121 also be used:
122 CREATE TABLE films2 AS
124 TABLE films;
125 Create a new temporary table films_recent, consisting of only recent
127 entries from the table films, using a prepared statement. The new table
128 will be dropped at commit:
129 PREPARE recentfilms(date) AS
131 SELECT * FROM films WHERE date_prod > $1;
132 CREATE TEMP TABLE films_recent ON COMMIT DROP AS
133 EXECUTE recentfilms('2002-01-01');
134
136 CREATE TABLE AS conforms to the SQL standard. The following are
137 nonstandard extensions:
138 • The standard requires parentheses around the subquery clause; in
140 PostgreSQL, these parentheses are optional.
141 • In the standard, the WITH [ NO ] DATA clause is required; in
143 PostgreSQL it is optional.
144 • PostgreSQL handles temporary tables in a way rather different from
146 the standard; see CREATE TABLE (CREATE_TABLE(7)) for details.
147 • The WITH clause is a PostgreSQL extension; storage parameters are
149 not in the standard.
150 • The PostgreSQL concept of tablespaces is not part of the standard.
152 Hence, the clause TABLESPACE is an extension.
153
155 CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), CREATE TABLE
156 (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO (SELECT_INTO(7)),
157 VALUES(7)
158PostgreSQL 13.3 2021 CREATE TABLE AS(7)