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
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
Was this helpful?
