MERGE with PTI Table Usage Notes | Teradata Vantage - 17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
Language
English (United States)

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.