The Ultimate Guide to High Availability with MariaDB

ColumnStore Alter Table

You are viewing an old version of this article. View the current version here.

Contents

  1. ADD
    1. Online alter table add column
  2. CHANGE
  3. DROP
  4. RENAME

The ALTER TABLE statement modifies existing tables. This includes adding, deleting and renaming columns as well as renaming tables.

ADD

The ADD clause allows you to add columns to a table. You must specify the data type after the column name.The following statement adds a priority column with an integer datatype to the orders table:

ALTER TABLE orders ADD COLUMN priority INTEGER;
  • A ColumnStore autoincrement column can be added by including an autoincrement comment at either the table level or the column level. Only one autoincrement can be defined per table.
  • Compression level (0 for no compression, 1 for compression) can be set at the system level. If a session default exists, this will override the system default. In turn, this can be overridden by the table level compression comment, and finally a compression comment at the column level.

Online alter table add column

ColumnStore fully supports online DDL (one session can be adding columns to a table while another session is querying that table). We have also provided the following workaround. This workaround is intended for adding columns to a table, one at a time only. Do not attempt to use it for any other purpose. Follow the example below as closely as possible.

Scenario: add an INT column named col7 to the existing table foo:

select calonlinealter('alter table foo add column col7 int;');
alter table foo add column col7 int comment 'schema sync only';

The select statement may take several tens of seconds to run, depending on how many rows are currently in the table. Regardless, other sessions can select against the table during this time (but they won’t be able to see the new column yet). The alter table statement will take less than 1 second (depending on how busy MariaDB is) and during this brief time interval, other table reads will be held off.

CHANGE

The CHANGE clause allows you to rename a column in a table.

Notes to CHANGE COLUMN:

  • You cannot currently use CHANGE COLUMN to change the definition of thatcolumn.
  • You can only change a single column at a time.The following example renames the order_qty field to quantity in the orders table: 
ALTER TABLE orders CHANGE COLUMN order_qty quantity
INTEGER;

DROP

The DROP clause allows you to drop columns. All associated data is removed when the column is dropped. You can DROP COLUMN (column_name). The following example alters the orders table to drop the priority column:

ALTER TABLE orders DROP COLUMN priority;

RENAME

The RENAME clause allows you to rename a table.The following example renames the orders table:

ALTER TABLE orders RENAME TO customer_orders;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.