Adding a Valid-Time Column - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

If a default value is specified for a new valid-time column, the column is populated with the specified value. Otherwise, the new column is populated as follows:

IF the valid-time column has a … THEN the column is populated with a value of …
PERIOD(DATE) data type PERIOD(TEMPORAL_DATE, UNTIL_CHANGED).
PERIOD(TIMESTAMP) data type PERIOD(TEMPORAL_TIMESTAMP, UNTIL_CHANGED).

The precision and time zone values are set depending on the data type of the new column.

In addition to the rules for specifying a valid-time column specified in CREATE TABLE/CREATE TABLE AS (Temporal Forms), the following rules apply when using ALTER TABLE to add a valid-time column to an existing table:

  • If the table has a transaction-time column, the ALTER TABLE statement must specify the NONTEMPORAL prefix. This requires the NONTEMPORAL privilege on the table.
  • The table cannot have a UPI.
  • If it does, first use ALTER TABLE table_name MODIFY NOT UNIQUE, which is described in SQL Data Definition Language, to modify the PI to be non-unique.
  • The table may have a NUPI, or it can have no primary index.
  • Existing CHECK constraints become current constraints in the valid-time dimension. An error is reported if there are any other types of constraints. See Using Constraints with Temporal Tables.
  • Any join indexes defined on the table must be dropped before the table can be made a valid-time table.
  • The table cannot be the subject table of an existing trigger.
  • Existing views, macros, or triggered action statements that reference the table but do not specify a valid-time qualifier in the statement referencing the table must be modified to add a valid-time qualifier.

If an executing stored procedure includes an SQL statement that references the table being altered, and no explicit qualifier is specified in the SQL, the compile time qualifier is applied to the SQL.

The partitioning for the table cannot be altered to be partitioned on the added valid-time column unless the table is empty.