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.

Data type	Precision	Note
Exact numeric type
`DECIMAL(p,s)`	Precision, scale: (p,s)	`p` = precision (optional) 1 ≤ `p` ≤ 36, default = `18` `s` = scale (optional) 0 ≤ `s` ≤ 36, default = `0` `s` ≤ `p`
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.

String data types

Exasol supports two SQL standard types for characters: CHAR and VARCHAR.

CHAR(n) has a fixed and pre-defined length n. If you insert a value that is shorter than n, space characters are automatically added to fill the remaining space (padding). The maximum allowed length is 2,000 characters.

VARCHAR(n) can contain a string of the length n or smaller. The string is stored in its actual length without padding. The maximum allowed length is 2,000,000 characters.

You can use ASCII or UTF-8 (Unicode) to define the character set in CHAR and VARCHAR. If no character set is defined, UTF-8 is used by default.

Data type	Character set	Note
`CHAR(n)`	ASCII, UTF-8	`n` = number of characters (optional) 1 ≤ `n` ≤ 2000, default = `1`
`VARCHAR(n)`	ASCII, UTF-8	`n` = number of characters 1 ≤ `n` ≤ 2,000,000

An empty string is evaluated as NULL.

To display the character set of a certain column you can use the DESC[RIBE] command.

stringtype_definition::=

String Data Type

Date and time data types

Exasol supports the DATE and TIMESTAMP data types from the SQL standard. Additionally, the data type TIMESTAMP WITH LOCAL TIME ZONE can be used to handle timestamps in the session time zone. For more information, see ALTER SESSION and SESSIONTIMEZONE.

Data type	Range	Note
`DATE`	`0001-01-01` to `9999-12-31`
`TIMESTAMP`	`0001-01-01 00:00:00.000` to `9999-12-31 23:59:59.999`	Timestamp without time zone. The accuracy is limited to milliseconds.
`TIMESTAMP WITH LOCAL TIME ZONE`	`0001-01-01 00:00:00.000` to `9999-12-31 23:59:59.999`	Timestamp considering the session time zone. The accuracy is limited to milliseconds.

TIMESTAMP WITH LOCAL TIME ZONE usage notes

In TIMESTAMP 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.

When casting between the data types TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP, the session time zone is evaluated and the TIMESTAMP WITH LOCAL TIME ZONE is transformed into a TIMESTAMP data type. This is the same as displaying a TIMESTAMP WITH LOCAL TIME ZONE value. For example, in your database client, the internal UTC normalized value is also converted to TIMESTAMP data type, considering the session time zone.

TIME_ZONE_BEHAVIOR

While TIMESTAMP is a simple structure consisting of a year, month, day, hour, minute, and second, data of type TIMESTAMP WITH LOCAL TIME ZONE represents a specific moment on the time axis. Internally, the data is normalized to UTC because within certain time zones there may be time shifts and ambiguous periods, for example, when switching to/from daylight saving time. If such problematic values are inserted within the local session time zone, the session value TIME_ZONE_BEHAVIOR defines how they are treated. The value is set using ALTER SESSION and consists of a string defining the two parameters INVALID and AMBIGUOUS, separated by spaces.

When time is set forward in a time zone, there will be a gap. To define how a timestamp located in the gap should be treated, set one of the following values for INVALID:

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

When time is set back in a time zone, timestamps may become ambiguous. To define how an ambiguous timestamp should be treated, set one of the following values for AMBIGUOUS:

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

Example:

ALTER SESSION SET TIME_ZONE_BEHAVIOR='INVALID ADJUST AMBIGUOUS NULLIFY';

Literals

There are no special literals for the data type TIMESTAMP WITH LOCAL TIME ZONE. The literal for data type TIMESTAMP 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.

Timestamp in statistical system tables

The timestamp values logged in statistical system tables are rendered in the database time zone (DBTIMEZONE), set from EXAoperation. This is particularly relevant if you want to use the different functions for the current timestamp:

Function	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`

A 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 that are especially useful for datetime arithmetic.

Data type Range Note

Data type	Range	Note
`INTERVAL YEAR(p) TO MONTH`	`'-999999999-11'` to `'999999999-11'` For example: `'5-3'`	`p` = leading field precision (optional) 1 ≤ `p` ≤ 9, default = `2` The leading field precision `p` is given as the maximal number of decimal digits for the number of years.
`INTERVAL DAY(lfp) TO SECOND(fsp)`	`'-999999999 23:59:59.999'` to `'999999999 23:59:59.999'` For example: `'2 12:50:10.123'`	`lfp` = leading field precision (optional) 1 ≤ `lfp` ≤ 9, default = `2` The leading field precision `lfp` is given as the maximal number of decimal digits for the number of days. `fsp` = fractional second precision (optional) 0 ≤ `fsp` ≤ 9, default = `3` The accuracy is limited to milliseconds. The fractional seconds precision `fsp` is given as the number of decimals used to store fractional seconds. For example, `3` means millisecond precision.

INTERVAL YEAR(p) TO MONTH

'-999999999-11' to '999999999-11'

For example: '5-3'

p = leading field precision (optional)

1 ≤ p ≤ 9, default = 2

The leading field precision p is given as the maximal number of decimal digits for the number of years.

INTERVAL DAY(lfp) TO SECOND(fsp)

'-999999999 23:59:59.999' to '999999999 23:59:59.999'

For example: '2 12:50:10.123'

lfp = leading field precision (optional)

1 ≤ lfp ≤ 9, default = 2

The leading field precision lfp is given as the maximal number of decimal digits for the number of days.

fsp = fractional second precision (optional)

0 ≤ fsp ≤ 9, default = 3

The accuracy is limited to milliseconds.

The fractional seconds precision fsp is given as the number of decimals used to store fractional seconds. For example, 3 means millisecond precision.

Boolean data type

The SQL standard data type BOOLEAN is directly supported in Exasol.

Data type	Range	Note
`BOOLEAN`	`TRUE`, `FALSE`, `NULL` (unknown)	Integers or strings (case-insensitive) can be interpreted as well as boolean literals: `TRUE`, `1`, `'1'`, `'T'`, `'t'`, `'TRUE'` , `'true'` `FALSE`, `0`, `'0'`, `'F'`, `'f'`, `'FALSE'` , `'false'`

Geospatial data type

The geospatial data type can store various geometry objects. For more information, see Geospatial Data.

Data type	Possible elements	Note
`GEOMETRY(srid)`	`POINT`, `LINESTRING`, `POLYGON`, `MULTIPOINT`, `MULTILINESTRING`, `MULTIPOLYGON`, `GEOMETRYCOLLECTION`	`srid` (optional) defines the coordinate system used. Default = `0` (none)

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.

Data type	Note
`HASHTYPE(n BYTE)` or `HASHTYPE(m BIT)`	`n` = size in bytes (optional) 1 ≤ `n` ≤ 1024 `m` = size in bits (optional) 8 ≤ `m` ≤ 8192 (`m` must always be a multiple of 8) Default = `16 BYTE`

Input formats

The following formats are accepted as input for the HASHTYPE data type:

Hexadecimal string
UUID

UUID is only supported for HASHTYPE(16 BYTE).

Examples of valid input strings 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');

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. For more information, see ALTER SESSION / ALTER SYSTEM.