6. CREATE TABLE AS SELECT#

6.1. Synopsis#

CREATE TABLE [ IF NOT EXISTS ] <table_name>
[ SHARD_SIZE [ = ] { <duration> | INFINITE } ]
[ WITH TAGS = '[' [ '<tag>' [, ... ] ] ']' ]
[ TTL [ = ] <duration> ]
AS <select_statement>

Specify the options in the order that the synopsis shows.

6.2. Description#

CREATE TABLE AS SELECT creates a table from the result schema of a SELECT statement. This statement is also known as CTAS. It is available in QuasarDB 3.14.2 and later.

CTAS creates only the table schema. It does not copy the selected rows. To copy the rows, run INSERT INTO … SELECT after CTAS. A SELECT statement that returns no data rows can still provide a valid schema.

CTAS creates a time-series table. The SELECT result must contain one $timestamp column of type TIMESTAMP and at least one regular column. Use an alias in the SELECT statement to set a different name for a result column. Do not specify a column list before AS SELECT.

QuasarDB does not add the virtual $table result column to the new schema. CTAS converts a selected SYMBOL column to STRING. It does not copy the source symbol-table definition.

If the table exists, CTAS returns an error. If you specify IF NOT EXISTS, the statement succeeds and does not change the existing table.

6.3. Parameters#

table_name

The name of the table to create. Use double quotation marks around an identifier that contains special characters.

SHARD_SIZE

The time span of one shard. Specify a duration or INFINITE. The default value is one day. The = character is optional.

WITH TAGS

A comma-separated list of tags to attach to the new table. Put each tag in single quotation marks. The = character is required.

TTL

The time-to-live value for the table data. Specify a duration. The = character is optional. TTL removes expired data during storage compaction. For more information, see the TTL section of CREATE TABLE.

select_statement

A SELECT statement that provides the new schema. Its result must contain $timestamp TIMESTAMP and at least one regular column.

6.4. Examples#

Create a time-series schema from all source columns:

CREATE TABLE target AS SELECT * FROM source

Create a schema from selected columns and rename one column:

CREATE TABLE target
  AS SELECT $timestamp, value, status AS current_status FROM source

Create a schema, set the table options, and attach tags:

CREATE TABLE target
  SHARD_SIZE = 1hour
  WITH TAGS = ['derived', 'hourly']
  TTL = 30day
  AS SELECT $timestamp, value FROM source

Create a schema and then copy the selected rows:

CREATE TABLE target
  AS SELECT $timestamp, value, status FROM source

INSERT INTO target
  SELECT $timestamp, value, status FROM source