Valid and Invalid INSERT Operations - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.