SingleStore : Alter tables

SingleStore : Alter tables

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.

• Rename the table
• Add / Modify / Rename / Drop Column
• Add / Remove Computed Column
• Add Column Compression
• Remove Column Compression

• Datatype modification is not supported on ColumnStore tables. Users will get below error if they try to modify datatype of columnstore table.

  
  ERROR 1890 (HY000): ALTER TABLE which modifies type of column `active_ind1` is not supported on a columnstore table.
  

• Other column alter commands like ADD, MODIFY cannot be used along CHANGE alter command. Users will get below error if they try to do that.

  
  ERROR 1706 (HY000): Feature 'CHANGE run with other types of ALTERs' is not supported by SingleStore.
  

• Primary key change/drop is not supported in SingleStore.

  
  ERROR 1749 (HY000): MemSQL Distributed only supports online ALTER TABLE. The requested ALTER TABLE cannot be performed online because it changes index `PRIMARY`.
  

• SHRED Key change/drop is not supported in SingleStore.

  
  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 and Columnstore tables does not support changing column from NULL to NOT NULL.

  
  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.
  

• SingleStore does not support adding SPARSE compression to columnStore tables.

  
  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
);

➩ Rename the table
• Renaming the existing table

  
  ALTER TABLE test_table RENAME TO singlestoreAlterTest_Columnstore;
  


➩ Add / Modify / Rename / Drop Column
• Adding new column to the existing table

  
  ALTER TABLE singlestoreAlterTest_Columnstore ADD active_ind CHAR(1);
  

  
  
• Increase length of existing column in the table i.e modify column datatype from varchar(50) to varchar(100). This is only supported for RowStore tables, column store table does not allow this.

  
  ALTER TABLE singlestoreAlterTest_Rowstore MODIFY emp_name VARCHAR(100);
  

  
  
• Renaming existing column in the table, active_ind to delete_ind

  
  ALTER TABLE singlestoreAlterTest_Columnstore CHANGE active_ind delete_ind; 
  

  
  
• Dropping existing column in the table

  
  ALTER TABLE singlestoreAlterTest_Columnstore DROP delete_ind;
  

  
  
• Adding new column and dropping existing column in same ALTER statement.

  
  ALTER TABLE singlestoreAlterTest_Columnstore  ADD active_ind1 INTEGER, DROP active_ind;
  


➩ Add / Remove Computed Column
• Adding computed column in the table

  
  ALTER TABLE singlestoreAlterTest_Columnstore ADD total_salary as COALESCE(salary,0) + COALESCE(commission,0) PERSISTED INTEGER;
  

  
  
• Remove column title of the existing column in the table

  
  ALTER TABLE singlestoreAlterTest_Columnstore DROP total_salary;
  


➩ Add / Remove Compression
• Adding commpression to the existing table

  
  ALTER TABLE singlestoreAlterTest_Columnstore COMPRESSION=SPARSE;  --Compresses all columns
  

  
  
• Adding commpression to the existing column of the table, only rowstore table supports column level compression.

  
  ALTER TABLE singlestoreAlterTest_Rowstore  ADD active_ind5 INTEGER  SPARSE;
  
  
  SELECT column_name, is_sparse FROM information_schema.columns
  WHERE table_name = 'singlestoreAlterTest_Rowstore';
  

  
  
• Remove commpression from an existing table.

  
  ALTER TABLE singlestoreAlterTest_Columnstore COMPRESSION=NONE;
  

  
  
• Remove commpression from an existing column of the table.

  
  ALTER TABLE singlestoreAlterTest_Rowstore MODIFY active_ind5 INTEGER;