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

Teradata Vantageā„¢ - Time Series Tables and Operations

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
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.