3. ALTER TABLE#

3.1. Synopsis#

ALTER TABLE <table_expression> [, ... ] ADD
  <column_name> <data_type> [ ( <column_size> ) ]
  [, ... ]

ALTER TABLE <table_expression> [, ... ] RENAME COLUMN
  <current_column_name> TO <new_column_name>
  [, ... ]

ALTER TABLE <table_expression> [, ... ] DROP COLUMN
  <column_name>
  [, ... ]

ALTER TABLE <table_expression> [, ... ] SET TTL
  [ = ] <duration>

<table_expression> ::= <table_name> | FIND ( ... )

<data_type> ::= BINARY | BLOB
              | FLOAT | DOUBLE
              | BIGINT | INT64
              | STRING
              | TIMESTAMP
              | SYMBOL [ ( <symbol_table_name> ) ]

3.2. Description#

ALTER TABLE modifies one or more tables. Each statement performs one of these operations:

  • Add one or more columns.

  • Rename one or more columns.

  • Drop one or more columns.

  • Set the TTL for a time-series table.

The statement is transactional. If an operation fails for one selected table, QuasarDB does not change any of the selected tables.

3.3. Table selection#

Use a table name, a FIND(...) expression, or a comma-separated mixture of both forms. A FIND(...) expression selects tables by tag. For more information, see key/value lookups.

The statement returns an error if a named table does not exist or if a FIND(...) expression does not select a table.

3.4. Column operations#

ADD adds the specified columns to each selected table. Existing rows have NULL in the new columns. Each new column name must be unique and must not already exist in the table.

RENAME COLUMN renames the specified columns. QuasarDB applies all renames in the statement together. Thus, you can swap column names in one statement. Each source column must exist, and the resulting column names must be unique.

DROP COLUMN removes the specified columns. Each column must exist, and a column can occur only once in the statement. You can drop all regular columns from a table.

These operations do not change the table type. You cannot add, rename, or drop the $timestamp time index. ALTER TABLE does not support changes to a column data type.

General tables support ADD, RENAME COLUMN, and DROP COLUMN in QuasarDB 3.14.3 and later.

3.5. TTL behavior#

SET TTL updates the TTL in the time-series table metadata. It does not rename existing buckets.

Buckets that later writes create use the new TTL. Data that is already written stays in its existing buckets. These buckets keep the TTL that is encoded in their names. Thus, a TTL change does not change the physical retention period of existing data.

Queries use the current TTL in the table metadata. Therefore, a query can stop returning expired data before storage compaction removes the data.

3.6. Limitations#

Aggregated tables do not support ALTER TABLE.

3.7. Parameters#

table_name

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

find_expression

A tag-based expression that selects one or more tables.

column_name, current_column_name, new_column_name

The name of a column to add, rename, or drop. Use double quotation marks around an identifier that contains special characters.

data_type

The column data type. BINARY is an alias for BLOB. FLOAT is an alias for DOUBLE. BIGINT is an alias for INT64.

column_size

An unsigned integer in parentheses after a data type. QuasarDB accepts this compatibility parameter but does not use it. It does not set a storage size or a value-length limit.

symbol_table_name

The symbol table for a SYMBOL column. If you omit the name, QuasarDB creates a symbol table and assigns an internal name.

duration

The new TTL. Use a duration. The = character is optional.

3.8. Examples#

Add a single column:

ALTER TABLE example ADD my_int INT64

Add multiple columns:

ALTER TABLE example ADD
  my_int INT64,
  my_double DOUBLE,
  my_blob BLOB,
  my_ts TIMESTAMP,
  my_symbol SYMBOL(my_symtable)

Add a column to all tables that have the stocks tag:

ALTER TABLE FIND(TAG = 'stocks') ADD description STRING

Add a column to named and tagged tables in one transaction:

ALTER TABLE example, FIND(TAG = 'stocks') ADD source STRING

Rename a single column:

ALTER TABLE example RENAME COLUMN my_int TO my_new_int

Rename multiple columns:

ALTER TABLE example RENAME COLUMN
  my_int TO my_new_int,
  my_double TO my_new_double

Swap two column names:

ALTER TABLE example RENAME COLUMN
  column_a TO column_b,
  column_b TO column_a

Drop a single column:

ALTER TABLE example DROP COLUMN my_column

Drop multiple columns:

ALTER TABLE example DROP COLUMN column_a, column_b, column_c

Set the TTL for buckets that later writes create:

ALTER TABLE example SET TTL = 30day