16.20 - ALTER TABLE - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

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);