MSQL Reference – Generic Operators

The following are the built-in generic (i.e. general-purpose) operators available in the MSQL language.

CASE

CASE WHEN (cond1: bool) THEN (expr1: T1) [WHEN (cond2: bool) THEN (expr2: T2)] ... [ELSE (exprN: Tn)] END -> T

CASE (switchExpr: T) WHEN (val1: T) THEN (expr1: U1) [WHEN (val2: T) THEN (expr2: U2)] ... [ELSE (exprN: Un)] END -> U

A generic conditional expression, similar to if/else statements in other programming languages.

Arguments

Result types of all the expr1..N expressions must be of the same type family (e.g. numeric, character) and convertible to a common type, which will also be the type of the result.

Notes

There are two forms of the expression, a regular, if-else-like form (also called searched CASE, first signature) and a simplified, switch-case-like form (also called simple CASE, second signature).

In the first form, if a boolean expression cond1 is true, it returns the value of expr1; if cond2 is true, it returns the value of expr2 etc. When none of them are true and there's an ELSE present, it returns the value of exprN (the return value for the ELSE branch), otherwise the result is NULL.

In the second form, the result of the expression switchExpr is checked against each of the val1..N values until one of them is found equal to it, and the result of the corresponding exprI is then returned. When none of the val1..N match and there's an ELSE present, the result of exprN is returned (the value for the ELSE branch), otherwise, the result is NULL.

If a certain branch condition (or value-comparison) isn't true, the corresponding result expression won't be evaluated at all.
Likewise, when a branch condition (or value-comparison) is found to be true, none of the following branches (if any) will ever be evaluated.

Examples

SELECT x,
    CASE
      WHEN x < 0 THEN 'lesser'
      WHEN x = 0 THEN 'equal'
      ELSE 'greater'
    END
  FROM (VALUES 4, -2, 0) AS t(x);

--> +----+-----------+
--> |  x |      CASE |
--> +----+-----------+
--> |  4 | 'greater' |
--> | -2 |  'lesser' |
--> |  0 |   'equal' |
--> +----+-----------+

SELECT x,
    CASE x
      WHEN 'one' THEN 1
      WHEN 'two' THEN 2
      WHEN 'three' THEN 3
    END
  FROM (VALUES 'two', 'three', 'five') AS t(x);

--> +---------+------+
--> |       x | CASE |
--> +---------+------+
--> |   'two' |    2 |
--> | 'three' |    3 |
--> |  'five' | NULL |
--> +---------+------+

See also

CAST

ITEM OPERATOR

(x: range[T]) [(idx: int64)] -> T

(p: [geog]point) [(n: int64)] -> float64

Returns the range element at the given index, or the n-th point coordinate.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

When idx is neither 0 nor 1, the function will return an error: Range index out of bounds.

Notes

When the first argument is a range, for the index 0 the function returns either the start of the range (when idx = 0 or end, when idx = 1)

Examples

SELECT RANGE(10, 20)[0];
  --> 10

SELECT RANGE(10, 20)[1];
  --> 20

SELECT ST_Point(100, 200)[0];
  --> 100.00

SELECT ST_Point(100, 200)[1];
  --> 200.00

?? COALESCE OPERATOR

(x: T) ?? (y: T) -> T

If the left operand is NULL, it returns the right operand, otherwise it returns the left one.

Null handling

If both operands are NULL, the result will be NULL as well.

Notes

The ?? operator is just a shortcut for the COALESCE function.

Examples

SELECT x ?? 'n/a' FROM (VALUES NULL::text) AS t(x);
  --> 'n/a'