17.05 - Syntax - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
Teradata Vantage™ - Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this document. For additional syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 , Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 , and Teradata Vantage™ - SQL Data Control Language, B035-1149.
[ { CURRENT | NONSEQUENCED } VALIDTIME |
  [ SEQUENCED ] VALIDTIME [ period_expression ] |
    NONTEMPORAL
]
upsert_statement [;]
To ensure application portability to ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.
CURRENT VALIDTIME
Specifies that the upsert is current in the valid-time dimension.

The CURRENT VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.

If the session temporal qualifier is not set and the temporal qualifier is omitted from the upsert statement, the default qualifier is CURRENT VALIDTIME.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

CURRENT DML modifications can cause serializability issues for concurrent transactions. See Potential Concurrency Issues with Current Temporal DML for information on avoiding these issues.

VALIDTIME and SEQUENCED VALIDTIME
Specifies that the upsert is sequenced in the valid-time dimension if the target table supports valid time.

The target table must have a valid-time column.

A sequenced upsert on a target table that supports valid time sets the valid-time value to period_expression, or, if omitted, to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP) valid-time column.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

period_expression

Specifies the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP( n ) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

NONSEQUENCED VALIDTIME
Specifies that the upsert is nonsequenced in the valid-time dimension if the target table supports valid time.

The target table must have a valid-time column.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

upsert_statement
Specifies existing UPDATE (Upsert Form) statement syntax.

For details, see SQL Data Manipulation Language.

The following restrictions apply to the SET clause of the UPDATE portion of upsert_statement:
  • For a current or sequenced update on a table with valid time, the SET clause cannot reference the valid-time column as the name of the column whose data is to be updated.
  • For an update on a table with transaction time, the SET clause cannot reference the transaction-time column as the name of the column whose data is to be updated.
  • The SET clause cannot use CURRENT_DATE or CURRENT_TIMESTAMP in the expressions that are used to assign a value to the valid-time column.
  • For a row-partitioned table, the SET clause cannot reference a partitioning column