The following table provides you with an overview of data type mapping between Teradata and Exasol.
| Teradata | Exasol | Comment |
|---|---|---|
| Numeric Data Types | ||
|
BYTEINT |
DECIMAL(9) | |
|
SMALLINT |
DECIMAL(9) or SMALLINT (Alias) |
You can also use the alias SMALLINT in Exasol which is a DECIMAL(9) |
|
INTEGER |
DECIMAL(10) or INTEGER |
You can also use the alias INTEGER in Exasol which is a DECIMAL(18) |
|
BIGINT |
DECIMAL(20) or BIGINT |
You can also use the Alias BIGINT in Exasol which is a DECIMAL(36) |
|
DECIMAL(p,s)
|
DECIMAL(p,s) |
The maximum precision in Exasol is 36, but in Teradata is 38 |
|
NUMBER |
DOUBLE PRECISION | Possible loss in precision |
|
NUMBER(p) |
DECIMAL(p,s) | |
|
NUMBER(p,s) |
DECIMAL(p,s) | |
|
FLOAT |
DOUBLE PRECISION | |
| Character Data Types | ||
|
CHARACTER(n)
|
CHAR(n) if n <= 2000 VARCHAR(n) if n >2000 |
For Exasol, the maximum for n is 2,000. By default, Exasol stores data in UTF-8. For Teradata, the maximum n for LATIN is 64000, and for UNICODE, it is 32000 |
|
VARCHAR(n)
|
VARCHAR(n) |
The maximum n for Exasol 2,000,000, and Teradata is 64000 for LATIN and 32000 for UNICODE. By default, Exasol stores data in UTF-8 |
|
CLOB |
VARCHAR(2000000) |
Exasol can store a maximum of 2,000,000 characters versus Teradata, which can store a maximum of 2,097,088,000 characters in a CLOB |
|
JSON |
VARCHAR(2000000) | |
|
XML |
VARCHAR(2000000) | |
| Date and Time Data Types | ||
| DATE | DATE | |
|
TIME (without timezone) |
TIMESTAMP |
Exasol does not have a dedicated datatype for TIME, but you can convert it to a TIMESTAMP with a generic date |
| TIME WITH TIME ZONE | TIMESTAMP |
We recommend normalizing the timestamps to UTC in Exasol. Exasol also offers a TIMESTAMP WITH LOCAL TIME ZONE datatype, which interprets the input/output values (for example, while inserting data or querying the table) depending on the session time zone. Please be aware that this is different from the Teradata behavior, where each value stored may have a different time zone |
|
TIMESTAMP |
TIMESTAMP |
The accuracy is limited to milliseconds |
|
TIMESTAMP WITH TIME ZONE |
TIMESTAMP |
We recommend normalizing the timestamps to UTC in Exasol. Exasol also offers a TIMESTAMP WITH LOCAL TIME ZONE datatype, which interprets the input/output values (for example, while inserting data or querying the table) depending on the session time zone. Please be aware that this is different from the Teradata behavior, where each value stored may have a different time zone |
|
PERIOD(DATE) |
DATE, DATE |
Split the PERIOD into two separate columns for the beginning and end date |
|
PERIOD(TIMESTAMP) |
TIMESTAMP, TIMESTAMP |
Split the PERIOD into two separate columns for the beginning and end timestamp |
|
PERIOD(TIME) |
TIMESTAMP, TIMESTAMP |
Split the PERIOD into two separate columns for the beginning and end timestamp |
| Interval Data Types | ||
|
INTERVAL YEAR |
INTERVAL YEAR TO MONTH | |
|
INTERVAL YEAR TO MONTH |
INTERVAL YEAR TO MONTH |
|
|
INTERVAL DAY |
INTERVAL DAY TO SECOND |
|
|
INTERVAL DAY TO HOUR |
INTERVAL DAY TO SECOND |
|
|
INTERVAL DAY TO MINUTE |
INTERVAL DAY TO SECOND | |
|
INTERVAL DAY TO SECOND |
INTERVAL DAY TO SECOND |
|
|
INTERVAL HOUR |
INTERVAL DAY TO SECOND | |
|
INTERVAL HOUR TO MINUTE |
INTERVAL DAY TO SECOND | |
|
INTERVAL HOUR TO SECOND |
INTERVAL DAY TO SECOND | |
|
INTERVAL MINUTE |
INTERVAL DAY TO SECOND |
|
|
INTERVAL SECOND |
INTERVAL DAY TO SECOND |
|
| Unsupported Data Types | ||
|
BYTE |
Not supported |
Exasol does not support binary data |
|
VARBYTE |
Not supported |
Exasol does not support binary data |
|
BLOB |
Not supported |
Exasol does not support binary data |
|
UDT |
Depends on type of UDT |
Distinct UDT Types, which are based on a single predefined data type (e.g., INTEGER or DECIMAL(m,n)), can be supported. More complex types can only be imported by transforming the UDT into a VARCHAR |
|
ARRAY/VARRAY |
VARCHAR(64000) |
ARRAYs can be serialized during import and stored as a VARCHAR in Exasol. The upper limit is the maximum of 64000 characters for a VARCHAR in Teradata |
