Data Type Details
Numeric data types
Exasol supports approximate and exact numerical data types. The difference when using the approximate data types arises from the possible rounding differences, which can occur due to the type of storage and the way computations are performed. Information losses of this nature do not occur when using exact numeric types because of storage type.
Exasol type (ANSI type) | Range (min.,max.) | Precision | Note |
---|---|---|---|
Exact numeric type | |||
DECIMAL(p,s) | Precision, Scale: (p,s) |
s ≤ p≤ 36 p ≥ 1 and s ≥ 0 |
|
Approximate numeric type | |||
DOUBLE PRECISION | ~15 digits | About 15 digits can be stored, independent of the scale. The special element NaN is interpreted as NULL, the element Infinity is not supported |
Boolean data type
The ANSI American National Standards Institute SQL standard data type BOOLEAN is directly supported in Exasol.
Exasol type (ANSI type) | Range | Note |
---|---|---|
BOOLEAN | TRUE, FALSE, NULL/UNKNOWN |
Instead of Boolean literals, the following numbers and strings can be interpreted: TRUE: 1, '1', 'T', 't', 'TRUE' (case insensitive) FALSE: 0,'0','F','f','FALSE' (case insensitive) |
Date and time data types
Of the various data types of the ANSI SQL standard, Exasol currently supports the DATE and TIMESTAMP(3) types. DATE corresponds to a date. In addition to the date, TIMESTAMP(3) contains the time. Additionally, the data type TIMESTAMP(3) WITH LOCAL TIME ZONE considers the session time zone. For more information, see ALTER SESSION and SESSIONTIMEZONE.
Exasol type (ANSI type) | Range | Note |
---|---|---|
DATE | 01.01.0001 to 31.12.9999 |
|
TIMESTAMP(3) | 01.01.0001 00:00:00.000 to 31.12.9999 23:59:59.999 |
The accuracy is limited to milliseconds |
TIMESTAMP(3) WITH LOCAL TIME ZONE | 01.01.0001 00:00:00.000 to 31.12.9999 23:59:59.999 |
The range is similar to the TIMESTAMP(3) type; however, this type also considers the time zone. The accuracy is limited to milliseconds. |
Other considerations for data type TIMESTAMP(3) WITH LOCAL TIME ZONE
-
In the case of TIMESTAMP(3) WITH LOCAL TIME ZONE columns, the timestamps are internally normalized to UTC, while the input and output values are interpreted in the session time zone. Hence, users in different time zones can easily insert and display data without worrying about internal storage. However, you should be aware that executing the same SQL statements in sessions with different time zones can lead to different results.
-
While TIMESTAMP(3) is a simple structure consisting of a year, month, day, hour, minute, and second, data of type TIMESTAMP(3) WITH LOCAL TIME ZONE represents a specific moment on the time axis. Internally, the data is normalized to UTC because within certain time zones exist time shifts (for example, when switching from winter to summer time) and ambiguous periods (for example, when switching from summer to winter time). If such problematic data is inserted within the local session time zone, the session value TIME_ZONE_BEHAVIOR (changeable by ALTER SESSION) defines the course of action.
-
Exasol stores the TIMESTAMP datatype in UTC and performs all computations involving the TIMESTAMP datatype in UTC.
SQL clients using the Exasol JDBC driver may have their own time zone settings that affect how TIMESTAMP values are displayed. For more information, refer to the documentation for the respective SQL client.
The string for option TIME_ZONE_BEHAVIOR consists of two parts:
INVALID
When the time is set ahead in a time zone, then there is a gap. If timestamps are located within this gap, it can be treated in the following different ways:
SHIFT - Corrects the value by adding the daylight saving time offset (typically one hour)
ADJUST - Rounds the value to the first valid value after the time shift
NULLIFY - Sets the value to NULL
REJECT - Throws an exception
AMBIGUOUS
When the time is moved backward in a time zone, then ambiguous timestamps exist which can be treated in the following different ways:
ST - Interprets the value in Standard Time (ST)
DST - Interprets the values in Daylight Saving Time (DST)
NULLIFY - Sets the value to NULL
REJECT - Throws an exception
Therefore, the following combinations can be defined:
'INVALID SHIFT AMBIGUOUS ST'
'INVALID SHIFT AMBIGUOUS DST'
'INVALID SHIFT AMBIGUOUS NULLIFY'
'INVALID SHIFT AMBIGUOUS REJECT'
'INVALID ADJUST AMBIGUOUS ST'
'INVALID ADJUST AMBIGUOUS DST'
'INVALID ADJUST AMBIGUOUS NULLIFY'
'INVALID ADJUST AMBIGUOUS REJECT'
'INVALID NULLIFY AMBIGUOUS ST'
'INVALID NULLIFY AMBIGUOUS DST'
'INVALID NULLIFY AMBIGUOUS NULLIFY'
'INVALID NULLIFY AMBIGUOUS REJECT'
'INVALID REJECT AMBIGUOUS ST'
'INVALID REJECT AMBIGUOUS DST'
'INVALID REJECT AMBIGUOUS NULLIFY'
'INVALID REJECT AMBIGUOUS REJECT'
- When casting between the data types TIMESTAMP(3) WITH LOCAL TIME ZONE and TIMESTAMP(3), the session time zone is evaluated, and the TIMESTAMP WITH LOCAL TIME ZONE is transformed into a normal TIMESTAMP(3) data type. This is like displaying a TIMESTAMP(3) WITH LOCAL TIME ZONE value. For example, in your database client, the internal UTC normalized value is also converted to TIMESTAMP(3) data type, considering the session time zone.
- There are no special literals for the data type TIMESTAMP(3) WITH LOCAL TIME ZONE. The literal for data type TIMESTAMP(3) is considered, and the session time zone defines the corresponding moment on the time axis. For details about the arithmetic on datetime values and the datetime functions, see the Scalar Functions and Operators. For more information, see Literals.
- The timestamp values logged in statistical system tables are rendered in the database time zone (DBTIMEZONE). This is particularly relevant if you want to use the different functions for the current timestamp:
Timestamp | Description |
---|---|
SYSTIMESTAMP | Returns the current timestamp, rendered in the database time zone (DB-TIMEZONE) |
CURRENT_TIMESTAMP | Returns the current timestamp, rendered in the session time zone (SESSION_TIMEZONE) |
LOCALTIMESTAMP | Equivalent to CURRENT_TIMESTAMP |
NOW() | Equivalent to CURRENT_TIMESTAMP |
- The list of supported time zones can be found in the system table EXA_TIME_ZONES.
Interval data types
Using the interval data types you can define time periods which are especially useful for datetime arithmetic.
Exasol type (ANSI type) | Range | Note |
---|---|---|
INTERVAL YEAR [(p)] TO MONTH |
'-999999999-11' to '999999999-11' For example: '5-3' |
1 ≤ p ≤ 9 (default = 2) |
INTERVAL DAY [(p)] TO SECOND [(fp)] |
'-999999999 23:59:59.999' to '999999999 23:59:59.999' For example: '2 12:50:10.123' |
1 ≤ p ≤ 9 (default = 2), The accuracy is limited to milliseconds |
Geospatial data type
The geospatial data type can store various geometry objects. For more information, see Geospatial Data.
Exasol type (ANSI type) | Possible Elements | Note |
---|---|---|
GEOMETRY [(srid)] | POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION |
srid defines the coordinate system. The default value is 0 (none) For more information, see EXA_SPATIAL_REF_SYS. |
HASHTYPE data type
The HASHTYPE data type can be used to store hash values such as MD5 hashes or Universally Unique Identifiers (UUID).
There is no special literal for the HASHTYPE data type. Values are defined as strings and must be delimited with single quotes. Dashes (-) in the input hash string will be ignored. Curly brackets surrounding the input hash string are allowed.
Exasol type (ANSI type) | Note |
---|---|
HASHTYPE[(n BYTE | m BIT)] |
1 ≤ n ≤ 1024 BYTE Default = 16 BYTE When specifying the size in bits, the number of bits m must always be a multiple of 8. |
Input
The following formats are accepted as input for the HASHTYPE data type:
-
hex strings (0-F)
-
UUID
-
base64 - Encoded ascii string (e.g., chatkYY/Tsai+DDVIukNnw)
-
base64url - Encoded ascii string (e.g., chatkYY_Tsai-DDVIukNnw)
UUID is only supported for HASHTYPE (16 BYTE).
The following examples are valid for HASHTYPE (16 BYTE):
create table t1 (my_hash HASHTYPE);
insert into t1 values ('550e8400-e29b-11d4-a716-446655440000');
insert into t1 values ('{550e8400-e29b-11d4-a716-446655440000}');
insert into t1 values ('550e8400e29b11d4a716446655440000');
insert into t1 values ('550e-8400-e29b-11d4-a716-4466-5544-0000');
insert into t1 values ('VQ6EAOKbEdSnFkRmVUQAAA');
Output
The parameter HASHTYPE_FORMAT defines the output format of data that is stored as HASHTYPE. The default output of hex strings and UUID is a hex string. The format can be changed to UUID
Conversion from hash data type to a base64 and base64url encoded ascii string is done without a padding character. Conversion from base64 and base64url encoded ascii to a hash data type string supports both padded and unpadded input. The padding character = is optional. The implementation follows RFC-4648 standard. For more information, see RFC-4686.
String data types
There are two ANSI American National Standards Institute SQL standard types supported in Exasol for characters: CHAR and VARCHAR.
The CHAR(n) data type has a fixed and pre-defined length n. When you insert a shorter value than the pre-defined length, then spacing characters (padding) are used to fill the space. The length is limited to 2000 characters and you can use ASCII or UTF-8 (Unicode) to define the character set.
VARCHAR(n) can contain any string of the length n or smaller. These strings are stored in their respective length. The maximum allowed length is 2,000,000 characters and you can use ASCII or UTF-8 (Unicode) to define the character set.
If no character set is defined in both CHAR(n) and VARCHAR(n), by default UTF-8 will be considered.
Exasol type (ANSI type) | Character set | Note |
---|---|---|
CHAR(n) | ASCII, UTF-8 |
1 ≤ n ≤ 2,000 (default = 8) |
VARCHAR(n) | ASCII, UTF-8 |
1 ≤ n ≤ 2,000,000 (default = 128) |
An empty string is evaluated as NULL value.
To display the character set of a certain column you can use the DESC[RIBE] command.
stringtype_definition::=