ALTER TABLE

Syntax

SQL

ALTER [ONLINE] TABLE <table_name>
  [<alter_specification> [, <alter_specification>] ...] [TIMEOUT <timeout_value>]

<alter_specification>:
    ADD [COLUMN] col_name { column_definition | AS computed_column_definition } [FIRST | AFTER <column_name>]
  | ADD [COLUMN] (<column_name> <column_definition>, ...)
  
  | ADD [UNIQUE] { INDEX | KEY } [<index_name>] [<index_type>] (<index_column_name>, ...) [<index_option>] ...
  
  
  | DROP [COLUMN] <column_name>
  | DROP { INDEX | KEY } <index_name> 
  
  | MODIFY [COLUMN] <column_name> <column_definition>
        [FIRST | AFTER <column_name>]
  | MODIFY COLUMN <column_name> <column_definition> COLLATE <collation_name>
  | CHANGE <old_column_name> <new_column_name>
  | RENAME [TO | AS] <new_table_name>
  | MODIFY { INDEX | KEY } <index_name> SET ([<index_kv_option>], ...)
  | AUTOSTATS_ENABLED = {ON|OFF}
  | AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}
  | AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}
  | AUTOSTATS_SAMPLING = {ON|OFF}
  | AUTO_INCREMENT [=] <new_value>
  | COMPRESSION [=] { NONE | SPARSE }
  

<column_definition>:
    <data_type> [NOT NULL | NULL] [DEFAULT <default_value>]
      [COMMENT '<string>'] [SERIES TIMESTAMP] [SPARSE]

<computed_column_definition>:
    <computed_column_expression> PERSISTED <data_type>

<index_column_name>:
    <column_name> [(<length>)] [ASC | DESC]

<index_type>:
    USING { BTREE | HASH }

<index_option>:
    KEY_BLOCK_SIZE [=] <value>
  | BUCKET_COUNT [=] <value>
  | <index_type>
  | WITH ([<index_kv_option>], ...)
  | UNENFORCED [RELY | NORELY]

<index_kv_option>:
    RESOLUTION = <value>
  | COLUMNSTORE_SEGMENT_ROWS = <value>
  | COLUMNSTORE_FLUSH_BYTES = <value>

Remarks

SingleStore supports online ALTER TABLE, which means that you can read and write while the table is being altered. ALTER TABLE on a sharded table is always executed online. Note that online ALTER TABLE will not begin modifying the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before modifying the structure of the table, and ensures consistency of results from queries on the table since the time of execution of ALTER TABLE. As soon as the in-progress reads and writes complete and the ALTER TABLE command begins modifying the table, new reads and writes will proceed as normal. This blocking period usually lasts approximately for milliseconds.

If you are running frequent ALTER TABLE statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for completion of long-running queries.

You can monitor the progress of the ALTER TABLE statement by running the following statement:
SQL
```
SELECT * FROM INFORMATION_SCHEMA.MV_PROCESSLIST 
WHERE INFO LIKE '%ALTER%' ORDER BY NODE_ID;
```
This query contains information about currently running ALTER commands for all nodes on a cluster. Since DDL commands like ALTER run on the master aggregator only, the order by NODE_ID clause ensures the master aggregator is the top result. Note the STATE column as it displays the state of the process.

A common state for DDL commands like ALTER is: waiting on the leaves. This is a normal state in most cases since altering the table happens on the leaves. If the execution of the DDL command is waiting on a metadata lock on the leaves and an error message is generated, there could be a long-running query preventing the lock from being taken. A metadata lock is taken when DDL commands are executed to ensure a consistent state on all nodes.

Refer to the Query Errors topic for resolving query timeout errors due to long running queries in a workload.
Online ALTER TABLE in SingleStore cannot be rolled back, so operations that may result in errors are disallowed. For example, you cannot add indexes with online ALTER TABLE because there may be duplicate keys in the indexed column
Once the ALTER TABLE command is invoked, it cannot be stopped or killed if it is in the commit phase and must be completed. Killing the query will have no effect; the query will continue to perform its work in the background even if the cluster is restarted. You must wait and allow the ALTER to complete. If the ALTER is killed during the commit phase, restart the cluster and execute OPTIMIZE TABLE <table_name> FIX_ALTER to complete the failed ALTER.
ALTER OFFLINE TABLE is supported on REFERENCE tables only. ALTER OFFLINE TABLE operations will cause write queries against the table to fail until the ALTER TABLE completes. Read queries can still be executed against the table as if it were in the state immediately before the ALTER TABLE operation began executing. If the user does not specify whether to do the ALTER TABLE operation online or offline, SingleStore will do it online if possible. Note that offline ALTER TABLE completes more quickly than online, but offline blocks writes for as long as the command is executing.
Use ALTER TABLE CHANGE to rename a column.
Use ALTER TABLE RENAME TO or ALTER TABLE RENAME AS to rename the table. TO or AS can be used interchangeably as they are synonyms. If a read on a table is occurring during the alter operation, the read will continue.
This command can be run on the master aggregator node, or a child aggregator node (see Cluster Management Commands ).
Columns used to construct a view cannot be dropped. See CREATE VIEW for more information.
<computed_column_expression> defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see Using Persistent Computed Columns.
On columnstore tables, adding columns, dropping columns, and changing column names are supported, but using ALTER TABLE MODIFY to change the data type for a column is not supported. To modify column data types in Columnstore tables, see the examples below.

On rowstore tables, adding columns, dropping columns, and changing column names are supported. Using ALTER TABLE MODIFY to change the data type from a string to an integer will default any values in those columns to 0.
Only hash indexes can be added or dropped on columnstore tables. These hash indexes are non-unique and single-column, only.
ALTER TABLE <table_name> MODIFY KEY SET (...) modifies configuration variables associated with the index. It is currently only supported on columnstore indexes. See Configuring the Columnstore to Work Effectively for more information.
ALTER TABLE <table_name> ADD KEY <field_name> modifies the table by assigning the key designation to a chosen field or multiple fields. ADD KEY is the same as CREATE INDEX.
ALTER TABLE <table_name> ADD KEY <field_name> modifies the table by assigning the key designation to a chosen field. ADD KEY is the same as CREATE INDEX.
ALTER TABLE <table_name> { ENABLE | DISABLE } AUTOSTATS enables or disables automatic statistics collection on the table. There are three categories of autostats - AUTOSTATS_CARDINALITY_MODE, AUTOSTATS_HISTOGRAM_MODE, and AUTOSTATS_SAMPLING. SingleStore lets you independently control how each category of statistics is automatically gathered. Multiple autostats settings can be combined in a single ALTER TABLE statement. See Automatic Statistics for more information.
ALTER TABLE <table_name> AUTO_INCREMENT [=] <new_value> sets the next auto_increment value that will be generated. It can only be used with reference tables and to increase the internal auto_increment counter. If <new_value> is smaller than the current value, a warning is shown, displaying the current value.
Altering PRIMARY or SHARD keys is unsupported. Altering UNIQUE keys is only supported on rowstore reference tables: CREATE ROWSTORE REFERENCE TABLE table_name
This command causes implicit commits. Refer to COMMIT for more information.
TIMEOUT, a value specified in milliseconds, determines the length of time to wait for the ALTER TABLE command to commit across the cluster. If not specified, the default_distributed_ddl_timeout global variable value is used.
The SERIES TIMESTAMP clause specifies a table column as the default timestamp column. This setting can be specified only for a single table column. The column can be either of the following data types: DATE, TIME, DATETIME, or TIMESTAMP. SingleStore recommends to use either DATETIME or DATETIME(6) data type instead of TIMESTAMP because the automatic update behavior of TIMESTAMP is subject to change. See Timestamp Behavior for details.
The SERIES TIMESTAMP clause does not affect the data type of a table column, rather it specifies the behavior of the column in the time series specific functions like FIRST(), LAST(), and TIME_BUCKET.
For an example of how to modify a rowstore table’s columns to use sparse compression, see Example 3. Also, see COMPRESSION = SPARSE and SPARSE behavior for a general discussion on sparse rowstore compression.
Adding an index on a table having the COMPRESSION = SPARSE table option will store all key columns in their non-sparse format
Adding an index on a column having the SPARSE column option is not allowed.
Dropping an index on a table having the COMPRESSION = SPARSE option will, for each column, store the column as sparse if possible.
If a table was created with the SPARSE option on individual columns, you will not be able to modify the COMPRESSION = SPARSE table option, and vice-versa. For example, if a table is created using the statement CREATE TABLE <table_name> (<column_1> INT, <column_2> VARCHAR(50) SPARSE, <column_3> DATETIME);, you would not be able to run ALTER TABLE <table_name> COMPRESSION = SPARSE; successfully.
In rowstores, you can use the ALTER TABLE statement to modify the collation of existing columns.
Refer to the Permission Matrix for the required permission.

Note

Successful completion of a new ALTER TABLE will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation.

Examples

ALTER TABLE to Add Columns

The following example creates a table with three columns, then alters the table to add a fourth column called middle_initial.

SQL

CREATE ROWSTORE TABLE customer(
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    birth_date DATE);

SQL

INSERT INTO customer VALUES
  ("Joe", "Williams", "1960/1/2"),
  ("Esther", "Johnson", "1962/7/19");

SQL

SELECT * FROM customer;

+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Esther     | Johnson   | 1962-07-19 |
| Joe        | Williams  | 1960-01-02 |
+------------+-----------+------------+

SQL

ALTER TABLE customer ADD column middle_initial CHAR(1);

See the result:

SQL

SELECT * FROM customer;

+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | NULL           |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+

SQL

UPDATE customer SET middle_initial = "T" WHERE first_name = "Esther";

See the result:

SQL

SELECT * FROM customer;

+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | T              |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+

ALTER TABLE to Rename Columns

The following example uses the customer table from Example 1. This example changes (renames) the middle_initial column to a middle_name column.

SQL

ALTER TABLE customer CHANGE middle_initial middle_name;
ALTER TABLE customer MODIFY middle_name VARCHAR(30);

See the result:

SQL

SELECT * FROM customer;

+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | NULL        |
+------------+-----------+------------+-------------+

SQL

UPDATE customer SET middle_name = "Wallace" WHERE last_name = "Williams";

See the result:

SQL

SELECT * FROM customer;

+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | Wallace     |
+------------+-----------+------------+-------------+

ALTER TABLE to Modify Individual SPARSE Compression Columns

The following example demonstrates how to modify existing columns in a table to use or not to use sparse compression.

Create a table with three columns. Column b is a sparse column.

SQL

CREATE ROWSTORE TABLE sparse_demo (a INT, b VARCHAR(50) SPARSE, c DATETIME);

Modify column a to use sparse compression. Modify column b to use non-sparse compression.

SQL

ALTER TABLE sparse_demo MODIFY COLUMN a INT SPARSE;
ALTER TABLE sparse_demo MODIFY COLUMN b VARCHAR(50);

See the results:

SQL

SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'sparse_demo';

+-------------+-----------+
| column_name | is_sparse |
+-------------+-----------+
| a           | YES       |
| b           | NO        |
| c           | NO        |
+-------------+-----------+

ALTER TABLE to Modify All Columns to Use SPARSE Compression

The following example demonstrates how to modify all of the columns in the table to use sparse compression.

Create a table with three non-sparse columns.

SQL

CREATE ROWSTORE TABLE sparse_demo2 (a INT, b VARCHAR(50), c DATETIME);

SQL

ALTER TABLE sparse_demo2 COMPRESSION = SPARSE;

See the results:

SQL

SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'sparse_demo2';

+-------------+-----------+
| column_name | is_sparse |
+-------------+-----------+
| a           | YES       |
| b           | YES       |
| c           | YES       |
+-------------+-----------+

ALTER TABLE to Modify Column Data Types, Sizes, and Collation

Note

On rowstore tables, adding columns, dropping columns, and changing column names are supported using the MODIFY COLUMN argument. Using ALTER TABLE MODIFY COLUMN to change the data type from a string to integer will default any values in those columns to 0.

The following example demonstrates how to modify the data type of a column in a rowstore table. In this case, we are changing col1 to a FLOAT data type.

SQL

ALTER TABLE alter_rowstore_table MODIFY COLUMN col1 FLOAT;

The following example demonstrates how to modify the data type of a column in a columnstore table.

SQL

CREATE TABLE towns (
    id INT UNSIGNED NOT NULL PRIMARY KEY, 
    town_name VARCHAR(64) COLLATE utf8_general_ci NOT NULL, 
    pop INT NOT NULL);

SQL

INSERT INTO towns 
VALUES 
  (1, 'Hamilton Bay', 12346),
  (2, 'Probability Bay', 263951), 
  (3, 'Decentralized Park', 29265), 
  (4, 'Axiom Township', 845696), 
  (5, 'Elegant Island', 987654);

SQL

DESC towns;

+-----------+------------------+------+-----+---------+
| Field     | Type             | Null | Key | Default |
+-----------+------------------+------+-----+---------+
| id        | int(10) unsigned | NO   | PRI | NULL    |
| town_name | varchar(64)      | NO   |     | NULL    |
| pop       | int(11)          | YES  |     | NULL    |
+-----------+------------------+------+-----+---------+

First, create the new column, pop2 with the desired data type:

SQL

ALTER TABLE towns ADD COLUMN pop2 DECIMAL(20);

Then, copy the data into the new column (pop2), rename the original column to a different name (pop_to_delete) to retain the data in the original column, rename the new column to the original column name, confirm the data type for the new column is correct, and drop the original column with the old data type:

SQL

-- Copy data from the original column to the new column
UPDATE towns SET pop2 = pop;

-- Rename the old column into a different name to retain data
ALTER TABLE towns CHANGE pop pop_to_delete;

-- Rename the new column to the original column name
ALTER TABLE towns CHANGE pop2 pop;

-- Confirm the data type is correct
DESC towns;

+---------------+------------------+------+-----+---------+
| Field         | Type             | Null | Key | Default |
+---------------+------------------+------+-----+---------+
| id            | int(10) unsigned | NO   | PRI | NULL    |
| town_name     | varchar(64)      | NO   |     | NULL    |
| pop_to_delete | int(10) unsigned | NO   |     | NULL    |
| pop           | decimal(20,0)    | YES  |     | NULL    |
+---------------+------------------+------+-----+---------+

-- Drop the column created to retain the data
ALTER TABLE towns DROP COLUMN pop_to_delete;

If the new data type is not correct after checking, revert the columns as follows and repeat the process from the previous code block.

SQL

ALTER TABLE towns CHANGE pop pop2;
ALTER TABLE towns CHANGE pop_to_delete pop;

ALTER TABLE to Add Keys

The following examples demonstrate how a table will look before and after ADD KEY with the ALTER TABLE command is used.

SQL

CREATE TABLE employees(
    emp_id INT(7),
    emp_lastname VARCHAR(25),
    emp_firstname VARCHAR(25),
    emp_dept VARCHAR(25));

SQL

INSERT employees
VALUES    
    (114, "Williams", "Trent", "sales"),
    ("010", "Stevens", "Carol", "R&D"),
    ("208", "Lowell", "Martin", "maint"),
    ("160", "Anderson", "Jane", "sales");

SQL

DESC employees;

+----------------+------------------+------+-----+---------+
| Field          | Type             | Null | Key | Default |
+----------------+------------------+------+-----+---------+
| emp_id         | int(7)           | YES  |     | NULL    |
| emp_lastname   | varchar(25)      | YES  |     | NULL    |
| emp_firstname  | varchar(25)      | YES  |     | NULL    |
| emp_dept       | varchar(25)      | YES  |     | NULL    |
+----------------+------------------+------+-----+---------+

Below is the result if ADD KEY had been applied to a single field in the table.

SQL

ALTER TABLE employees ADD KEY (emp_id);

See the result:

SQL

SHOW CREATE TABLE employees;

+-----------+----------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                               |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees`                                                                                                   |
|           | (`emp_id` int(7) DEFAULT NULL,  `                                                                                          |
|           |  emp_lastname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                        | 
|           | `emp_firstname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                       |
|           | `emp_dept` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                            |
|           |  SORT KEY `__UNORDERED` (),                                                                                                |
|           |  KEY `emp_id` (`emp_id`) USING HASH,                                                                                  |
|           |  SHARD KEY () )                                                                                                            |
|           |  AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
+-----------+----------------------------------------------------------------------------------------------------------------------------+

Below is the result if ADD KEY had been applied to more than one field in the table.

SQL

ALTER TABLE employees ADD KEY id_lastname (emp_id, emp_lastname);

See the result:

SQL

SHOW CREATE TABLE employees;

+-----------+----------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                               |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees`                                                                                                   |
|           | (`emp_id` int(7) DEFAULT NULL,  `                                                                                          |
|           |  emp_lastname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                        | 
|           | `emp_firstname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                       |
|           | `emp_dept` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,                                            |
|           |  SORT KEY `__UNORDERED` (),                                                                                                |
|           |  KEY `emp_id` (`emp_id`,`emp_lastname`) USING HASH                                                                         |
|           |  SHARD KEY () )                                                                                                            |
|           |  AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
+-----------+----------------------------------------------------------------------------------------------------------------------------+

ALTER TABLE to Change Column Positions

This example shows how to move a column and add a column at a specific position in a table.

SQL

CREATE TABLE at_chgord (a int, b int, c int);

/* add a column at the beginning */

ALTER TABLE at_chgord ADD COLUMN id bigint FIRST;

/* change the type of b to bigint but leave it in the same position */

ALTER TABLE at_chgord ADD COLUMN b_big bigint;

UPDATE at_chgord SET b_big = b;   /* for a huge table, consider doing this in batches */

Now the table looks like this:

SQL

DESC at_chgord;

+-------+------------+------+------+---------+-------+
| Field | Type       | Null | Key  | Default | Extra |
+-------+------------+------+------+---------+-------+
| id    | bigint(20) | YES  |      | NULL    |       |
| a     | int(11)    | YES  |      | NULL    |       |
| b     | int(11)    | YES  |      | NULL    |       |
| c     | int(11)    | YES  |      | NULL    |       |
| b_big | bigint(20) | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

Next, drop column b:

SQL

ALTER TABLE at_chgord DROP COLUMN b;

Now move b_big back to the original position of b using AFTER:

SQL

ALTER TABLE at_chgord MODIFY b_big bigint AFTER a;

Then rename it:

SQL

ALTER TABLE at_chgord CHANGE b_big b;

And here's the final result:

SQL

DESC at_chgord;

+-------+------------+------+------+---------+-------+
| Field | Type       | Null | Key  | Default | Extra |
+-------+------------+------+------+---------+-------+
| id    | bigint(20) | YES  |      | NULL    |       |
| a     | int(11)    | YES  |      | NULL    |       |
| b     | bigint(20) | YES  |      | NULL    |       |
| c     | int(11)    | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

ALTER TABLE

On this page

Syntax

Remarks

Examples

ALTER TABLE to Add Columns

ALTER TABLE to Rename Columns

ALTER TABLE to Modify Individual SPARSE Compression Columns

ALTER TABLE to Modify All Columns to Use SPARSE Compression

ALTER TABLE to Modify Column Data Types, Sizes, and Collation

ALTER TABLE to Add Keys

ALTER TABLE to Change Column Positions

Was this article helpful?

On this page

Was this article helpful?