MERGE with PTI Table Usage Notes | Teradata Vantage - 17.00 - Usage Notes - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

Refer to the rules and restrictions below when using MERGE statements with PTI tables.

You cannot specify the system generated TD_TIMEBUCKET column in a MERGE statement, including column_name, column_name_list, subquery_source, expression, in a WHERE clause, and so forth. However, you can use the TD_GETTIMEBUCKET function to retrieve TD_TIMEBUCKET column values.

When you merge data into a PTI table, the system automatically generates the TD_TIMEBUCKET value based on the TD_TIMECODE.

When the target table is a PTI table, you must specify the following columns in the ON clause with an equality predicate:
  • TD_TIMECODE column.
  • TD_SEQNO column, for a sequenced PTI table.
  • Each column specified in the COLUMNS clause.

When merging into a PTI table, you cannot merge rows where the source TD_TIMECODE value precedes the time zero value specified in the target table PTI clause, such as DATE '2016-01-03'.

The table below lists the various merge combinations.

Source Table Target Table ON Clause
PTI table PTI table Must specify an equality predicate for each of the following columns:
  • TD_TIMECODE column
  • Each column in the COLUMNS specification.
  • TD_SEQNO column for a sequenced table.
non-PTI table PTI table
PTI table non-PTI table Must specify an equality predicate for each of the following columns:
  • Each PI column in the primary index clause.
  • Each PPI column in the PARTITION BY clause.