CREATE TABLE AS

The CREATE TABLE … AS statement creates a new table from a selection query.

Intended use

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

This can be more efficient than a view when thefollowing 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 largerquery, a view is advisable instead. The query optimizer can "peek"into the view and optimize the surrounding query using the primary keyand indices of the tables mentioned in the view query.

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

Required privileges

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

Synopsis

CREATETABLEIFNOTEXISTStable_name(name,)ASselect_stmt

Parameters

ParameterDescription
IF NOT EXISTSCreate 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_nameThe 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.
nameThe name of the column you want to use instead of the name of the column from select_stmt.
select_stmtA selection query to provide the data.

Limitations

The primary key of tables created with CREATE
TABLE … AS is not derived from the query results. Like for othertables, it is not possible to add or change the primary key aftercreation. Moreover, these tables are notinterleaved with other tables. Thedefault rules for column families apply.

For example:

  1. > CREATE TABLE logoff (
  2.     user_id INT PRIMARY KEY,
  3.     user_email STRING UNIQUE,
  4.     logoff_date DATE NOT NULL,
  5. );
  1. > CREATE TABLE logoff_copy AS TABLE logoff;
  1. > SHOW CREATE logoff_copy;
  1. +-------------+-----------------------------------------------------------------+
  2. |    Table    |                           CreateTable                           |
  3. +-------------+-----------------------------------------------------------------+
  4. | logoff_copy | CREATE TABLE logoff_copy (
  5.                                      |
  6. |             |     user_id INT NULL,
  7.                                           |
  8. |             |     user_email STRING NULL,
  9.                                     |
  10. |             |     logoff_date DATE NULL,
  11.                                      |
  12. |             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)
  13.  |
  14. |             | )                                                               |
  15. +-------------+-----------------------------------------------------------------+
  16. (1 row)

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

It is however possible tocreate a secondary index after CREATE TABLE
… AS.

For example:

  1. > CREATE INDEX logoff_copy_id_idx ON logoff_copy(user_id);
  1. > SHOW CREATE logoff_copy;
  1. +-------------+-----------------------------------------------------------------+
  2. |    Table    |                           CreateTable                           |
  3. +-------------+-----------------------------------------------------------------+
  4. | logoff_copy | CREATE TABLE logoff_copy (
  5.                                      |
  6. |             |     user_id INT NULL,
  7.                                           |
  8. |             |     user_email STRING NULL,
  9.                                     |
  10. |             |     logoff_date DATE NULL,
  11.                                      |
  12. |             |     INDEX logoff_copy_id_idx (user_id ASC),
  13.                     |
  14. |             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)
  15.  |
  16. |             | )                                                               |
  17. +-------------+-----------------------------------------------------------------+
  18. (1 row)

For maximum data storage optimization, consider using separatelyCREATE followed byINSERT INTO … to populate the table using the queryresults.

Examples

Create a table from a SELECT query

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

Change column names

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

  1. > CREATE TABLE customers_ny (id, first_name) AS SELECT id, name FROM customers WHERE state = 'NY';
  1. > SELECT * FROM customers_ny;
  1. +----+------------+
  2. | id | first_name |
  3. +----+------------+
  4. |  6 | Dorotea    |
  5. | 15 | Thales     |
  6. +----+------------+

Create a table from a VALUES clause

  1. > CREATE TABLE tech_states AS VALUES ('CA'), ('NY'), ('WA');
  1. > SELECT * FROM tech_states;
  1. +---------+
  2. | column1 |
  3. +---------+
  4. | CA      |
  5. | NY      |
  6. | WA      |
  7. +---------+
  8. (3 rows)

Create a copy of an existing table

  1. > CREATE TABLE customers_ny_copy AS TABLE customers_ny;
  1. > SELECT * FROM customers_ny_copy;
  1. +----+------------+
  2. | id | first_name |
  3. +----+------------+
  4. |  6 | Dorotea    |
  5. | 15 | Thales     |
  6. +----+------------+

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

See also

Was this page helpful?
YesNo