MSQL Reference – Generic Functions

The following are the generic built-in functions for type-casting, NULL-value handling, conditional evaluation etc.

fn ABS

ABS(x: int64) -> int64

ABS(x: float64) -> float64

ABS(x: timeperiod) -> timeperiod

Returns the absolute value of the argument.

Return type

When the argument is an integer, the function will return an int64 result. When the argument is a floating-point number, the function will return a float64 result. When the argument is a timeperiod, the function will return a timeperiod.

Null handling

If the argument is NULL, the function will also return NULL.

Examples

SELECT ABS(4);
  --> 4

SELECT ABS(-27);
  --> 27

SELECT ABS(7.5);
  --> 7.5

SELECT ABS(-3.14);
  --> 3.14

SELECT ABS(tp '1h');
  --> 3600::timeperiod

SELECT ABS(tp '-3min');
  --> 180::timeperiod

See also

@ (ABS/LENGTH/SPAN) SIGN

fn CAST

CAST(expr: U AS typename(T)) -> T

Cast the expression to the specified type.

Notes

When downcasting a type, a loss of significant bits might occur. In the case of integers, only lower bits will be retained, the higher will be discarded.

Examples

SELECT CAST(257 AS float64);
  --> 257.0

Casting with loss of significant bits:

SELECT CAST(257 AS int8);
  --> 1

See also

:: operator

fn COALESCE

COALESCE(expr1: T1, expr2: T2, ..., exprN: TN) -> T

Returns the value of the first non-null argument, or NULL if all arguments are null.

Arguments

It requires at least one argument.

Data types of all the arguments must be implicitly convertible to a common type, which will also be the type of the result.

Notes

The COALESCE function is often used to supply a default value for an expression which could evaluate to NULL.
E.g. if some table column name might sometimes be NULL, SELECT COALESCE(name, 'n/a') will then replace the NULL with the default value 'n/a'.

Examples

SELECT COALESCE(1, 2);
  --> 1

SELECT COALESCE(NULL::int64, 2);
  --> 2

SELECT COALESCE(NULL::int64, NULL::int64);
  --> NULL

See also

?? operator NULLIF

fn IFNOTNULL

IFNOTNULL(expr1: U, expr2: V) -> V

If the first expression isn't NULL, returns the value of the second expression, otherwise returns NULL.

Return type

The return type of the function is always the same as the type of the second argument.

Notes

IFNOTNULL(x, y) is really just a shortcut for CASE WHEN x IS NOT NULL THEN y ELSE NULL END.

Examples

SELECT IFNOTNULL(1, 2);
  --> 2

SELECT IFNOTNULL(NULL::int64, 2);
  --> NULL

SELECT IFNOTNULL(1, NULL::int64);
  --> NULL

fn NULLIF

NULLIF(expr1: T, expr2: T) -> T

Returns NULL if the results of the two expressions are equal, otherwise returns the result of the first expression.

Notes

NULLIF is often used for recreating NULL values from other values which stand for them in particular contexts.

For example, as SpaceTime tables don't support storing NULL values, to represent a missing or non-existent value, another special value such as -1 or 255 could be used. But if NULL values need to be restored in queries, this can be done with the NULLIF function: e.g. NULLIF(x, -1) would replace all -1 values with NULLs and leave others the same as they were.

Another common use-case of the NULLIF function is preventing unwanted errors in query execution due to problematic arguments, most often the division by zero error.
For instance, if there's an expression a / b in which b can sometimes be zero (which would cause an error), the expression can be rewritten as a / NULLIF(b, 0), and it would then return NULL when b = 0 instead of causing an error.

NULLIF(x, y) is actually just a shortcut for more verbose CASE WHEN x = y THEN NULL ELSE x END.

Examples

SELECT NULLIF(10, -1);
  --> 10

SELECT NULLIF(-1, -1);
  --> NULL

See also

COALESCE

fn IF

IF(cond1: bool, expr1: T1, [cond2: bool, expr2: T2, ...][, exprElse: TN]) -> T

Returns the result of the expression following the first condition which is true.

Arguments

Data types of all the arguments must be implicitly convertible to a common type, which will also be the type of the result.

Notes

If no condition evaluates to true, the function will return the result of expression exprElse if present, otherwise NULL.

When a condition evaluates to NULL, the behaviour is the same as if it evaluated to false.

Actually, IF(cond1, expr1, ...[, exprElse]) is just a shortcut for the more verbose CASE WHEN cond1 THEN expr1 ... [ELSE exprElse] END.
It means that the rules of evaluation of the CASE expression hold also for this function's arguments, i.e. depending on the value of the condition, either exprTrue or exprFalse won't be evaluated at all.

Examples

SELECT x, IF(x < 10, 'smaller than 10', x > 10, 'larger than 10', 'neither smaller nor larger than 10')
  FROM (VALUES 14, 2, 10, NULL::int64) AS t(x);

--> +------+--------------------------------------+
--> |    x |                                   IF |
--> +------+--------------------------------------+
--> |   14 |                     'larger than 10' |
--> |    2 |                    'smaller than 10' |
--> |   10 | 'neither smaller nor larger than 10' |
--> | NULL | 'neither smaller nor larger than 10' |
--> +------+--------------------------------------+

SELECT x, IF(x < 10, 'smaller than 10', x > 10, 'larger than 10')
  FROM (VALUES 14, 2, 10, NULL::int64) AS t(x);

--> +------+-------------------+
--> |    x |                IF |
--> +------+-------------------+
--> |   14 |  'larger than 10' |
--> |    2 | 'smaller than 10' |
--> |   10 |              NULL |
--> | NULL |              NULL |
--> +------+-------------------+

See also

CASE

fn GREATEST

GREATEST(expr1: T1, expr2: T2, ..., exprN: TN) -> T

Returns the largest value from a list of any number of expressions.

Arguments

Data types of all the arguments must be implicitly convertible to a common type, which will also be the type of the result.

Null handling

NULL values in the list are ignored. The result will be NULL only if all the expressions are NULL.

Examples

SELECT GREATEST(10, 20, 15);
  --> 20

SELECT GREATEST(5, NULL::int64, 30);
  --> 30

SELECT GREATEST(NULL::int64, NULL::int64, NULL::int64);
  --> NULL

See also

LEAST MINIMUM MAXIMUM

fn LEAST

LEAST(expr1: T1, expr2: T2, ..., exprN: TN) -> T

Returns the smallest value from a list of any number of expressions.

Arguments

Data types of all the arguments must be implicitly convertible to a common type, which will also be the type of the result.

Null handling

NULL values in the list are ignored. The result will be NULL only if all the expressions are NULL.

Examples

SELECT LEAST(10, 20, 15);
  --> 10

SELECT LEAST(5, NULL::int64, 30);
  --> 5

SELECT LEAST(NULL::int64, NULL::int64, NULL::int64);
  --> NULL

See also

GREATEST MINIMUM MAXIMUM

fn MAXIMUM

MAXIMUM(expr1: T1, expr2: T2, ..., exprN: TN) -> T

Alias for the GREATEST function.

Notes

This function shouldn't be confused with the function MAX, which is an aggregate function.

See also

MINIMUM

fn MINIMUM

MINIMUM(expr1: T1, expr2: T2, ..., exprN: TN) -> T

Alias for the LEAST function.

Notes

This function shouldn't be confused with the function MIN, which is an aggregate function.

See also

MAXIMUM

fn SIGN

SIGN(x: int64) -> int64

SIGN(x: float64) -> int64

SIGN(x: timeperiod) -> int64

Returns the sign of the argument.

Null handling

If the argument is NULL, the function will also return NULL.

Notes

It returns -1 when the argument is negative, 1 when it's positive , and 0 when it's equal to zero.

Examples

SELECT SIGN(4);
  --> 1

SELECT SIGN(0.0);
  --> 0

SELECT SIGN(tp '-3d');
  --> -1