Modify the names, columns and constraints of an existing table.
Syntax
ALTER TABLE [schema_name.]table_name
{
RENAME TO [schema_name.]table_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
| Parameter | Details |
|---|---|
| ADD / DROP COLUMN | Adds or drops a column. When adding a column, all existing rows will have NULL value for the new column. |
| ADD / DROP PRIMARY KEY | Adds 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. |
| RENAME | Lets you change the name of an existing table, within the same schema. To move a table between schemas, see the instructions below. |
Moving a table between schemas
Log into each jethro machine which is running your instance, using OS user 'jethro', and stop all the jethro services for that instance:
service jethro stop <instance-name> all
Important! - Make sure absolutely no machine is still running this instance's services, before proceeding to step #2.
- Make sure that the target schema doesn't use the same name for join-indexs, as of the join indexes from the old schema, which are relevant to the table designated to be moved. All the Join Indexes of that table will move to the new schema with the table. If the destination schema holds a join index with the same name, the operation will fail.
Using a bash script (not JethroClient), run the following command:
JethroMoveTable <instance_name> [old_schema_name.]<old_table_name> [new_schema_name.]<new_table_name>
If no schema will be specified for one of the table names, the utility will use def_schema as default.
Examples
ALTER TABLE store_sales RENAME TO store_profits;
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');
ALTER TABLE web_events DROP PARTITION FOR (BETWEEN '2014-01-01' AND '2014-03-01');