Data Type Mapping between SQL Engine and ML Engine

Teradata® Vantage User Guide

prodname
Teradata Vantage
vrm_release
1.0
category
User Guide
featnum
B700-4002-098K

SQL Engine to ML Engine

SQL Engine Type SQL Engine Range Default ML Engine Type ML Engine Range Expected Data Loss in Conversion
BYTEINT -128 to 127 SMALLINT / INT2 -32,768 to +32,767 No
SMALLINT -32,768 to 32,767 SMALLINT / INT2 -32,768 to +32,767 No
INTEGER -2,147,483,648 to 2,147,483,647 INTEGER / INT4 -2,147,483,648 to +2,147,483,647 No
BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 BIGINT / INT8 -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 No
DECIMAL(p, s) / DEC / NUMERIC

p: total number of digits, s: number of digits to the right of the decimal point

1 <= p <= 38

0 <= s <= p

DECIMAL === DECIMAL(5, 0)

DECIMAL(p) === DECIMAL(p, 0).

  • The maximum value for DECIMAL(p, s) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
NUMERIC(p, s) / NUMERIC / NUMERIC(p)

1 <= p <= 1000

0 <= s <= p

NUMERIC(p) === NUMERIC(p, 0)

  • The maximum value for NUMERIC(p, s) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
No
FLOAT / REAL / DOUBLE PRECISION Values in sign/magnitude form ranging from 2.226 * 10-308 to 1.797 * 10+308 DOUBLE PRECISION / FLOAT4 / FLOAT(p)

1<=p<=24

The double precision type typically has a range of approximately 1.0 * 10-307 to 1.0 * 10+308 with a precision of at least 15 digits.

Infinity

-Infinity

NaN

No
NUMBER(p, s)

1<=p<=38

0<=s<=p

Unbounded number defined as NUMBER(*) / NUMBER is not supported.
  • The maximum value for DECIMAL(p, s) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
NUMERIC(p, s) / NUMERIC / NUMERIC(p)

1 <= p <= 1000

0 <= s <= p

NUMERIC(p) === NUMERIC(p, 0)

  • The maximum value for NUMERIC(p, s) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
No
VARCHAR(n)/CHARACTER VARYING(n)/CHAR VARYING(n)/LONG VARCHAR
  • For the LATIN server character set, the maximum value for n is 64,000 characters.
  • For the UNICODE and GRAPHIC server character sets, the maximum value for n is 32,000 characters.
  • For the KANJISJIS server character set, the maximum value for n is 32,000 bytes.
  • For LONG VARCHAR, the size is the maximum value of the n in the above description. That is, 64,000 characters for LATIN, 32,000 characters for UNICODE and GRAPHIC, and 32,000 bytes for KANJISJIS.
CHARACTER VARYING(n) / VARCHAR(n) Variable length with limit:

the maximum length is 10 MB.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
CHARACTER / CHAR(n)

default n::1

  • For the LATIN server character set, the maximum value for n is 64,000 characters.
  • For the UNICODE and GRAPHIC server character sets, the maximum value for n is 32,000 characters.
  • For the KANJISJIS server character set, the maximum value for n is 32,000 bytes.
CHARACTER(n) / CHAR(n) Fixed length with blank padded:

the maximum length is 10 MB.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
CHARACTER LARGE OBJECT/CLOB (n K/M/G)

default n::maximum value allowed

  • For LATIN:

    n <= 2,097,088,000.

    nK = n * 1024, for the LATIN server character set, n cannot exceed 2,047,937.

    nM = n * 1024K, for the LATIN server character set, n cannot exceed 2,047,937.

    nG = n * 1024M, n must be 1 and the server character set must be LATIN.

  • For UNICODE:

    n<= 1,048,544,000.

    nK = n * 1024, for the UNICODE server character set, n cannot exceed 1,023,968.

    nM = n * 1024k, for the UNICODE server character set, n cannot exceed 999.

    There is no G for the Unicode.

Based on the size:
  • If n < 10 MB:

    VARCHAR(n)

  • If n >10 MB:

    VARCHAR/CHARACTER VARYING

The maximum length is 10 MB. Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
DATE YEAR-MONTH-DAY

1<=YEAR<=9999

1<=MONTH<=12

1]=DAY<=31

DATE 4713 BC to 5,874,897 AD No
TIME[(p

0<=p<=6

SQL Engine supports SECOND > 60 (leap second), which is not supported by ML Engine.
00<=HOUR<=23

00<=MINUTE<=59

00.000000<=SECOND<=61.999999

TIME[(p)] WITHOUT TIME ZONE

0<=p<=6

00:00:00 24:00:00 No
TIME[(p)] WITH TIME ZONE

0<=p<=6

SQL Engine supports SECOND > 60 (leap second), which is not supported by ML Engine.
00<=HOUR<=23

00<=MINUTE<=59

00.000000<=SECONDS<=61.999999

-12.59<=TIMEZONE_HOUR<=+14.00

-12.59<=TIMEZONE_MINUTE<=+14.00

TIME[(p)] WITH TIME ZONE, TIMEZ

0<=p<=10

00:00:00+1459 24:00:00-1459 No
TIMESTAMP[(p)]

0<=p<=6

SQL Engine supports SECOND > 60 (leap second), which is not supported by ML Engine.
00<=HOUR<=23

00<=59<=59

00.000000<=SECOND<=61.999999

1<=YEAR<=9999

1<=MONTH<=12

1<=DAY<=31

TIMESTAMP[(p)] WITHOUT TIME ZONE

0<=p<=6

4713 BC to 5,874,897 AD No
TIMESTAMP[(p)] WITH TIMEZONE

0<=p<=6

SQL Engine supports SECOND > 60 (leap second), which is not supported by ML Engine.
00<=HOUR<=23

00<=59<=59

00.000000<=SECOND<=61.999999

1<=YEAR<=9999

1<=MONTH<=12

1<=DAY<=31

-12.59<=TIMEZONE_HOUR<=+14.00

-12.59<=TIMEZONE_MINUTE<=+14.00

TIMESTAMP[(p)] WITH TIME ZONE, TIMESTAMPZ

0<=p<=6

4713 BC to 5,874,897 AD No
BYTE(n) The maximum value for n is 64,000. BYTEA Variable length with limit:

the maximum length is 10 MB.

No
VARBYTE(n) The maximum value for n is 64,000. BYTEA Variable length with limit:

the maximum length is 10 MB.

No
BLOB(n K/M/G)

*

The maximum number of bytes is 2,097,088,000, which is the default if n is not specified.

K - n is specified in kilobytes (KB). When K is specified, n cannot exceed 2,047,937.

M - n is specified in megabytes (Mb). When M is specified, n cannot exceed 1,999.

G - n is specified in gigabytes (GB). When G is specified, n must be 1.

INTERVAL YEAR[(p)] TO MONTH

1<=p<=4

*

Range for YEAR depends on value of p.

MONTH=11

INTERVAL MONTH[(p)]

1<=p<=4

*

Range depends on value of p.
INTERVAL DAY[(p)]

1<=p<=4

*

Range depends on value of p.
INTERVAL DAY[(p)] TO HOUR

1<=p<=4

*

Range for DAY depends on value of p.

HOUR=23

INTERVAL DAY[(p)] TO MINUTE

1<=p<=4

*

Range for DAY depends on value of p.

HOUR=23

MINUTE=59

INTERVAL DAY[(p)] TO SECOND[(n)]

1<=p<=4

1<=n<=6

*

Range for DAY depends on value of p.

HOUR=23

MINUTE=59

SECOND=59

Value of n=9

INTERVAL HOUR[(p)]

1<=p<=4

*

Range depends on value of p.
INTERVAL HOUR[(p)] TO MINUTE

1<=p<=4

*

Range for HOUR depends on value of p.

MINUTE=59

INTERVAL HOUR[(p)] TO SECOND[(n)]

1<=p<=4

1<=n<=6

*

Range for HOUR depends on value of p.

MINUTE=59

SECOND=59

Value of n=9

INTERVAL MINUTE[(p)]

1<=p<=4

*

Range depends on value of p.
INTERVAL MINUTE[(p)] TO SECOND[(n)]

1<=p<=4

1<=n<=6

*

Range for MINUTE depends on value of p.

SECOND=59

Value of n=9

INTERVAL SECOND[(p, s)]

1<=p<=4

1<=s<=6

*

Range depends on value of p.

Value of s=9

PERIOD(DATE)

*

User specify the beginning(inclusive) and ending(exclusive) bound.
PERIOD(TIME[(p)])

0<=p<=6

*

User specify the beginning(inclusive) and ending(exclusive) bound.
PERIOD(TIME[(p)] WITH TIME ZONE)

0<=p<=6

*

User specify the beginning(inclusive) and ending(exclusive) bound.
PERIOD(TIMESTAMP[(p)])

0<=p<=6

*

User specify the beginning(inclusive) and ending(exclusive) bound.
PERIOD(TIMESTAMP[(p)] WITH TIME ZONE)

0<=p<=6

*

User specify the beginning(inclusive) and ending(exclusive) bound.
UDT(User Defined Type)

*

ARRAY / VARRAY

*

1 DIMENSION, n DIMENSIONS

2<=n<=5

TD_ANYTYPE

*

VARIANT_TYPE

*

JSON

*

The absolute maximum length is 16,776,192 bytes.
  • For LATIN, the maximum length is 16,776,192 characters.
  • For UNICODE, the maximum length is 8,388,096 characters.
XML / XMLTYPE

*

ST_Geometry

*

DATASET

*

GRAPHIC(n) /

CHARACTER(n) CHARACTER SET GRAPHIC

*

Maximum value for n is 32,000.      

VARGRAPHIC(n) /

VARCHAR(n) CHARACTER SET GRAPHIC

*

Maximum value for n is 32,000.      
* For SQL Engine Type without corresponding Default ML Engine Type, the conversion raises an error.

ML Engine to SQL Engine

ML Engine Type ML Engine Range Default SQL Engine Type SQL Engine Range Expected Data Loss in Conversion
SMALLINT / INT2 -32,768 to +32,767 SMALLINT -32,768 to 32,767 No
INTEGER / INT4 -2,147,483,648 to +2,147,483,647 INTEGER -2147483648 to 2147483647 No
BIGINT / INT8 -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 No
DECIMAL(p, s) / NUMERIC(p, s) / NUMERIC / NUMERIC(p)

1 <= p <= 1000

0 <= s <= p

DECIMAL === NUMERIC === DECIMAL(1000, s) for all possible values of s

DECIMAL(p) === NUMERIC(p) === NUMERIC(p, 0)

  • p > 38 is not supported.
  • Unbounded decimal / numeric defined as DECIMAL / NUMERIC is not supported.
  • The maximum value for DECIMAL(p, s)) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
NUMERIC(P)/NUMERIC(p, s)

p: total number of digits

s: number of digits to the right of the decimal point

1 <= p <= 38

0 <= s <= p

  • The maximum value for NUMERIC(p, s) is a value consisting of p 9s, with the decimal point s digits from the right.
  • The minimum value is the negative of the maximum value.
No
REAL / FLOAT4 / FLOAT(p)

1<=p<=24

1.0 * 10-37 to 1.0 * 10+37 with a precision of at least 6 decimal digits.

Infinity

-Infinity

NaN

FLOAT Values in sign/magnitude form ranging from 2.226 * 10-308 to 1.797 * 10+308 No
DOUBLE PRECISION / FLOAT8 / FLOAT(p)

25<=p<=53

1.0 * 10-307 to 1.0 * 10+308 with a precision of at least 15 decimal digits.

Infinity

-Infinity

NaN

FLOAT Values in sign/magnitude form ranging from 2.226 * 10-308 to 1.797 * 10+308 No
CHARACTER VARYING(n) / VARCHAR(n) Variable length with limit:

the maximum length is 10 MB.

  • If n < 32k characters for UNICODE (64k characters for LATIN):

    VARCHAR(n)

  • If n > 32k characters for UNICODE (64k characters for LATIN):

    CLOB

  • LATIN:

    n <= 2,097,088,000.

  • UNICODE:

    n <= 1,048,544,000.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.

VARCHAR

Variable length with limit:

the maximum length is 10 MB.

CLOB
  • LATIN:

    n <= 2,097,088,000.

  • UNICODE:

    n <= 1,048,544,000.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
CHARACTER(n) / CHAR(n) / CHARACTER / CHAR Fixed length with blank padded:

the maximum length is 10 MB.

default: n::1

  • If n < 32k characters for UNICODE (64k characters for LATIN):

    CHARACTER / CHAR(n)

  • If n > 32k characters for UNICODE (64k characters for LATIN):

    CLOB

default: n::1

  • If n < 32k characters for UNICODE (64k characters for LATIN):
    • For the LATIN server character set, the maximum value for n is 64,000 characters.
    • For the UNICODE and GRAPHIC server character sets, the maximum value for n is 32,000 characters.
    • For the KANJISJIS server character set, the maximum value for n is 32,000 bytes.
  • If n > 32k characters for UNICODE (64k characters for LATIN):
    • LATIN:

      n <= 2,097,088,000.

    • UNICODE:

      n <= 1,048,544,000.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
DATE
ML Engine can have YEAR > 9999 which is not supported by SQL Engine.
4713 BC to 5,874,897 AD DATE YEAR-MONTH-DAY

1<=YEAR<=9999

1<=MONTH<=12

1<=DAY<=31

No
TIME[(p)] WITHOUT TIME ZONE

0<=p<=6

00:00:00 24:00:00 TIME[(p)]

0<=p<=6

00<=HOUR<=23

00<=MINUTE<=59

00.000000<=SECOND<=61.999999

No
TIME[(p)] WITH TIME ZONE, TIMEZ

0<=p<=10

TIMEZONE range is different in ML Engine and SQL Engine.
00:00:00+1459 24:00:00-1459 TIME[(p)] WITH TIME ZONE

0<=p<=6

000<=HOUR<<=23

00<=MINUTE<=59

00.000000<=SECOND<=61.999999

-12.59<=TIMEZONE_HOUR<=+14.00

-12.59<=TIMEZONE_MINUTE<=+14.00

No
TIMESTAMP[(p)] WITHOUT TIME ZONE

0<=p<=6

ML Engine can have YEAR > 9999 which is not supported by SQL Engine.
4713 BC to 5,874,897 AD TIMESTAMP[(p)]

0<=p<=6

00<=HOUR<=23

00<=MINUTE<=59

00.000000<=SECOND<=61.999999

1<=YEAR<=9999

1<=MONTH<=12

1<=DAY<=31

No
TIMESTAMP[(p)] WITH TIME ZONE, TIMESTAMPZ

0<=p<=6

ML Engine can have YEAR > 9999 which is not supported by SQL Engine.
TIMEZONE range is different in ML Engine and SQL Engine.
4713 BC to 5,874,897 AD TIMESTAMP[(p)] WITH TIMEZONE

0<=p<=6

00<=HOUR<=23

00<=MINUTE<=59

00.000000<=SECOND<=61.999999

1<=YEAR<=9999

1<=MONTH<=12

1<=DAY<=31

-12.59<=TIMEZONE_HOUR<=+14.00

-12.59<=TIMEZONE_MINUTE<=+14.00

No
BOOLEAN TRUE/FALSE/NULL BYTEINT -128 to 127 No
TEXT Variable length with limit:

the maximum length is 32 MB.

CLOB
  • LATIN:

    n <= 2,097,088,000.

  • UNICODE:

    n <= 1,048,544,000.

Strings will be truncated if they are larger than the value of Default String Size property of the ML Engine Connector in QueryGrid™.
BYTEA Variable length with limit:

the maximum length is 10 MB.

VARBYTE(32,000)
32,000 is the default value of Default Binary Size property of the ML Engine Connector in QueryGrid™.
Strings will be truncated if they are larger than the value of Default Binary Size property of the ML Engine Connector in QueryGrid™.
SERIAL / SERIAL4 1 to 2147483647 INTEGER / INT4 -2,147,483,648 to +2,147,483,647 No
BIGSERIAL / SERIAL8 1 to 9,223,372,036,854,775,807 BIGINT / INT8 -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 No
INTERVAL -178,956,970 years to 178,956,970 years VARCHAR(49) 49 No
BIT(n) Value must match the length specified by n.

The maximum length of bit is 10MB (83,866,080 bits).

CHAR(n)
  • If n < 32k characters for UNICODE (64k characters for LATIN):
    • For the LATIN server character set, the maximum value for n is 64,000 characters.
    • For the UNICODE and GRAPHIC server character sets, the maximum value for n is 32,000 characters.
    • For the KANJISJIS server character set, the maximum value for n is 32,000 bytes.
  • If n > 32k characters for UNICODE (64k characters for LATIN):
    • LATIN:

      n <= 2,097,088,000.

    • UNICODE:

      n <= 1,048,544,000.

No
BIT VARYING(n) Value must match the length specified by n.

The maximum length of bit is 10MB (83,866,080 bits).

BLOB 2,097,088,000 No
UUID Unique string with 32 digits CLOB(10485760) 10485760 No
IP4

*

IP4RANGE

*

* For ML Engine Type without corresponding Default SQL Engine Type, the conversion raises an error.