Reference for SQL operators available in Firebolt.
Operator | Description | Associativity |
---|---|---|
. | Table/column name separator | left |
:: | Type cast | left |
[] | Array element | left |
+ - | Unary plus and minus | right |
AT | AT TIME ZONE | left |
^ | exponentiation | left |
* / % | multiplication, division, modulo | left |
+ / | addition, subtraction | left |
|| | concatenation | left |
BETWEEN | range containment | |
LIKE ILIKE | string matching | |
< > = <= >= <> | comparison | |
IS | IS NULL , IS DISTINCT FROM , IS TRUE | |
NOT | logical negation | right |
AND | logical conjunction | left |
OR | logical disjunction | left |
::
operator instead of the CAST function to convert one data type to another.
Component | Description |
---|---|
<value> | The value to convert or an expression that results in a value to convert. Can be a column name, a function applied to a column or another function, or a literal value. |
<type> | The target data type (case-insensitive). |
Operator | Operator description | Input Data Types | Output Data Types | Example | Result |
---|---|---|---|---|---|
+ | addition | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | SELECT 2 + 3; | 5 |
- | subtraction | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | SELECT 2 - 3; | -1 |
* | multiplication | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | SELECT 2 * 3; | 6 |
/ | division (integer division truncates the result) | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | SELECT 4 / 2; | 2 |
% | modulo (remainder) | INTEGER, BIGINT | Corresponding type | SELECT 5 % 4; | 1 |
^ | exponentiation | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | DOUBLE PRECISION | SELECT 2 ^ 3.0; | 8 |
INTEGER <op> INTEGER = INTEGER
INTEGER <op> BIGINT = BIGINT
INTEGER <op> REAL = DOUBLE PRECISION
REAL
data types have 6-digit precision and DOUBLE PRECISION
have 16-digit precision. This means that calculations have a precision of 6 or 16 respectively, and numbers are truncated to that precision. For example, if a number is stored as 1.234567, it is automatically truncated to 1.23456 for REAL
.interval
represents a duration.
In Firebolt, values of type interval
can be used to add or subtract a duration to/from a date or timestamp.
Interval
cannot be used as the data type of a column.
Operator | Description |
---|---|
DATE + interval -> TIMESTAMP | Add an interval to a DATE |
DATE - interval -> TIMESTAMP | Subtract an interval from a DATE |
TIMESTAMP + interval -> TIMESTAMP | Add an interval to a TIMESTAMP |
TIMESTAMP - interval -> TIMESTAMP | Subtract an interval from a TIMESTAMP |
TIMESTAMPTZ + interval -> TIMESTAMPTZ | Add an interval to a TIMESTAMPTZ |
TIMESTAMPTZ - interval -> TIMESTAMPTZ | Subtract an interval from a TIMESTAMPTZ |
interval * DOUBLE PRECISION -> interval | Multiply an interval by a scalar |
+
and *
operators shown above come in commutative pairs (e.g., both DATE + interval
and interval + DATE
are accepted).
Interval
literals can be specified in two formats.
INTERVAL
data type using various casting syntaxes.
Using interval keyword:
CAST
function:
direction
can be either ago
or left empty. Using ago
negates all specified quantities. The quantity
represents a signed or unsigned integer, and unit
refers to one of the following time units, matched case-insensitively:
Unit |
---|
microsecond[s] / us |
millisecond[s] / ms |
second[s] / s |
minute[s] / m |
hour[s] / h |
day[s] / d |
week[s] / w |
month[s] / mon[s] |
year[s] / y |
decade[s] / dec[s] |
century / centuries / c |
millennium[s] / mil[s] |
unit
can appear only once in an interval literal.
The value of the interval is determined by adding the quantities of the specified units with the appropriate signs.
Unit outside of text literalN
represents a signed or unsigned integer, and unit
specifies the time unit, matched case-insensitively, from the following options:
Unit |
---|
second |
minute |
hour |
day |
week |
month |
year |
TIMESTAMPTZ
values works as follows:
TIMESTAMPTZ
value from Unix time to local time according to the rules of the time zone specified by the session’s time_zone
setting.millennium
, century
, decade
, year
, month
, week
and day
components of the interval to the local time.time_zone
setting.hour
, minute
, second
, millisecond
, and microsecond
components of the interval to the Unix time.SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day'
returns 2022-10-31 00:00:00+01
but SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24 hours'
returns 2022-10-30 23:00:00+01
(assuming the value of the session’s time_zone
setting is 'Europe/Berlin'
).
Still, the dependence on the session’s time_zone
setting should be kept in mind when doing arithmetic between interval and TIMESTAMPTZ
.
date_time + INTERVAL * d
where date_time
is a constant or column reference of type DATE
, TIMESTAMP
, or TIMESTAMPTZ
, and d
is a constant or column reference of type DOUBLE PRECISION
.
The effect is that the INTERVAL is scaled by d
, and the resulting INTERVAL is added to date_time
.
E.g., INTERVAL '1 day' * 3
-> INTERVAL '3 days'
.
Operator | Example | Explanation |
---|---|---|
AND | x AND y | True if both x and y are true |
NOT | NOT x | True if x is false |
OR | x OR y | True if either x or y is true |
Operator | Syntax | Explanation |
---|---|---|
= | a=b | a is equal to b. |
!= | a!=b | a is not equal to b. |
<> | a<>b | a is not equal to b. |
<= | a<=b | a is less than or equal to b. |
> | a>b | a is greater than b. |
>= | a>=b | a is greater than or equal to b. |
< | a<b | a is less than b. |
BETWEEN | a BETWEEN b AND c | equivalent to b <= a <= c |
IS NULL | a IS NULL | a contains a NULL value |
IS NOT NULL | a IS NOT NULL | a doesn’t contain a NULL value |
IS NOT DISTINCT FROM | a IS NOT DISTINCT FROM b | equivalent to a=b where NULL is considered equal to NULL. |
IS DISTINCT FROM | a IS DISTINCT FROM b | equivalent to a!=b where NULL is considered equal to NULL. |
WHERE
clause
Parameter | Description | Supported input types |
---|---|---|
<condition> | A condition can be defined for each WHEN , and ELSE clause. | BOOLEAN |
<result> | The result of any condition. Every THEN clause receives a single result. All results in a single CASE function must share the same data type. | Any |
<result>
player_level
with the following columns and values:
player | currentlevel |
---|---|
kennethpark | 3 |
esimpson | 8 |
sabrina21 | 11 |
rileyjon | 15 |
burchdenise | 4 |
player | currentlevel | ranking |
---|---|---|
kennethpark | 3 | Beginner |
esimpson | 8 | Intermediate |
sabrina21 | 11 | Intermediate |
rileyjon | 15 | Expert |
burchdenise | 4 | Beginner |
CONCAT
function.
||
operator.
Operator | Explanation |
---|---|
EXISTS | The EXISTS operator is used to check for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records. The subquery within EXISTS is executed repeatedly, once for each row that might be selected by the outer query. If the subquery returns any row, the EXISTS condition is met, and the outer query processing continues for that row. |
NOT EXISTS | It is opposite of EXISTS and is used to find records in one table that have no related records in another table. If the subquery returns no rows, NOT EXISTS returns TRUE. |
IN | The IN operator checks if a specific value is present in a list of values or the results of a subquery. Commonly utilized in a WHERE clause, it compares a column’s value against a predefined set. When the column’s value matches any value in this set, IN yields TRUE . |
NOT IN | Retrieve all entries from the value list that don’t match the required value. |