> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE TABLE AS

The `CREATE TABLE ... AS` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) creates a new table from a [selection query](https://docs.cockroachlabs.com/docs/v24.2/selection-queries).

> [!NOTE]
>
> The `CREATE TABLE AS` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes).

## 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 [view](https://docs.cockroachlabs.com/docs/v24.2/create-view) 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.

> [!NOTE]
>
> The default rules for [column families](https://docs.cockroachlabs.com/docs/v24.2/column-families) apply.

## Required privileges

The user must have the `CREATE` [privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) on the parent database.

## Synopsis

> [tab] Basic
>
> ![create\_table\_as syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_table_as.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=7f8abedbf217331546580d47d9df3019)

> [tab] Expanded
>
> ![create\_table\_as syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_table_as.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=7f8abedbf217331546580d47d9df3019)
>
> **create\_as\_col\_qual\_list ::=**
>
> ![create\_as\_col\_qual\_list syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_as_col_qual_list.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=80fb4e5a1d551864f0b66dd093bcda0a)
>
> **create\_as\_constraint\_def ::=**
>
> ![create\_as\_constraint\_def syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_as_constraint_def.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=16ec919e35a27113103be7b5cc9fb9ca)
>
> **opt\_with\_storage\_parameter\_list ::=**
>
> ![opt\_with\_storage\_parameter\_list syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/opt_with_storage_parameter_list.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=e9f1caf42aebd642f5c357584cebfac1)

## 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 [identifier rules](https://docs.cockroachlabs.com/docs/v24.2/keywords-and-identifiers#identifiers). When the parent database is not set as the default, the name must be formatted as `database.name`. The [`UPSERT`](https://docs.cockroachlabs.com/docs/v24.2/upsert) and [`INSERT ON CONFLICT`](https://docs.cockroachlabs.com/docs/v24.2/insert) 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 [primary key constraints](https://docs.cockroachlabs.com/docs/v24.2/primary-key) and [column family assignments](https://docs.cockroachlabs.com/docs/v24.2/column-families). |
| `family_def` | An optional [column family definition](https://docs.cockroachlabs.com/docs/v24.2/column-families). 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 [primary key constraint](https://docs.cockroachlabs.com/docs/v24.2/primary-key). |
| `select_stmt` | A [selection query](https://docs.cockroachlabs.com/docs/v24.2/selection-queries) to provide the data. |
| `opt_persistence_temp_table` | Defines the table as a session-scoped temporary table. For more information, see [Temporary Tables](https://docs.cockroachlabs.com/docs/v24.2/temporary-tables). Note that the `LOCAL`, `GLOBAL`, and `UNLOGGED` options are no-ops, allowed by the parser for PostgreSQL compatibility. **Support for temporary tables is [in preview](https://docs.cockroachlabs.com/docs/releases/cockroachdb-feature-availability.md)**. |
| `opt_with_storage_parameter_list` | A comma-separated list of [spatial index tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#index-tuning-parameters). 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 [Spatial index tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#index-tuning-parameters). For an example, see [Create a spatial index that uses all of the tuning parameters](https://docs.cockroachlabs.com/docs/v24.2/spatial-indexes#create-a-spatial-index-that-uses-all-of-the-tuning-parameters). |
| `ON COMMIT PRESERVE ROWS` | This clause is a no-op, allowed by the parser for PostgreSQL compatibility. CockroachDB only supports session-scoped [temporary tables](https://docs.cockroachlabs.com/docs/v24.2/temporary-tables), 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 [primary key](https://docs.cockroachlabs.com/docs/v24.2/primary-key) 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 [Specify a primary key](https://docs.cockroachlabs.com/docs/v24.2/create-table-as#specify-a-primary-key).

## Examples

#### Setup

To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/v24.2/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/v24.2/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

### Create a table from a `SELECT` query

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users_ny AS SELECT * FROM users WHERE city = 'new york';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users_ny_names (user_id, user_name) AS SELECT id, name FROM users WHERE city = 'new york';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE drivers (id, city, name) AS VALUES (gen_random_uuid(), 'new york', 'Harry Potter'), (gen_random_uuid(), 'seattle', 'Evelyn Martin');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users_ny_copy AS TABLE users_ny;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 [primary key](https://docs.cockroachlabs.com/docs/v24.2/primary-key) of a new table created from a selection query:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users_ny_pk (id, city, name PRIMARY KEY) AS SELECT id, city, name FROM users WHERE city = 'new york';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 [column families](https://docs.cockroachlabs.com/docs/v24.2/column-families) of a new table created from a selection query:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

## See also

* [Selection Queries](https://docs.cockroachlabs.com/docs/v24.2/selection-queries)
* [Simple `SELECT` Clause](https://docs.cockroachlabs.com/docs/v24.2/select-clause)
* [`CREATE TABLE`](https://docs.cockroachlabs.com/docs/v24.2/create-table)
* [`CREATE VIEW`](https://docs.cockroachlabs.com/docs/v24.2/create-view)
* [`INSERT`](https://docs.cockroachlabs.com/docs/v24.2/insert)
* [`DROP TABLE`](https://docs.cockroachlabs.com/docs/v24.2/drop-table)
* [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements)
* [`ALTER PRIMARY KEY`](https://docs.cockroachlabs.com/docs/v24.2/alter-table#alter-primary-key)
* [`ALTER TABLE`](https://docs.cockroachlabs.com/docs/v24.2/alter-table)
* [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)