Inserting Into Distinct UDT Columns - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Inserting Into Distinct UDT Columns

To insert into a distinct column, either of the following must be true:

  • The inserted value must be of the same distinct type as the column.
  • There must exist a cast, either system-generated or user-defined, that converts the type of the inserted value to the distinct type of the column, and that cast was created with the AS ASSIGNMENT option. See CREATE CAST in SQL Data Definition Language.
  • A distinct value can be constructed from any of the following:

  • A cast expression where the target is the same distinct type.
  • An instance method invocation that returns the same distinct type.
  • A UDF invocation that returns the same distinct type.
  • A column reference.
  • A null.
  • For example, suppose you have the following two table definitions:

         CREATE TABLE table_1 (
           column1 euro, 
           column2 INTEGER)
         UNIQUE PRIMARY INDEX(column2);
     
         CREATE TABLE table_2 (
           column1 euro, 
           column2 INTEGER)
         UNIQUE PRIMARY INDEX(column2);

    For this case, the information in the following table is true:

     

    Example

    Comment

    INSERT INTO table_1 
    VALUES (5.64, 1);	

    Valid if there is a cast with AS ASSIGNMENT, whether system-generated or user-defined, to convert a DECIMAL type to the distinct type euro.

    INSERT INTO table_1 
    VALUES (CAST(5.64 AS euro), 1);	

    Valid because an explicit cast is specified for conversion from the source type euro.

    INSERT INTO table_1 
    SELECT CAST(us_dollar_col AS euro), c2
    FROM us_sales;	

    Valid if the cast from us_dollar to euro is valid.

    INSERT INTO table_1 						
    SELECT column1.roundup(0), column2
    FROM TAB2;	

    Valid if the method roundup() returns the euro type.

    INSERT INTO table_1 						
    SELECT column1, column2 from table_2;	

    Valid column reference.

    INSERT INTO table_1 
    VALUES (NULL, 8);

    Valid because the request insert a null into a distinct type column.