Operators

Reference for SQL operators available in Firebolt.

Operators are represented by special characters or keywords, they do not use function call syntax.

Operator Precedence

The following table lists all SQL operators from highest to lowest precedence, alongside with their associativity. The precedence and associativity of the operators is hard-coded into the parser. Add parentheses if you want an expression with multiple operators to be interpreted in some other way than what the precedence rules imply.

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

:: Type cast

Use can use the :: operator instead of the CAST function to convert one data type to another.

Syntax

 -- CAST function
 CAST(<value> AS <type>)
 -- :: operator
 <value>::<type>

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).

Example

SELECT '2021-12-31'::DATE;
SELECT 8.5::REAL;
SELECT col_a::BIGINT;

Arithmetic with numbers

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

In arithmetic operations like +, -, *, and / , the result’s data type aligns with the most encompassing type of the operands indicated as “Corresponding type” in the table above. For clarity:

• When both operands are of the same data type (e.g., two INTEGERs or two NUMERICs), the result will also be of that same data type.
  • INTEGER <op> INTEGER = INTEGER
  • INTEGER <op> BIGINT = BIGINT
• For operations involving two different numeric data types, the result will typically be of the more precise or larger data type.
  • INTEGER <op> REAL = DOUBLE PRECISION
• Overflow checks and floating point errors are applied according to the result data type only.

Floating point precision means that the representation of a number is accurate up to a certain number of digits. In Firebolt, 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.

When performing arithmetic, the number of leading digits in the output is the product of the leading digits in both inputs. This means that if either or both of the input numbers are larger than 6, those numbers are the first truncated, and then the arithmetic is performed.

Arithmetic with date/time and intervals

An 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

The + and * operators shown above come in commutative pairs (e.g., both DATE + interval and interval + DATE are accepted).

Literal string interpretation

Interval literals can be specified in two formats.

Format examples

Cast from text literal
The following examples demonstrate different ways to cast a text literal to an interval in SQL. Each method converts a string representing a time interval into the INTERVAL data type using various casting syntaxes.

Using interval keyword:

interval 'quantity unit [quantity unit...] [direction]'

Using double colon:

'quantity unit [quantity unit...] [direction]'::interval

Using CAST function:

CAST('quantity unit [quantity unit...] [direction]' AS interval)

In these examples, 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]

Each 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 literal
The following example demonstrates how to cast a numeric value to an interval by placing the unit outside of the text literal.

interval 'N' unit

In this format, N 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

Arithmetic between interval and TIMESTAMPTZ

Interval arithmetic with TIMESTAMPTZ values works as follows:

1. Convert the TIMESTAMPTZ value from Unix time to local time according to the rules of the time zone specified by the session’s time_zone setting.
2. Add the millennium, century, decade, year, month, week and day components of the interval to the local time.
3. Convert the local time back to Unix time according to the rules of the time zone specified by the session’s time_zone setting.
4. Add the hour, minute, second, millisecond, and microsecond components of the interval to the Unix time.

The back and forth between Unix time and local time is necessary to handle the fact that not all days consist of 24 hours due to daylight savings time transitions. For instance, 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.

Multiplying an interval by a scalar

You can use the expression 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'.

Examples

SELECT DATE '1996-09-03' - interval '1 millennium 5 years 42 day 42 ms';

?column? timestamp
0991-07-22 23:59:59.958

Rows: 1Execution time: 5ms

SELECT TIMESTAMP '1996-09-03 11:19:42' + interval '10 years 5 months 42 days 7 seconds';

?column? timestamp
2007-03-17 11:19:49

Rows: 1Execution time: 5ms

SELECT TIMESTAMP '2023-10-20 11:49:52' + interval '1 year 6 months 4 weeks 7 hours' * 7.5;

?column? timestamp
2035-08-20 16:19:52

Rows: 1Execution time: 5ms

SELECT DATE '2023-10-20' - 42 * interval '1 months 1 day 1 hour';

?column? timestamp
2020-03-07 06:00:00

Rows: 1Execution time: 5ms

The following example shows a daylight savings time change in the time zone 'Europe/Berlin':

-- Results depend on the session time_zone setting
SET time_zone = 'Europe/Berlin';
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day';  --> 2022-10-31 00:00:00+01
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour;  --> 2022-10-30 23:00:00+01

SET time_zone = 'US/Pacific';
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day';  --> 2022-10-30 15:00:00-07
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour;  --> 2022-10-30 15:00:00-07

Logical

Logical operators return the result of a boolean operation using three valued logic

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

Comparison

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.
ANY / ALL (array)	a = ANY(arr)	Quantified comparison against array elements. See ANY/ALL (array).
ANY / ALL (subquery)	a = ANY(SELECT ...)	Quantified comparison against the rows returned by a subquery. See ANY/ALL (subquery).

Example of using comparison operator in WHERE clause

SELECT
  *
FROM
  Table
WHERE
  Price >= 100;

CASE

Conditional expression similar to if-then-else statements. If the result of the condition is true, then the value of the CASE expression is the result that follows the condition. If the result is false, any subsequent WHEN clauses (conditions) are searched in the same manner. If no WHEN condition is true, then the value of the case expression is the result specified in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL.

Syntax

CASE
    WHEN <condition> THEN <result>
    [ WHEN ...n ]
    [ ELSE <result> ]
END;

Parameters

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

Return type

Same data type as <result>

Example

This example references a table player_level with the following columns and values:

player	currentlevel
kennethpark	3
esimpson	8
sabrina21	11
rileyjon	15
burchdenise	4

The following example categorizes each entry by length. If the movie is longer than zero minutes and less than 50 minutes it is categorized as SHORT. When the length is 50-120 minutes, it’s categorized as Medium, and when even longer, it’s categorized as Long.

SELECT
	player,
	currentlevel,
	CASE
		WHEN length > 0
		AND length <= 5 THEN 'Beginner'
		WHEN length > 5
		AND length <= 12 THEN 'Intermediate'
		WHEN length > 12 THEN 'Expert'
	END ranking
FROM
	player_level
ORDER BY
	player;

Returns:

player	currentlevel	ranking
kennethpark	3	Beginner
esimpson	8	Intermediate
sabrina21	11	Intermediate
rileyjon	15	Expert
burchdenise	4	Beginner

String

To concatenate strings, you can use the CONCAT function.

SELECT concat('This', ' is', ' a', ' parenthetical', ' concatenation.') AS concatenated_string

concatenated_string text
This is a parenthetical concatenation.

Rows: 1Execution time: 5ms

Alternatively, you can use the double pipe || operator.

SELECT 'This' || ' is' || ' a' || ' double pipe' || ' concatenation.' AS concatenated_string

concatenated_string text
This is a double pipe concatenation.

Rows: 1Execution time: 5ms

Subquery operators

Subqueries are queries nested within another query. They allow complex data retrieval by enabling a query to filter results based on the outcome of another query. Subquery operators are crucial in constructing these nested queries, especially within the WHERE clause, to filter data based on specific conditions.

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.
<op> ANY	Quantified comparison against the rows returned by a subquery. Returns TRUE if <value> <op> row holds for at least one row. <op> can be any comparison operator (=, <>, <, >, <=, >=). = ANY is equivalent to IN. See ANY/ALL (subquery).
<op> ALL	Quantified comparison against the rows returned by a subquery. Returns TRUE if <value> <op> row holds for every row. <> ALL is equivalent to NOT IN. See ANY/ALL (subquery).

Example–using EXISTS to find all suppliers with products equal to the price of 22

SELECT supplier_name
FROM suppliers
WHERE EXISTS (
  SELECT
    product_name
  FROM
    products
  WHERE
    products.supplier_id = suppliers.supplier_id
  AND
    price = 22);

Example–using the IN operator to return all customers from Mannheim or London

SELECT
  customer_name
FROM
  customers
WHERE
  customer_address IN ('Mannheim','London');

Example–using a correlated subquery to retrieve all the products that cost more than the average price

SELECT
  product_id,
  product_name,
  list_price
FROM
  products p
WHERE
  list_price > (
    SELECT
      AVG( list_price )
    FROM
      products
    WHERE
      category_id = p.category_id);

Example–using a scalar boolean subquery to retrieve rows based on true/false condition

SELECT
  *
FROM
  products
WHERE (
  SELECT CASE WHEN
    MIN(list_price) > 100
  THEN
    true
  ELSE
    false
  END
  FROM
    products);