17.05 - Example: INSERT and GENERATED ALWAYS Identity Columns - 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)

Assume that column_1 of newly created table_1 is an identity column defined as GENERATED ALWAYS. The following INSERT statements automatically generate numbers for column_1 of the inserted rows, even if they specify a value for that column.

     INSERT INTO table_1 (column_1, column_2, column_3)
     VALUES (111,111,111);
     INSERT INTO table_1 (column_1, column_2, column_2)
     VALUES (,222,222);

Check the inserts by selecting all the rows from table_1.

     SELECT *
     FROM table_1;
     *** Query completed. 2 rows found. 3 columns returned.
     *** Total elapsed time was 1 second.
      column_1  column_2  column_3
     --------- --------- ---------
             2       111       111
             1       222       222
Note the following things about this result:
  • Even though the value 111 was specified for column_1 in the first insert, the value is rejected and replaced by the generated value 2 because column_1 is defined as GENERATED ALWAYS.
  • A warning is returned to the requestor when a user-specified value is overridden by a system-generated number.
  • The first number in the identity column sequence is not necessarily allocated to the first row inserted because of parallelism.