17.05 - Valid and Invalid INSERT Operations - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Valid INSERT Operations

An INSERT operation does not return an error message if either of the following occurs:
  • In Teradata session mode, the operation attempts to insert a character string that is longer or shorter than that declared for the column. The string is automatically adjusted and inserted. This could result in improper strings for the Kanji1 character data type.
  • The operation uses a query and no rows are returned.

INSERT Operations That Are Not Valid

An INSERT operation causes an error or failure message to be returned if any of the following are true.
  • The operation attempts to assign a value that will result in a violation of a unique index specification.
  • The operation attempts to insert a row with no value for a column that has no default and is defined as NOT NULL.
  • The operation attempts to assign a nonnull value that violates a CHECK constraint declared for a column.
  • The operation attempts to assign a value that is of a different numeric type than that declared for the column and the assigned value cannot be converted correctly.
  • The operation attempts to assign a character value that is not in the repertoire of the destination character data type.
  • The operation attempts to insert a character string trailing pad characters into a VARCHAR field, and that operation causes the row to become identical to another row (except for the number of trailing pad characters).
  • In ANSI session mode, inserting character data, if in order to comply with maximum length of the target column, non-pad characters are truncated from the source data.
KANJI1 support is deprecated. KANJI1 is not allowed as a default character set. The system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible.

Large Objects and INSERT

The behavior of truncated LOB inserts differs in ANSI and Teradata session modes. The following table explains the differences in truncation behavior.

Session Mode Result When Non-Pad Bytes are Truncated on Insertion
ANSI An exception condition is raised.

The INSERT fails.

Teradata Exception condition is not raised.

The INSERT succeeds: the truncated LOB is stored.