{
    "mode": "man",
    "parameter": "CREATE_TABLE_AS",
    "section": "7",
    "url": "https://www.chedong.com/phpMan.php/man/CREATE_TABLE_AS/7/json",
    "generated": "2026-10-08T09:21:45Z",
    "synopsis": "CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\n[ (columnname [, ...] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nAS query\n[ WITH [ NO ] DATA ]",
    "sections": {
        "NAME": {
            "content": "CREATETABLEAS - define a new table from the results of a query\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\n[ (columnname [, ...] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nAS query\n[ WITH [ NO ] DATA ]\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The\ntable columns have the names and data types associated with the output columns of the SELECT\n(except that you can override the column names by giving an explicit list of new column\nnames).\n\nCREATE TABLE AS bears some resemblance to creating a view, but it is really quite different:\nit creates a new table and evaluates the query just once to fill the new table initially. The\nnew table will not track subsequent changes to the source tables of the query. In contrast, a\nview re-evaluates its defining SELECT statement whenever it is queried.\n\nCREATE TABLE AS requires CREATE privilege on the schema used for the table.\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "GLOBAL or LOCAL\nIgnored for compatibility. Use of these keywords is deprecated; refer to CREATE TABLE\n(CREATETABLE(7)) for details.\n\nTEMPORARY or TEMP\nIf specified, the table is created as a temporary table. Refer to CREATE TABLE\n(CREATETABLE(7)) for details.\n\nUNLOGGED\nIf specified, the table is created as an unlogged table. Refer to CREATE TABLE\n(CREATETABLE(7)) for details.\n\nIF NOT EXISTS\nDo not throw an error if a relation with the same name already exists; simply issue a\nnotice and leave the table unmodified.\n\ntablename\nThe name (optionally schema-qualified) of the table to be created.\n\ncolumnname\nThe name of a column in the new table. If column names are not provided, they are taken\nfrom the output column names of the query.\n\nUSING method\nThis optional clause specifies the table access method to use to store the contents for\nthe new table; the method needs be an access method of type TABLE. See Chapter 63 for\nmore information. If this option is not specified, the default table access method is\nchosen for the new table. See defaulttableaccessmethod for more information.\n\nWITH ( storageparameter [= value] [, ... ] )\nThis clause specifies optional storage parameters for the new table; see Storage\nParameters in the CREATE TABLE (CREATETABLE(7)) documentation for more information. For\nbackward-compatibility the WITH clause for a table can also include OIDS=FALSE to specify\nthat rows of the new table should contain no OIDs (object identifiers), OIDS=TRUE is not\nsupported anymore.\n\nWITHOUT OIDS\nThis is backward-compatible syntax for declaring a table WITHOUT OIDS, creating a table\nWITH OIDS is not supported anymore.\n\nON COMMIT\nThe behavior of temporary tables at the end of a transaction block can be controlled\nusing ON COMMIT. The three options are:\n\nPRESERVE ROWS\nNo special action is taken at the ends of transactions. This is the default behavior.\n\nDELETE ROWS\nAll rows in the temporary table will be deleted at the end of each transaction block.\nEssentially, an automatic TRUNCATE is done at each commit.\n\nDROP\nThe temporary table will be dropped at the end of the current transaction block.\n\nTABLESPACE tablespacename\nThe tablespacename is the name of the tablespace in which the new table is to be\ncreated. If not specified, defaulttablespace is consulted, or temptablespaces if the\ntable is temporary.\n\nquery\nA SELECT, TABLE, or VALUES command, or an EXECUTE command that runs a prepared SELECT,\nTABLE, or VALUES query.\n\nWITH [ NO ] DATA\nThis clause specifies whether or not the data produced by the query should be copied into\nthe new table. If not, only the table structure is copied. The default is to copy the\ndata.\n",
            "subsections": []
        },
        "NOTES": {
            "content": "This command is functionally similar to SELECT INTO (SELECTINTO(7)), but it is preferred\nsince it is less likely to be confused with other uses of the SELECT INTO syntax.\nFurthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.\n",
            "subsections": []
        },
        "EXAMPLES": {
            "content": "Create a new table filmsrecent consisting of only recent entries from the table films:\n\nCREATE TABLE filmsrecent AS\nSELECT * FROM films WHERE dateprod >= '2002-01-01';\n\nTo copy a table completely, the short form using the TABLE command can also be used:\n\nCREATE TABLE films2 AS\nTABLE films;\n\nCreate a new temporary table filmsrecent, consisting of only recent entries from the table\nfilms, using a prepared statement. The new table will be dropped at commit:\n\nPREPARE recentfilms(date) AS\nSELECT * FROM films WHERE dateprod > $1;\nCREATE TEMP TABLE filmsrecent ON COMMIT DROP AS\nEXECUTE recentfilms('2002-01-01');\n",
            "subsections": []
        },
        "COMPATIBILITY": {
            "content": "CREATE TABLE AS conforms to the SQL standard. The following are nonstandard extensions:\n\n•   The standard requires parentheses around the subquery clause; in PostgreSQL, these\nparentheses are optional.\n\n•   In the standard, the WITH [ NO ] DATA clause is required; in PostgreSQL it is optional.\n\n•   PostgreSQL handles temporary tables in a way rather different from the standard; see\nCREATE TABLE (CREATETABLE(7)) for details.\n\n•   The WITH clause is a PostgreSQL extension; storage parameters are not in the standard.\n\n•   The PostgreSQL concept of tablespaces is not part of the standard. Hence, the clause\nTABLESPACE is an extension.\n",
            "subsections": []
        },
        "SEE ALSO": {
            "content": "CREATE MATERIALIZED VIEW (CREATEMATERIALIZEDVIEW(7)), CREATE TABLE (CREATETABLE(7)),\nEXECUTE(7), SELECT(7), SELECT INTO (SELECTINTO(7)), VALUES(7)\n\nPostgreSQL 16.14                                2026                              CREATE TABLE AS(7)",
            "subsections": []
        }
    },
    "summary": "CREATETABLEAS - define a new table from the results of a query",
    "flags": [],
    "examples": [
        "Create a new table filmsrecent consisting of only recent entries from the table films:",
        "CREATE TABLE filmsrecent AS",
        "SELECT * FROM films WHERE dateprod >= '2002-01-01';",
        "To copy a table completely, the short form using the TABLE command can also be used:",
        "CREATE TABLE films2 AS",
        "TABLE films;",
        "Create a new temporary table filmsrecent, consisting of only recent entries from the table",
        "films, using a prepared statement. The new table will be dropped at commit:",
        "PREPARE recentfilms(date) AS",
        "SELECT * FROM films WHERE dateprod > $1;",
        "CREATE TEMP TABLE filmsrecent ON COMMIT DROP AS",
        "EXECUTE recentfilms('2002-01-01');"
    ],
    "see_also": [
        {
            "name": "CREATEMATERIALIZEDVIEW",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/CREATEMATERIALIZEDVIEW/7/json"
        },
        {
            "name": "CREATETABLE",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/CREATETABLE/7/json"
        },
        {
            "name": "EXECUTE",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/EXECUTE/7/json"
        },
        {
            "name": "SELECT",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/SELECT/7/json"
        },
        {
            "name": "SELECTINTO",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/SELECTINTO/7/json"
        },
        {
            "name": "VALUES",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/VALUES/7/json"
        },
        {
            "name": "AS",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/AS/7/json"
        }
    ]
}