1CREATE TABLE AS(7) PostgreSQL 12.2 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 for more information. For
67 backward-compatibility the WITH clause for a table can also include
68 OIDS=FALSE to specify that rows of the new table should contain no
69 OIDs (object identifiers), OIDS=TRUE is not supported anymore.
70 WITHOUT OIDS
72 This is backward-compatible syntax for declaring a table WITHOUT
73 OIDS, creating a table WITH OIDS is not supported anymore.
74 ON COMMIT
76 The behavior of temporary tables at the end of a transaction block
77 can be controlled using ON COMMIT. The three options are:
78 PRESERVE ROWS
80 No special action is taken at the ends of transactions. This is
81 the default behavior.
82 DELETE ROWS
84 All rows in the temporary table will be deleted at the end of
85 each transaction block. Essentially, an automatic TRUNCATE(7)
86 is done at each commit.
87 DROP
89 The temporary table will be dropped at the end of the current
90 transaction block.
91 TABLESPACE tablespace_name
93 The tablespace_name is the name of the tablespace in which the new
94 table is to be created. If not specified, default_tablespace is
95 consulted, or temp_tablespaces if the table is temporary.
96 query
98 A SELECT(7), TABLE, or VALUES(7) command, or an EXECUTE(7) command
99 that runs a prepared SELECT, TABLE, or VALUES query.
100 WITH [ NO ] DATA
102 This clause specifies whether or not the data produced by the query
103 should be copied into the new table. If not, only the table
104 structure is copied. The default is to copy the data.
105
107 This command is functionally similar to SELECT INTO (SELECT_INTO(7)),
108 but it is preferred since it is less likely to be confused with other
109 uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a
110 superset of the functionality offered by SELECT INTO.
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 will be dropped at commit:
128 PREPARE recentfilms(date) AS
130 SELECT * FROM films WHERE date_prod > $1;
131 CREATE TEMP TABLE films_recent 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; storage parameters are
148 not 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 12.2 2020 CREATE TABLE AS(7)