> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# CREATE TABLE AS
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `CREATE TABLE... AS` statement creates a new table from a selection query.
The `CREATE TABLE AS` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see 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 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.
The default rules for column families apply.
## Required privileges
The user must have the `CREATE` privilege on the parent database.
## Synopsis
**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 identifier rules. When the parent database is not set as the default, the name must be formatted as `database.name`. The `UPSERT` and `INSERT ON CONFLICT` 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 and column family assignments. |
| `family\_def` | An optional column family definition. 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. |
| `select\_stmt` | A selection query to provide the data. |
| `opt\_persistence\_temp\_table` | Defines the table as a session-scoped temporary table. For more information, see 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**. |
| `opt\_with\_storage\_parameter\_list` | A comma-separated list of spatial 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. For an example, see 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, 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 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.
## Examples
#### Setup
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `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';
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
* Simple `SELECT` Clause
* `CREATE TABLE`
* `CREATE VIEW`
* `INSERT`
* `DROP TABLE`
* SQL Statements
* `ALTER PRIMARY KEY`
* `ALTER TABLE`
* Online Schema Changes