CREATE TABLE AS

On this page Carat arrow pointing down
Warning:
CockroachDB v1.0 is no longer supported. For more details, see the Release Support Policy.

The CREATE TABLE ... AS statement creates a new table from the results of a query.

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.

Required Privileges

The user must have the CREATE privilege on the parent database.

Synopsis

CREATE TABLE IF NOT EXISTS any_name ( name , ) AS select_stmt

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.
any_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.
name The name of the column you want to use instead of the name of the column from select_stmt.
select_stmt The query whose results you want to use to create the table. This can use SELECT, TABLE or VALUES.

Limitations

The primary key of tables created with CREATE TABLE ... AS is not derived from the query results. Like for other tables, it is not possible to add or change the primary key after creation. Moreover, these tables are not interleaved with other tables. The default rules for column families apply.

For example:

> CREATE TABLE logoff (
    user_id INT PRIMARY KEY,
    user_email STRING UNIQUE,
    logoff_date DATE NOT NULL,
);
> CREATE TABLE logoff_copy AS TABLE logoff;
> SHOW CREATE TABLE logoff_copy;
+-------------+-----------------------------------------------------------------+
|    Table    |                           CreateTable                           |
+-------------+-----------------------------------------------------------------+
| logoff_copy | CREATE TABLE logoff_copy (                                      |
|             |     user_id INT NULL,                                           |
|             |     user_email STRING NULL,                                     |
|             |     logoff_date DATE NULL,                                      |
|             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)  |
|             | )                                                               |
+-------------+-----------------------------------------------------------------+
(1 row)

The example illustrates that the primary key, unique and "not null" constraints are not propagated to the copy.

It is however possible to create a secondary index after CREATE TABLE ... AS.

For example:

> CREATE INDEX logoff_copy_id_idx ON logoff_copy(user_id);
> SHOW CREATE TABLE logoff_copy;
+-------------+-----------------------------------------------------------------+
|    Table    |                           CreateTable                           |
+-------------+-----------------------------------------------------------------+
| logoff_copy | CREATE TABLE logoff_copy (                                      |
|             |     user_id INT NULL,                                           |
|             |     user_email STRING NULL,                                     |
|             |     logoff_date DATE NULL,                                      |
|             |     INDEX logoff_copy_id_idx (user_id ASC),                     |
|             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)  |
|             | )                                                               |
+-------------+-----------------------------------------------------------------+
(1 row)

For maximum data storage optimization, consider using separately CREATE followed by INSERT INTO ... to populate the table using the query results.

Examples

Create a Table from a SELECT Query

> SELECT * FROM customers WHERE state = 'NY';
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+
> CREATE TABLE customers_ny AS SELECT * FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+

Change Column Names

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

> CREATE TABLE customers_ny (id, first_name) AS SELECT id, name FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;
+----+------------+
| id | first_name |
+----+------------+
|  6 | Dorotea    |
| 15 | Thales     |
+----+------------+

Create a Table from a VALUES Clause

> CREATE TABLE tech_states AS VALUES ('CA'), ('NY'), ('WA');

> SELECT * FROM tech_states;
+---------+
| column1 |
+---------+
| CA      |
| NY      |
| WA      |
+---------+
(3 rows)

Create a Copy of an Existing Table

> CREATE TABLE customers_ny_copy AS TABLE customers_ny;

> SELECT * FROM customers_ny_copy;
+----+------------+
| id | first_name |
+----+------------+
|  6 | Dorotea    |
| 15 | Thales     |
+----+------------+

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.

See Also


Yes No
On this page

Yes No