ALTER TABLE

Owned by Boaz Raufman
Apr 02, 2023
2 min read

Modify the names, columns and constraints of an existing table.

Syntax

ALTER TABLE [schema_name.]table_name
{
	RENAME TO [schema_name.]table_name | 
    RENAME COLUMN old_name to new_name |
	ADD COLUMN column_name data_type | 
	DROP COLUMN column_name | 
	ADD PRIMARY KEY (column_name) | 
	DROP PRIMARY KEY (column_name) |
	DROP PARTITION FOR 
	{ 
		(value[,...n]) | 
		(BETWEEN value AND value)
	}
}

Description

ParameterDetails
ADD / DROP COLUMNAdds or drops a column. When adding a column, all existing rows will have NULL value for the new column.
ADD / DROP PRIMARY KEYAdds or drops a primary key column. A table can have one primary key column. Primary keys must contain unique values and cannot contain NULL values.
DROP PARTITION

To drop partitions, specify one or more column values for the partition key, or a range of values by using the BETWEEN clause.

The partitions where the value exists, or that are within the given range, will be dropped.

RENAMELets you change the table name or move the table to a different schema.
RENAME COLUMNLets you change a column's name. Applicable only to non-external tables. Please note that views must be updated manually to use the new column name instead of the old one. Join indexes and Cubes would still work as before, but the SHOW CUBES command may still present the old column name. (Supported from Jethro v3.4.4 and up)

Examples

ALTER TABLE store_sales RENAME TO store_profits;
ALTER TABLE store_sales RENAME COLUMN ss_sales_price TO ss_price;
ALTER TABLE store_sales ADD PRIMARY KEY (ss_ticket_number);
ALTER TABLE store_sales DROP COLUMN ss_wholesale_cost;
ALTER TABLE web_events DROP PARTITION FOR ('2014-05-27'); 								// Drops the partition which includes the value '2014-05-27'
ALTER TABLE web_events DROP PARTITION FOR ('2014-05-01','2014-06-01'); 					// Drops all the partitions which includes the specific values in the list
ALTER TABLE web_events DROP PARTITION FOR (BETWEEN '2014-01-01' AND '2014-03-01');		// Drops all the partitions which includes the range of values in the list