1.1 - 8.10 - Existing Table - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)
You cannot add a unique identifier column to an existing table with the ALTER TABLE command. Instead, you must create a new table from the existing table, giving the new table a unique identifier column by specifying either of the following:
  • IDENTITY clause with options GENERATED ALWAYS and NO CYCLE
  • row_number()

row_number() is more likely to impact performance.

Example: IDENTITY Clause with GENERATED ALWAYS and NO CYCLE

The table iris_attribute_train does not have a unique row identifier:

SELECT TOP 5 * FROM iris_attribute_train ORDER BY pid;

        pid     attribute    attrvalue
-----------  ------------  -----------
          1  petal_width           0.2
          1  sepal_width           3.5
          1  petal_length          1.4
          1  sepal_length          5.1
          2  sepal_width             3

The following CREATE and INSERT statements create a new table, iris_attr_train_uniqueid, which is identical to iris_attribute_train but has an additional unique id column named a1.

CREATE MULTISET TABLE iris_attribute_train_uniqueid (
  a1 INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1 NO CYCLE),
  pid INTEGER,
  attribute VARCHAR(30),
  attrvalue FLOAT
);

INSERT INTO iris_attribute_train_uniqueid
  SELECT null, iris_attribute_train.* FROM iris_attribute_train;
SELECT TOP 5 * FROM iris_attribute_train_uniqueid ORDER BY a1;

         a1          pid     attribute    attrvalue
-----------  -----------  ------------  -----------
          1           57  petal_width           1.6
          2           57  sepal_length          6.3
          3           57  petal_length          4.7
          4           57  sepal_width           3.3
          5          118  sepal_width           3.8

Example: row_number()

CREATE MULTISET TABLE test04 AS (
  SELECT product, mttf, row_number() OVER (ORDER BY 1) AS id_col FROM factory_7
) WITH DATA;