{"Entry":{"collection":"sql","key":"create-table-as","name":"CREATE TABLE AS","aliases":["createtableas"],"metadata":{"aliases":["createtableas"],"changed_in":["7.1","7.2","7.4","8.0","8.2","8.4","9.1","9.2","9.5","12","19"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"removed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":true,"renamed":{"from_file":"sql-createtableas.htm","to_file":"sql-createtableas.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ TEMPORARY | TEMP ] TABLE table [ (column [, ...] ) ]"],"removed":[]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":["parameters","diagnostics","notes","compatibility","history","see_also"],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ]","AS query"],"removed":["CREATE [ TEMPORARY | TEMP ] TABLE table [ (column [, ...] ) ]","AS select_clause"]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","compatibility","see_also"],"removed":["diagnostics","history"]},"status":"changed","synopsis":{"added":["CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ]"],"removed":[]},"to":"7.4"},{"from":"7.4","purpose_changed":true,"renamed":null,"sections":{"added":["examples"],"changed":["parameters","notes","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]"],"removed":[]},"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]","[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]","[ TABLESPACE tablespace ]"],"removed":["[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]"]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH [ NO ] DATA ]"],"removed":[]},"to":"8.4"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name"],"removed":[]},"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["[ TABLESPACE tablespace_name ]"],"removed":["[ TABLESPACE tablespace ]"]},"to":"9.2"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name"],"removed":[]},"to":"9.5"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ USING method ]","[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]"],"removed":["[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]"]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ persistence_mode ] TABLE [ IF NOT EXISTS ] table_name","{ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } } | UNLOGGED"],"removed":["CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name"]},"to":"19"}],"content_hash":"0d9e21d67631b528ee33b0229d18f3d0bc56ea48baea4db649591f1b44849645","editorial":{},"first_version":"6.4","group":"table","imported_at":"2026-09-30T17:43:36.711277+08:00","last_version":"20","name":"CREATE TABLE AS","object":"TABLE AS","position":11,"present_in":["6.4","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new table from the results of a query","purpose_zh":"","related":["create-materialized-view","create-table","execute","select","select-into","values"],"slug":"create-table-as","source_rev":"a709ab85","synopsis":"CREATE [ persistence_mode ] TABLE [ IF NOT EXISTS ] table_name\n[ ( column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]\n\nwhere persistence_mode is:\n\n{ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } } | UNLOGGED","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-table-as","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-table-as","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATETABLEAS","file":"sql-createtableas.html","lang":"en","name":"CREATE TABLE AS","purpose":"define a new table from the results of a query","purpose_zh":"","related":["create-materialized-view","create-table","execute","select","select-into","values"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e creates a table and fills it with data computed by a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e command. The table columns have the names and data types associated with the output columns of the \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e (except that you can override the column names by giving an explicit list of new column names).\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement whenever it is queried.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e requires \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the schema used for the table.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eGLOBAL\u003c/code\u003e or \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIgnored for compatibility. Use of these keywords is deprecated; refer to \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e or \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf specified, the table is created as a temporary table. Refer to \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf specified, the table is created as an unlogged table. Refer to \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not throw an error if a relation with the same name already exists; simply issue a notice and leave the table unmodified.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table to be created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a column in the new table. If column names are not provided, they are taken from the output column names of the query.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUSING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis optional clause specifies the table access method to use to store the contents for the new table; the method needs be an access method of type \u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e. See \u003ca href=\"/docs/18/tableam.html\" title=\"Chapter 62. Table Access Method Interface Definition\"\u003eChapter 62\u003c/a\u003e for more information. If this option is not specified, the default table access method is chosen for the new table. See \u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\"\u003edefault_table_access_method\u003c/a\u003e for more information.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional storage parameters for the new table; see \u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"Storage Parameters\"\u003eStorage Parameters\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e documentation for more information. For backward-compatibility the \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause for a table can also include \u003ccode class=\"literal\"\u003eOIDS=FALSE\u003c/code\u003e to specify that rows of the new table should contain no OIDs (object identifiers), \u003ccode class=\"literal\"\u003eOIDS=TRUE\u003c/code\u003e is not supported anymore.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITHOUT OIDS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis is backward-compatible syntax for declaring a table \u003ccode class=\"literal\"\u003eWITHOUT OIDS\u003c/code\u003e, creating a table \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e is not supported anymore.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eON COMMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe behavior of temporary tables at the end of a transaction block can be controlled using \u003ccode class=\"literal\"\u003eON COMMIT\u003c/code\u003e. The three options are:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePRESERVE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eNo special action is taken at the ends of transactions. This is the default behavior.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDELETE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAll rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic \u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003ccode class=\"command\"\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e is done at each commit.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe temporary table will be dropped at the end of the current transaction block.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e is the name of the tablespace in which the new table is to be created. If not specified, \u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\"\u003edefault_tablespace\u003c/a\u003e is consulted, or \u003ca href=\"/docs/18/runtime-config-client.html#GUC-TEMP-TABLESPACES\"\u003etemp_tablespaces\u003c/a\u003e if the table is temporary.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE Command\"\u003e\u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e\u003c/a\u003e, or \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e command, or an \u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\"\u003e\u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e\u003c/a\u003e command that runs a prepared \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e, \u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e query.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies whether or not the data produced by the query should be copied into the new table. If not, only the table structure is copied. The default is to copy the data.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eThis command is functionally similar to \u003ca href=\"/docs/18/sql-selectinto.html\" title=\"SELECT INTO\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e, but it is preferred since it is less likely to be confused with other uses of the \u003ccode class=\"command\"\u003eSELECT INTO\u003c/code\u003e syntax. Furthermore, \u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e offers a superset of the functionality offered by \u003ccode class=\"command\"\u003eSELECT INTO\u003c/code\u003e.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate a new table \u003ccode class=\"literal\"\u003efilms_recent\u003c/code\u003e consisting of only recent entries from the table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE films_recent AS\n  SELECT * FROM films WHERE date_prod \u0026gt;= '2002-01-01';\n\u003c/pre\u003e\u003cp\u003eTo copy a table completely, the short form using the \u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e command can also be used:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE films2 AS\n  TABLE films;\n\u003c/pre\u003e\u003cp\u003eCreate a new temporary table \u003ccode class=\"literal\"\u003efilms_recent\u003c/code\u003e, consisting of only recent entries from the table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e, using a prepared statement. The new table will be dropped at commit:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE recentfilms(date) AS\n  SELECT * FROM films WHERE date_prod \u0026gt; $1;\nCREATE TEMP TABLE films_recent ON COMMIT DROP AS\n  EXECUTE recentfilms('2002-01-01');\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard. The following are nonstandard extensions:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist compact\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe standard requires parentheses around the subquery clause; in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, these parentheses are optional.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eIn the standard, the \u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e clause is required; in PostgreSQL it is optional.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e handles temporary tables in a way rather different from the standard; see \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension; storage parameters are not in the standard.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e concept of tablespaces is not part of the standard. Hence, the clause \u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e is an extension.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table/?v=18\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select-into/?v=18\" title=\"SELECT INTO\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/values/?v=18\" title=\"VALUES\"\u003e\u003cspan class=\"refentrytitle\"\u003eVALUES\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n    [ (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) | WITHOUT OIDS ]\n    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n    [ TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ NO ] DATA ]","synopsis_text":"CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-table-as","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE TABLE AS","Summary":"根据查询结果定义一个新表","BodyHTML":"\u003cpre\u003eCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e创建一个表，并用 \u003ccode\u003eSELECT\u003c/code\u003e命令计算得到的数据填充该表。该表的列具有与\u003ccode\u003eSELECT\u003c/code\u003e输出列对应的名称和数据类型（但可以通过显式给出新的列名列表来覆盖列名）。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e与创建视图有些相似，但实际上差异很大：它会创建一个新表，并且只执行一次该查询来完成新表的初始填充。这个新表不会跟踪该查询所引用源表的后续变化。相比之下，视图每次被查询时都会重新执行其定义中的 \u003ccode\u003eSELECT\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e要求在用于该表的模式上具有 \u003ccode\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eGLOBAL\u003c/code\u003e 或 \u003ccode\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e出于兼容性考虑，这些关键字会被忽略。它们的用法已废弃；详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定，将把该表创建为临时表。详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定，该表将创建为不记录 WAL 的表。详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果已存在同名关系，则不抛出错误，而是发出一条提示，并保持现有关系不变。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的表名（可选地带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新表中某一列的名称。如果未提供列名，则采用查询输出列的列名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eUSING \u003cem\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个可选子句指定用于存储新表内容的表访问方法；该方法必须是 \u003ccode\u003eTABLE\u003c/code\u003e类型的访问方法。详见\u003ca href=\"/docs/18/tableam.html\" rel=\"nofollow\"\u003e第 62 章\u003c/a\u003e。如果未指定此选项，则会为新表选择默认的表访问方法。详见\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\" rel=\"nofollow\"\u003edefault_table_access_method\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH ( \u003cem\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个子句为新表指定可选的存储参数；更多信息见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"存储参数\" rel=\"nofollow\"\u003eStorage Parameters\u003c/a\u003e。为保持向后兼容，表的\u003ccode\u003eWITH\u003c/code\u003e子句也可以包含 \u003ccode\u003eOIDS=FALSE\u003c/code\u003e，用以指定新表的行不包含 OID（对象标识符）；不再支持\u003ccode\u003eOIDS=TRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITHOUT OIDS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这是声明表 \u003ccode\u003eWITHOUT OIDS\u003c/code\u003e 的向后兼容语法；创建 \u003ccode\u003eWITH OIDS\u003c/code\u003e 表已不再受支持。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eON COMMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可以使用\u003ccode\u003eON COMMIT\u003c/code\u003e控制临时表在事务块结束时的行为。三种选项是：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003ePRESERVE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在事务结束时不采取特殊动作。这是默认行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDELETE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每个事务块结束时都会删除临时表中的所有行。实质上，每次提交时都会自动执行一次\u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\" rel=\"nofollow\"\u003e\u003ccode\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDROP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e临时表会在当前事务块结束时被删除。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTABLESPACE \u003cem\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e是要在其中创建新表的表空间名称。如果未指定，则查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\" rel=\"nofollow\"\u003edefault_tablespace\u003c/a\u003e；如果该表是临时表，则查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-TEMP-TABLESPACES\" rel=\"nofollow\"\u003etemp_tablespaces\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE 命令\" rel=\"nofollow\"\u003e\u003ccode\u003eTABLE\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\" rel=\"nofollow\"\u003e\u003ccode\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令，或者一个\u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\" rel=\"nofollow\"\u003e\u003ccode\u003eEXECUTE\u003c/code\u003e\u003c/a\u003e 命令，用于执行一条已预备的\u003ccode\u003eSELECT\u003c/code\u003e、\u003ccode\u003eTABLE\u003c/code\u003e或\u003ccode\u003eVALUES\u003c/code\u003e查询。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个子句指定是否将查询产生的数据复制到新表中。如果不复制，则只复制表结构。默认会复制数据。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e这个命令在功能上类似于\u003ca href=\"/docs/18/sql-selectinto.html\" title=\"SELECT INTO\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e，但更推荐使用它，因为它更不容易与\u003ccode\u003eSELECT INTO\u003c/code\u003e语法的其他用法相混淆。此外，\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e提供的功能是\u003ccode\u003eSELECT INTO\u003c/code\u003e所提供功能的超集。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e创建一个新表\u003ccode\u003efilms_recent\u003c/code\u003e，其中只包含表 \u003ccode\u003efilms\u003c/code\u003e中的近期条目：\u003c/p\u003e\u003cpre\u003eCREATE TABLE films_recent AS\n  SELECT * FROM films WHERE date_prod \u0026gt;= \u0026#39;2002-01-01\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e要完整复制一个表，也可以使用\u003ccode\u003eTABLE\u003c/code\u003e命令的简写形式：\u003c/p\u003e\u003cpre\u003eCREATE TABLE films2 AS\n  TABLE films;\n\u003c/pre\u003e\u003cp\u003e使用预备语句创建一个新的临时表\u003ccode\u003efilms_recent\u003c/code\u003e，其中只包含表\u003ccode\u003efilms\u003c/code\u003e中的近期条目。新表将在提交时被删除：\u003c/p\u003e\u003cpre\u003ePREPARE recentfilms(date) AS\n  SELECT * FROM films WHERE date_prod \u0026gt; $1;\nCREATE TEMP TABLE films_recent ON COMMIT DROP AS\n  EXECUTE recentfilms(\u0026#39;2002-01-01\u0026#39;);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e 标准。以下是非标准扩展：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e标准要求在子查询子句外围加括号；在 \u003cspan\u003ePostgreSQL\u003c/span\u003e中，这些括号是可选的。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e在标准中，\u003ccode\u003eWITH [ NO ] DATA\u003c/code\u003e子句是必需的；在 PostgreSQL 中它是可选的。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e处理临时表的方式与标准有相当大的不同；详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003eWITH\u003c/code\u003e子句是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展；标准中没有存储参数。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e的表空间概念不是标准的一部分。因此，\u003ccode\u003eTABLESPACE\u003c/code\u003e子句是一种扩展。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table/?v=18\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\" rel=\"nofollow\"\u003e\u003cspan\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select-into/?v=18\" title=\"SELECT INTO\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/values/?v=18\" title=\"VALUES\" rel=\"nofollow\"\u003e\u003cspan\u003eVALUES\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"bc2359d299cc379977cbf1a73349385c048810229202cb1f1f006d2edaee0bbd","Payload":{"purpose_zh":"根据查询结果定义一个新表","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e创建一个表，并用 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e命令计算得到的数据填充该表。该表的列具有与\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e输出列对应的名称和数据类型（但可以通过显式给出新的列名列表来覆盖列名）。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e与创建视图有些相似，但实际上差异很大：它会创建一个新表，并且只执行一次该查询来完成新表的初始填充。这个新表不会跟踪该查询所引用源表的后续变化。相比之下，视图每次被查询时都会重新执行其定义中的 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e要求在用于该表的模式上具有 \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eGLOBAL\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e出于兼容性考虑，这些关键字会被忽略。它们的用法已废弃；详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定，将把该表创建为临时表。详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定，该表将创建为不记录 WAL 的表。详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果已存在同名关系，则不抛出错误，而是发出一条提示，并保持现有关系不变。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的表名（可选地带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新表中某一列的名称。如果未提供列名，则采用查询输出列的列名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUSING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个可选子句指定用于存储新表内容的表访问方法；该方法必须是 \u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e类型的访问方法。详见\u003ca href=\"/docs/18/tableam.html\" title=\"第 62 章 表访问方法接口定义\"\u003e第 62 章\u003c/a\u003e。如果未指定此选项，则会为新表选择默认的表访问方法。详见\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\"\u003edefault_table_access_method\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个子句为新表指定可选的存储参数；更多信息见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"存储参数\"\u003eStorage Parameters\u003c/a\u003e。为保持向后兼容，表的\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e子句也可以包含 \u003ccode class=\"literal\"\u003eOIDS=FALSE\u003c/code\u003e，用以指定新表的行不包含 OID（对象标识符）；不再支持\u003ccode class=\"literal\"\u003eOIDS=TRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITHOUT OIDS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这是声明表 \u003ccode class=\"literal\"\u003eWITHOUT OIDS\u003c/code\u003e 的向后兼容语法；创建 \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e 表已不再受支持。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eON COMMIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可以使用\u003ccode class=\"literal\"\u003eON COMMIT\u003c/code\u003e控制临时表在事务块结束时的行为。三种选项是：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePRESERVE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在事务结束时不采取特殊动作。这是默认行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDELETE ROWS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每个事务块结束时都会删除临时表中的所有行。实质上，每次提交时都会自动执行一次\u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003ccode class=\"command\"\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e临时表会在当前事务块结束时被删除。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e是要在其中创建新表的表空间名称。如果未指定，则查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\"\u003edefault_tablespace\u003c/a\u003e；如果该表是临时表，则查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-TEMP-TABLESPACES\"\u003etemp_tablespaces\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE 命令\"\u003e\u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令，或者一个\u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\"\u003e\u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e\u003c/a\u003e 命令，用于执行一条已预备的\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e、\u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e或\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e查询。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这个子句指定是否将查询产生的数据复制到新表中。如果不复制，则只复制表结构。默认会复制数据。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e这个命令在功能上类似于\u003ca href=\"/docs/18/sql-selectinto.html\" title=\"SELECT INTO\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e，但更推荐使用它，因为它更不容易与\u003ccode class=\"command\"\u003eSELECT INTO\u003c/code\u003e语法的其他用法相混淆。此外，\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e提供的功能是\u003ccode class=\"command\"\u003eSELECT INTO\u003c/code\u003e所提供功能的超集。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e创建一个新表\u003ccode class=\"literal\"\u003efilms_recent\u003c/code\u003e，其中只包含表 \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e中的近期条目：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE films_recent AS\n  SELECT * FROM films WHERE date_prod \u0026gt;= '2002-01-01';\n\u003c/pre\u003e\u003cp\u003e要完整复制一个表，也可以使用\u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e命令的简写形式：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE TABLE films2 AS\n  TABLE films;\n\u003c/pre\u003e\u003cp\u003e使用预备语句创建一个新的临时表\u003ccode class=\"literal\"\u003efilms_recent\u003c/code\u003e，其中只包含表\u003ccode class=\"literal\"\u003efilms\u003c/code\u003e中的近期条目。新表将在提交时被删除：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE recentfilms(date) AS\n  SELECT * FROM films WHERE date_prod \u0026gt; $1;\nCREATE TEMP TABLE films_recent ON COMMIT DROP AS\n  EXECUTE recentfilms('2002-01-01');\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e 标准。以下是非标准扩展：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist compact\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e标准要求在子查询子句外围加括号；在 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e中，这些括号是可选的。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e在标准中，\u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e子句是必需的；在 PostgreSQL 中它是可选的。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e处理临时表的方式与标准有相当大的不同；详见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e子句是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展；标准中没有存储参数。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的表空间概念不是标准的一部分。因此，\u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e子句是一种扩展。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table/?v=18\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select-into/?v=18\" title=\"SELECT INTO\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT INTO\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/values/?v=18\" title=\"VALUES\"\u003e\u003cspan class=\"refentrytitle\"\u003eVALUES\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n    [ (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) | WITHOUT OIDS ]\n    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n    [ TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ NO ] DATA ]","synopsis_text":"CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}