This tutorials will explain how to alter tables in SingleStore. This alteration may not be limited to addition of new column, drop existing column or rename column.
ERROR 1890 (HY000): ALTER TABLE which modifies type of column `active_ind1` is not supported on a columnstore table.
ERROR 1706 (HY000): Feature 'CHANGE run with other types of ALTERs' is not supported by SingleStore.
ERROR 1749 (HY000): MemSQL Distributed only supports online ALTER TABLE. The requested ALTER TABLE cannot be performed online because it changes index `PRIMARY`.
ERROR 1749 (HY000): MemSQL Distributed only supports online ALTER TABLE. The requested ALTER TABLE cannot be performed online because it modifies the type of column `dept_no`, which is part of index `dept_no`.
ROWSTORE: ERROR 1749 (HY000): MemSQL Distributed only supports online ALTER TABLE. The requested ALTER TABLE cannot be performed online because it modifies column `dept_no` from NULL to NOT NULL.
COLUMNSTORE: ERROR 1890 (HY000): ALTER TABLE which modifies column `dept_no` from NULL to NOT NULL is not supported on a columnstore table.
ERROR 2420 (HY000): SingleStore does not support specifying the type of column `active_ind5` as SPARSE. Please remove the SPARSE keyword or consider using the COMPRESSION = SPARSE table option.
CREATE ROWSTORE TABLE singlestoreAlterTest_Rowstore
(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
SHARD KEY (emp_no),
KEY (dept_no)
);
CREATE TABLE singlestoreAlterTest_Columnstore
(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
SHARD KEY (emp_no),
KEY (dept_no) USING CLUSTERED COLUMNSTORE
);
ALTER TABLE test_table RENAME TO singlestoreAlterTest_Columnstore;
ALTER TABLE singlestoreAlterTest_Columnstore ADD active_ind CHAR(1);
ALTER TABLE singlestoreAlterTest_Rowstore MODIFY emp_name VARCHAR(100);
ALTER TABLE singlestoreAlterTest_Columnstore CHANGE active_ind delete_ind;
ALTER TABLE singlestoreAlterTest_Columnstore DROP delete_ind;
ALTER TABLE singlestoreAlterTest_Columnstore ADD active_ind1 INTEGER, DROP active_ind;
ALTER TABLE singlestoreAlterTest_Columnstore ADD total_salary as COALESCE(salary,0) + COALESCE(commission,0) PERSISTED INTEGER;
ALTER TABLE singlestoreAlterTest_Columnstore DROP total_salary;
ALTER TABLE singlestoreAlterTest_Columnstore COMPRESSION=SPARSE; --Compresses all columns
ALTER TABLE singlestoreAlterTest_Rowstore ADD active_ind5 INTEGER SPARSE;
SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'singlestoreAlterTest_Rowstore';
ALTER TABLE singlestoreAlterTest_Columnstore COMPRESSION=NONE;
ALTER TABLE singlestoreAlterTest_Rowstore MODIFY active_ind5 INTEGER;