Data types
This topic lists the data types available in Firebolt.
Numeric
INT
A whole number ranging from -2,147,483,648 to 2,147,483,647. INT data types require 4 bytes of storage. Synonyms: INTEGER, INT4.
DECIMAL
An exact numeric data type defined by its precision (total number of digits) and scale (number of digits to the right of the decimal point). For more information, see DECIMAL data type.
BIGINT
A whole number ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. BIGINT data types require 8 bytes of storage. Synonyms: LONG, INT8.
FLOAT
A floating-point number that has six decimal-digit precision. Decimal (fixed point) types are not supported. FLOAT data types require 4 bytes of storage. Synonyms: REAL, FLOAT4, FLOAT(p) where 1 <= p <= 24.
DOUBLE
A floating-point number that has 15 decimal-digit precision. Decimal (fixed point) types are not supported. DOUBLE data types require 8 bytes. Synonyms: DOUBLE PRECISION, FLOAT8, FLOAT(p) where 25 <= p <= 53.
String
TEXT
A string of an arbitrary length that can contain any number of bytes, including null bytes. Useful for arbitrary-length string columns. Firebolt supports UTF-8 escape sequences. Synonyms: STRING, VARCHAR
Date and time
DATE
A year, month and day in the format YYYY-MM-DD. The minimum DATE value is 1970-01-01. The maximum DATE value is 2105-12-31. It does not specify a time zone.
Arithmetic operations can be executed on DATE values. The examples below show the addition and subtraction of integers.
CAST(‘2019-07-31' AS DATE) + 4
Returns: 2019-08-04
CAST(‘2019-07-31' AS DATE) - 4 Returns: 2019-07-27
Working with dates outside the allowed range
Arithmetic, conditional, and comparative operations are not supported for date values outside the supported range. These operations return inaccurate results because they are based on the minimum and maximum dates in the range rather than the actual dates provided or expected to be returned.
The arithmetic operations in the examples below return inaccurate results as shown because the dates returned are outside the supported range.
CAST ('1970-02-02' AS DATE) - 365
Returns 1970-01-31
CAST ('2105-02-012' AS DATE) + 365
Returns 2105-12-31
If you work with dates outside the supported range, we recommend that you use a string datatype such as TEXT. For example, the following query returns all rows with the date 1921-12-31.
SELECT
*
FROM
tab1text
WHERE
date_as_text = '1921-12-31';
The example below selects all rows where the date_as_text column specifies a date after 1921-12-31.
SELECT
*
FROM
tab1text
WHERE
date_as_text > '1921-12-31';
The example below generates a count of how many rows in date_as_text are from each month of the year. It uses SUBSTR to extract the month value from the date string, and then it groups the count by month.
SELECT
COUNT(), SUBSTR(date_as_text,6,2)
FROM
tab1text
GROUP BY
SUBSTR(date_as_text,6,2);
TIMESTAMP
A year, month, day, hour, minute and second in the format YYYY-MM-DD hh:mm:ss.
The minimum TIMESTAMP value is 1970-01-01 00:00:00. The maximum TIMESTAMP value is 2105-12-31 23:59.59
Synonyms: DATETIME
Boolean
BOOLEAN
Represents boolean value of TRUE or FALSE. Synonyms: BOOL
Composite
ARRAY
Represents an array of values. All elements of the array must have same data type. Elements of the array can be of any supported data type including nested arrays (array of arrays).
Example
The following CREATE TABLE statement shows arrays of different element types and different nullabilities.
CREATE DIMENSION TABLE demo (
a_t ARRAY(TEXT NULL) NULL,
a_i ARRAY(INT NULL) NOT NULL,
a_d ARRAY(DATE NOT NULL) NULL,
a_f ARRAY(FLOAT NOT NULL) NOT NULL,
a_a ARRAY(ARRAY(INT NULL) NULL) NULL
);
The following INSERT INTO statement demonstrates examples of values for these arrays.
INSERT INTO demo VALUES
(
['Hello', NULL, 'world'],
[1, 42, NULL],
[DATE '2000-01-01'],
[3.14, 2.71, 9.8],
[ [1, 2], [NULL], NULL]
),
(
NULL,
[],
NULL,
[],
NULL
)