CREATE TABLE AS

The CREATE TABLE... AS creates a new table from a .

The CREATE TABLE AS statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Intended use

Tables created with CREATE TABLE... AS are intended to persist the result of a query for later reuse.

This can be more efficient than a when the following two conditions are met:

• The result of the query is used as-is multiple times.
• The copy needs not be kept up-to-date with the original table over time.

When the results of a query are reused multiple times within a larger query, a view is advisable instead. The query optimizer can “peek”into the view and optimize the surrounding query using the primary key and indices of the tables mentioned in the view query.

A view is also advisable when the results must be up-to-date; a view always retrieves the current data from the tables that the view query mentions.

Use CREATE TABLE t AS... with the AS OF SYSTEM TIME clause to leverage to reduce contention and improve performance.

The default rules for apply.

Required privileges

The user must have the CREATE on the parent database.

Synopsis

Basic

Expanded

create_as_col_qual_list::=

create_as_constraint_def::=

opt_with_storage_parameter_list::=

Parameters

Parameter	Description
IF NOT EXISTS	Create a new table only if a table of the same name does not already exist in the database; if one does exist, do not return an error. Note that IF NOT EXISTS checks the table name only; it does not check if an existing table has the same columns, indexes, constraints, etc., of the new table.
table\_name	The name of the table to create, which must be unique within its database and follow these . When the parent database is not set as the default, the name must be formatted as database.name. The and statements use a temporary table called excluded to handle uniqueness conflicts during execution. It’s therefore not recommended to use the name excluded for any of your tables.
column\_name	The name of the column you want to use instead of the name of the column from select\_stmt.
create\_as\_col\_qual\_list	An optional column definition, which may include and .
family\_def	An optional . Column family names must be unique within the table but can have the same name as columns, constraints, or indexes.
create\_as\_constraint\_def	An optional .
select\_stmt	A to provide the data.
opt\_persistence\_temp\_table	Defines the table as a session-scoped temporary table. For more information, see . Note that the LOCAL, GLOBAL, and UNLOGGED options are no-ops, allowed by the parser for PostgreSQL compatibility. Support for temporary tables is .
opt\_with\_storage\_parameter\_list	A comma-separated list of . Supported parameters include fillfactor, s2\_max\_level, s2\_level\_mod, s2\_max\_cells, geometry\_min\_x, geometry\_max\_x, geometry\_min\_y, and geometry\_max\_y. The fillfactor parameter is a no-op, allowed for PostgreSQL-compatibility. For details, see . For an example, see .
ON COMMIT PRESERVE ROWS	This clause is a no-op, allowed by the parser for PostgreSQL compatibility. CockroachDB only supports session-scoped , and does not support the clauses ON COMMIT DELETE ROWS and ON COMMIT DROP, which are used to define transaction-scoped temporary tables in PostgreSQL.

Known limitations

The of tables created with CREATE TABLE... AS is not automatically derived from the query results. You must specify new primary keys at table creation. For examples, see .

Examples

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ cockroach demo

Create a table from a SELECT query

> SELECT * FROM users WHERE city = 'new york';

                   id                  |   city   |       name       |           address           | credit_card
+--------------------------------------+----------+------------------+-----------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills        | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57 | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61 | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8   | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley              | 0792553487
  19999999-9999-4a00-8000-000000000005 | new york | Nicole Mcmahon   | 11540 Patton Extensions     | 0303726947
(6 rows)

> CREATE TABLE users_ny AS SELECT * FROM users WHERE city = 'new york';

> SELECT * FROM users_ny;

                   id                  |   city   |       name       |           address           | credit_card
+--------------------------------------+----------+------------------+-----------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills        | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57 | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61 | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8   | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley              | 0792553487
  19999999-9999-4a00-8000-000000000005 | new york | Nicole Mcmahon   | 11540 Patton Extensions     | 0303726947
(6 rows)

Change column names

This statement creates a copy of an existing table but with changed column names:

> CREATE TABLE users_ny_names (user_id, user_name) AS SELECT id, name FROM users WHERE city = 'new york';

> SELECT * FROM users_ny_names;

                user_id                |    user_name
+--------------------------------------+------------------+
  00000000-0000-4000-8000-000000000000 | Robert Murphy
  051eb851-eb85-4ec0-8000-000000000001 | James Hamilton
  0a3d70a3-d70a-4d80-8000-000000000002 | Judy White
  0f5c28f5-c28f-4c00-8000-000000000003 | Devin Jordan
  147ae147-ae14-4b00-8000-000000000004 | Catherine Nelson
  19999999-9999-4a00-8000-000000000005 | Nicole Mcmahon
(6 rows)

Create a table from a VALUES clause

> CREATE TABLE drivers (id, city, name) AS VALUES (gen_random_uuid(), 'new york', 'Harry Potter'), (gen_random_uuid(), 'seattle', 'Evelyn Martin');

> SELECT * FROM drivers;

                   id                  |   city   |     name
+--------------------------------------+----------+---------------+
  146eebc4-c913-4678-8ea3-c5797d2b7f83 | new york | Harry Potter
  43cafd3b-2537-4fd8-a987-8138f88a22a4 | seattle  | Evelyn Martin
(2 rows)

Create a copy of an existing table

> CREATE TABLE users_ny_copy AS TABLE users_ny;

> SELECT * FROM users_ny_copy;

                   id                  |   city   |       name       |           address           | credit_card
+--------------------------------------+----------+------------------+-----------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills        | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57 | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61 | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8   | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley              | 0792553487
  19999999-9999-4a00-8000-000000000005 | new york | Nicole Mcmahon   | 11540 Patton Extensions     | 0303726947
(6 rows)

When a table copy is created this way, the copy is not associated to any primary key, secondary index, or constraint that was present on the original table.

Specify a primary key

You can specify the of a new table created from a selection query:

> CREATE TABLE users_ny_pk (id, city, name PRIMARY KEY) AS SELECT id, city, name FROM users WHERE city = 'new york';

> SELECT * FROM users_ny_pk;

                   id                  |   city   |       name
+--------------------------------------+----------+------------------+
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White
  19999999-9999-4a00-8000-000000000005 | new york | Nicole Mcmahon
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy
(6 rows)

> SHOW CREATE TABLE users_ny_pk;

    table_name   |                 create_statement
+----------------+--------------------------------------------------+
  users_ny_extra | CREATE TABLE users_ny_extra (
                 |     id UUID NULL,
                 |     city VARCHAR NULL,
                 |     name VARCHAR NOT NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (name ASC),
                 |     FAMILY "primary" (id, city, name)
                 | )
(1 row)

Define column families

You can define the of a new table created from a selection query:

> CREATE TABLE users_ny_alt (id PRIMARY KEY FAMILY ids, name, city FAMILY locs, address, credit_card FAMILY payments) AS SELECT id, name, city, address, credit_card FROM users WHERE city = 'new york';

> SELECT * FROM users_ny_alt;

                   id                  |       name       |   city   |           address           | credit_card
+--------------------------------------+------------------+----------+-----------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | Robert Murphy    | new york | 99176 Anderson Mills        | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | James Hamilton   | new york | 73488 Sydney Ports Suite 57 | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | Judy White       | new york | 18580 Rosario Ville Apt. 61 | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | Devin Jordan     | new york | 81127 Angela Ferry Apt. 8   | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | Catherine Nelson | new york | 1149 Lee Alley              | 0792553487
  19999999-9999-4a00-8000-000000000005 | Nicole Mcmahon   | new york | 11540 Patton Extensions     | 0303726947
(6 rows)

> SHOW CREATE TABLE users_ny_alt;

   table_name  |                create_statement
+--------------+------------------------------------------------+
  users_ny_alt | CREATE TABLE users_ny_alt (
               |     id UUID NOT NULL,
               |     name VARCHAR NULL,
               |     city VARCHAR NULL,
               |     address VARCHAR NULL,
               |     credit_card VARCHAR NULL,
               |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
               |     FAMILY ids (id, name, address),
               |     FAMILY locs (city),
               |     FAMILY payments (credit_card)
               | )
(1 row)

Populate CREATE TABLE AS with historical data using AS OF SYSTEM TIME

CockroachDB supports creating a table using historical data using the clause. You can use this to create a new table based on the state of an existing table as of a specific in the past. This is useful for:

• Generating static datasets for reporting or analytical workloads without increasing contention on production tables or otherwise impacting performance.
• Analyzing data changes over time.
• Preserving historical data for regulatory or investigative purposes.
• Undoing an accidental table deletion.

The timestamp must be within the of the source table for the data to be available.

The following example creates a new table from the dataset at the most recent timestamp that can perform a .

CREATE TABLE analysis_vehicle_location_histories
  AS SELECT * FROM movr.vehicle_location_histories
  AS OF SYSTEM TIME follower_read_timestamp();

NOTICE: CREATE TABLE ... AS does not copy over indexes, default expressions, or constraints; the new table has a hidden rowid primary key column
CREATE TABLE AS

Undo an accidental table deletion

The following steps use a table from the dataset to show how to undo an accidental table deletion using CREATE TABLE AS... AS OF SYSTEM TIME.

1. Get a timestamp before the table is deleted in an upcoming step:

   SELECT now();
   
                 now
   --------------------------------
     2025-06-17 15:04:15.82632+00
   (1 row)
   

2. Wait a few seconds to simulate time passing (adjust as needed):

   SELECT pg_sleep(5);
   
     pg_sleep
   ------------
        t
   (1 row)
   

3. Drop the original table:

   DROP TABLE movr.vehicle_location_histories;
   
   DROP TABLE
   

4. Restore the table using the clause and the timestamp you obtained before dropping the table:

   CREATE TABLE movr.vehicle_location_histories AS SELECT * FROM movr.vehicle_location_histories AS OF SYSTEM TIME '2025-06-17 15:04:15.82632+00';
   
   NOTICE: CREATE TABLE ... AS does not copy over indexes, default expressions, or constraints; the new table has a hidden rowid primary key column
   CREATE TABLE AS
   

For more information about historical reads at a given timestamp, refer to .

See also