1CREATE TABLE AS(7) PostgreSQL 14.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 CREATE TABLE AS requires CREATE privilege on the schema used for the
32 table.
33
35 GLOBAL or LOCAL
36 Ignored for compatibility. Use of these keywords is deprecated;
37 refer to CREATE TABLE (CREATE_TABLE(7)) for details.
38 TEMPORARY or TEMP
40 If specified, the table is created as a temporary table. Refer to
41 CREATE TABLE (CREATE_TABLE(7)) for details.
42 UNLOGGED
44 If specified, the table is created as an unlogged table. Refer to
45 CREATE TABLE (CREATE_TABLE(7)) for details.
46 IF NOT EXISTS
48 Do not throw an error if a relation with the same name already
49 exists. A notice is issued in this case. Refer to CREATE TABLE
50 (CREATE_TABLE(7)) for details.
51 table_name
53 The name (optionally schema-qualified) of the table to be created.
54 column_name
56 The name of a column in the new table. If column names are not
57 provided, they are taken from the output column names of the query.
58 USING method
60 This optional clause specifies the table access method to use to
61 store the contents for the new table; the method needs be an access
62 method of type TABLE. See Chapter 61 for more information. If this
63 option is not specified, the default table access method is chosen
64 for the new table. See default_table_access_method for more
65 information.
66 WITH ( storage_parameter [= value] [, ... ] )
68 This clause specifies optional storage parameters for the new
69 table; see Storage Parameters in the CREATE TABLE (CREATE_TABLE(7))
70 documentation for more information. For backward-compatibility the
71 WITH clause for a table can also include OIDS=FALSE to specify that
72 rows of the new table should contain no OIDs (object identifiers),
73 OIDS=TRUE is not supported anymore.
74 WITHOUT OIDS
76 This is backward-compatible syntax for declaring a table WITHOUT
77 OIDS, creating a table WITH OIDS is not supported anymore.
78 ON COMMIT
80 The behavior of temporary tables at the end of a transaction block
81 can be controlled using ON COMMIT. The three options are:
82 PRESERVE ROWS
84 No special action is taken at the ends of transactions. This is
85 the default behavior.
86 DELETE ROWS
88 All rows in the temporary table will be deleted at the end of
89 each transaction block. Essentially, an automatic TRUNCATE is
90 done at each commit.
91 DROP
93 The temporary table will be dropped at the end of the current
94 transaction block.
95 TABLESPACE tablespace_name
97 The tablespace_name is the name of the tablespace in which the new
98 table is to be created. If not specified, default_tablespace is
99 consulted, or temp_tablespaces if the table is temporary.
100 query
102 A SELECT, TABLE, or VALUES command, or an EXECUTE command that runs
103 a prepared SELECT, TABLE, or VALUES query.
104 WITH [ NO ] DATA
106 This clause specifies whether or not the data produced by the query
107 should be copied into the new table. If not, only the table
108 structure is copied. The default is to copy the data.
109
111 This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
112 but it is preferred since it is less likely to be confused with other
113 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
114 superset of the functionality offered by SELECT INTO.
115
117 Create a new table films_recent consisting of only recent entries from
118 the table films:
119 CREATE TABLE films_recent AS
121 SELECT * FROM films WHERE date_prod >= '2002-01-01';
122 To copy a table completely, the short form using the TABLE command can
124 also be used:
125 CREATE TABLE films2 AS
127 TABLE films;
128 Create a new temporary table films_recent, consisting of only recent
130 entries from the table films, using a prepared statement. The new table
131 will be dropped at commit:
132 PREPARE recentfilms(date) AS
134 SELECT * FROM films WHERE date_prod > $1;
135 CREATE TEMP TABLE films_recent ON COMMIT DROP AS
136 EXECUTE recentfilms('2002-01-01');
137
139 CREATE TABLE AS conforms to the SQL standard. The following are
140 nonstandard 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
146 PostgreSQL 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; storage parameters are
152 not 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 MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), CREATE TABLE
159 (CREATE_TABLE(7)), EXECUTE(7), SELECT(7), SELECT INTO (SELECT_INTO(7)),
160 VALUES(7)
161PostgreSQL 14.3 2022 CREATE TABLE AS(7)