ALTER TABLE

Function

ALTER TABLE modifies a table. You can use this syntax to modify table definitions, rename tables, rename a specified column in a table, add or update multiple columns, and enable or disable row-level access control.

Precautions

• You must own the time series table to use ALTER TABLE. A system administrator has this permission by default.
• The tablespace of the partitioned table cannot be modified. However, the tablespace of the partition can be modified.
• The storage parameter ORIENTATION cannot be modified.
• Currently, SET SCHEMA can only be used to set a schema to a user schema, not to a system internal schema.
• When you modify the enable_delta parameter of a time series table, other ALTER operations cannot be performed.
• orientation of storage_parameter and sub_partition_count cannot be modified.
• The column to be added must have the kv_type attribute, and the attribute must be set to tstag or tsfiled.
• The column to be deleted cannot be of the tstime type that indicates a partition column.
• If the delta table function is enabled, a delta table and an automatic writeback task will be created. If the delta table function is disabled, the delta table data will be forcibly written to CU.

Syntax

1
2

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
action [, ... ];

There are several clauses of action:

• ADD COLUMN is used to add a column to a time series table.

  1	ADD COLUMN column_name data_type [ kv_type ] [ compress_mode ]

A time series table can contain only one TSTIME column. If you attempt to create another TSTIME column, an error will be reported.

• DROP_COLUMN is used to delete columns from a time series table.

  1	|DROP COLUMN [ IF EXISTS ] column_name [RESTRICT | CASCADE ]

If an index column is deleted using DROP COLUMN, the remaining index columns will be used to rebuild the index. If all the index columns are deleted, the first 10 tag columns will be used to rebuild the index.

• Modifying the storage parameters of a time series table

  1	|SET ( { storage_parameter = value } [, ...] )

• Renaming the specified column in a table

  1	RENAME [ COLUMN ] column_name to new_column_name;

• Changing the owner of a time series table:

  1	OWNER TO new_owner

• (Not recommended) Expanding a time series table:

  1	ADD NODE ( nodename [, ...] )

• Adding a partition to a time series table:

  1	ADD PARTITION part_new_name partition_less_than_item

• Removing a specified partition from a partitioned table:

  1	DROP PARTITION { partition_name }

• Deleting the specified partition of a time series table:

  1	TRUNCATE PARTITION { partition_name }

Description

• table_name

  Specifies the name of a partitioned table.

  Value range: an existing partitioned table name

• partition_name

  Partition name

  Value range: an existing partition name

• partition_new_name

  Specifies the new name of a partition.

  Value range: a string compliant with the naming convention

Examples

Create a simple time series table.

1
2
3
4
5
6
7

CREATE TABLE CPU(
idle numeric TSField,
IO numeric TSField,
scope text TSTag,
IP text TSTag,
time timestamp TSTime
) with (TTL='7 days', PERIOD = '1 day', orientation=TIMESERIES);

Add a column to the time series table.

1

ALTER TABLE CPU ADD COLUMN memory numeric TSField;

Delete a column from the time series table.

1

ALTER TABLE CPU DROP COLUMN idle;

Modify the column name of the time series table.

1

ALTER TABLE CPU RENAME scope to scope1; 

Set the TTL of the partitions in a time series table to seven days.

1

ALTER TABLE CPU SET (TTL = '7 day');

Set Period to 1 day.

1

ALTER TABLE CPU SET (PERIOD = '1 day');

1

ALTER TABLE CPU SET (enable_delta = false);