Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Syntax

Code Block
languagesql
linenumberstrue
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

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 name of an existing
table, within the same schema. To move a table between schemas, see the instructions below.

...

table

...

Log into each jethro machine which is running your instance, using OS user 'jethro', and stop all the jethro services for that instance:

Code Block
service jethro stop <instance-name> all

Important! - Make sure absolutely no machine is still running this instance's services, before proceeding to step #2.

...

Using a bash script (not JethroClient), run the following command:

Code Block
JethroMoveTable <instance_name> [old_schema_name.]<old_table_name> [new_schema_name.]<new_table_name>

...

.

Examples

Code Block
languagesql
linenumberstrue
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');