Operators

• :: operator for CAST
• Arithmetic (numbers)
• Boolean
• Comparison
• CASE
  • Return type
• Date and time arithmetic
• String
• Subquery operators

:: operator for 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>

Example

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

Arithmetic (numbers)

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 note:

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.

Boolean

Boolean operators return the result of a Boolean operation between one or more expressions.

Comparison

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

Return type

Same data type as <result>

Example

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

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:

Date and time arithmetic

Use the INTERVAL operator to add to or subtract from a period of time in DATE, TIME, or TIMESTAMP data types.

Syntax

{ +|- } INTERVAL '<quantity> [ <date_unit> ] [ ...]'

Example

<date_column> + INTERVAL '1 year 2 months 3 days'
<date_column> - INTERVAL '2 weeks'
<date_column> - INTERVAL '1 year 3 hours 20 minutes'

String

To concatenate strings, you can use the CONCAT function.

SELECT concat('This', ' is', ' a', ' parenthetical', 'concantenation.') AS concatenated_String

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

SELECT 'This' || ' is' || ' a' || ' double pipe' || ' concantenation.' AS concatenated_String

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.

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 avgerage 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);