17.05 - CREATE TYPE Syntax Elements (Distinct Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)
An optional specification of the containing database for UDT_name, which must always be SYSUDTLIB.
Name of the distinct UDT to be created.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
See Teradata Vantage™ - SQL Fundamentals, B035-1141 for the rules for naming database objects.
Creating a distinct type consumes two names in the TVMNameI name space for SYSUDTLIB:
  • The first name consumed corresponds to the name of the UDT itself.
  • The second name corresponds to the system-generated UDF.

    The system constructs this name as explained in the topic on naming conventions.

You cannot create a UDF within the SYSUDTLIB database with the same specific name or same routine signature.
If such a UDF already exists in SYSUDTLIB at the time you submit a CREATE TYPE request, then the request fails because a routine with the same signature already exists.
Predefined data type on which the distinct UDT is based.
You cannot specify the following data types:
  • JSON
If data_type is a character type, then this is the server character set used by data_type , otherwise you cannot specify anything.
The purpose of the CHARACTER SET clause is to influence the system-generated routines.
The CHARACTER SET clause causes the following things to occur when the system creates the distinct type named UDF_name:
  • The system-generated CAST routines are registered to cast to and cast from a character source type of the specified server character set.
  • The system-generated ORDERING routine is registered to map to a character source type of the specified server character set.
  • The system-generated tosql and fromsql TRANSFORM routines are registered to transform the UDT to and from a character source type of the specified server character set.
If you know that all database transactions occur in languages that can be expressed using the LATIN character set, specify LATIN. Otherwise, for example, if some or all transactions use non-LATIN or multibyte characters, specify UNICODE. See Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.
You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.
Mandatory keyword required for the definition of all distinct UDTS.
The FINAL keyword indicates that you cannot create subtypes of the type being defined.