You can use the ALTER TABLE statement with time series tables. For complete information about ALTER TABLE syntax, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
You cannot use the following ALTER TABLE options on time series tables:
- ALTER TABLE MODIFY PRIMARY
- ALTER TABLE FROM TIME ZONE
- ALTER TABLE SET or RESET DOWN
- ALTER TABLE TO CURRENT
However, you can use an ALTER TABLE MODIFY PRIMARY statement to add a name or drop an existing name for PRIMARY TIME INDEX.
System Generated Time Series Columns and ALTER TABLE
You cannot specify the system generated time series columns, TD_TIMEBUCKET, TD_TIMECODE, or TD_SEQNO in an ALTER TABLE statement, including:
- ALTER TABLE table_name ADD column_name
- ALTER TABLE table_name DROP column_name
- ALTER TABLE ADD FOREIGN KEY (column_name)
- ALTER TABLE ADD UNIQUE column_name
- ALTER TABLE ADD PRIMARY KEY column_name
- ALTER TABLE RENAME column_name
You cannot add the following column attributes to the system generated time series columns, TD_TIMEBUCKET, TD_TIMECODE, or TD_SEQNO:
- DEFAULT, DEFAULT NULL, DEFAULT DATE, DEFAULT TIME, or DEFAULT USER
- NAMED
- NULL or NOT NULL
- CASESPECIFIC or NOT CASESPECIFIC
- CS or NOT CS
- CHARACTER SET
- COMPRESS or NO COMPRESS
- UPPERCASE or UC
Example: Alter a PTI Table
The table definition for this example includes an unnamed Primary Time Index.
CREATE TABLE ocean_buoy(c1 INTEGER, c2 INTEGER) PRIMARY TIME INDEX(TIMESTAMP(2) WITH TIME ZONE, DATE '2016-01-03', HOURS(2));
You can use an ALTER TABLE statement to add a name for the PRIMARY TIME INDEX.
ALTER TABLE ocean_buoy MODIFY PRIMARY TIME INDEX my_pti_index;
A SHOW TABLE statement lists the table definition, which now includes a named PRIMARY TIME INDEX.
SHOW TABLE ocean_buoy;
CREATE SET TABLE ocean_buoy ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
c1 INTEGER,
c2 INTEGER)
PRIMARY TIME INDEX my_pti_index (TIMESTAMP(6), DATE '2015-05-02', HOURS(1), COLUMNS(c1,c2), NONSEQUENCED);