Expressions

An expression is a combination of values, operators, and functions that evaluates to a single value. Expressions are highly composable and can range from very simple (e.g., a single column reference) to arbitrarily complex (e.g., nested function calls with multiple operators). They can appear in many different parts of SQL statements, including SELECT, WHERE, HAVING, and ORDER BY clauses.

Logical Operators

Logical operators are used within expressions to combine or modify boolean predicates (conditions).

The following logical operators are available: AND, OR, XOR, and NOT.

The operators AND, OR, and XOR are commutative, meaning you can switch the left and right operands without changing the result.

Comparison Operators

Comparison operators are used within expressions to compare values. Common use cases include comparing a field from the dataset against a literal value, though comparisons can also be between two fields or two literal values.

When one of the values in a comparison is null, the result is typically null (following SQL's three-valued logic).

Other Comparisons

BETWEEN

The BETWEEN operator provides a convenient way to test if a value falls within a range.

Warning

Using BETWEEN with other predicates in complex expressions, especially when combined with additional AND conjunctions, can sometimes cause the query parser to fail. Consider using explicit comparison operators for complex conditions.

CASE

The CASE expression provides conditional logic within SQL queries and comes in two forms.

The simple form searches each value expression from top to bottom until it finds one that equals the input expression:

CASE expression
    WHEN value THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

The result for the matching value is returned. If no match is found, the result from the ELSE clause is returned if present; otherwise null is returned.

Example:

SELECT name, 
       CASE numberOfMoons 
            WHEN 0 THEN 'none' 
            WHEN 1 THEN 'one' 
            ELSE 'lots' 
       END as how_many_moons
  FROM $planets;

The searched form evaluates each boolean condition from top to bottom until one evaluates to true, then returns the corresponding result:

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

If no conditions are true, the result from the ELSE clause is returned if present; otherwise null is returned.

Example:

SELECT name, 
       CASE
           WHEN numberOfMoons = 0 THEN 'none' 
           WHEN numberOfMoons = 1 THEN 'one' 
           ELSE 'lots' 
       END as how_many_moons
  FROM $planets;

IS DISTINCT FROM

IS DISTINCT FROM compares two values without SQL's three-valued logic. It is total — it always returns true or false, never null, which is the reason to reach for it over <>.

SELECT * FROM $planets WHERE name IS DISTINCT FROM 'Earth';

IN

IN tests membership. The right-hand side may be a constant list, a subquery, or an array expression.

-- a constant list
SELECT * FROM $planets WHERE id IN (1, 2, 3);

-- a subquery
SELECT * FROM $planets WHERE id IN (SELECT id FROM $planets WHERE numberOfMoons > 0);

-- the elements of an array
SELECT * FROM $planets WHERE name IN UNNEST(['Earth', 'Mars']);

Each form negates with NOT IN.

Every element of a constant list must be a constant and they must all share one type. Arithmetic is allowed and folded before the query runs — d_year IN (1999, 1999 + 1) is accepted — but an element that does not reduce to a constant is rejected.

Be Aware

NOT IN UNNEST(...) is not currently executable. It lowers to a universally-quantified comparison, which has no kernel; see Quantified Comparisons below.

EXISTS

EXISTS tests whether a subquery returns any row at all, and negates with NOT EXISTS. It may be used as a predicate or projected as a value.

SELECT name
  FROM $planets AS p
 WHERE EXISTS (SELECT 1 FROM $planets AS q WHERE q.id = p.id AND q.numberOfMoons > 0);

The subquery is usually correlated, as above. Opteryx decorrelates it during planning rather than evaluating it once per row.

Quantified Comparisons

A comparison may be quantified over an array with ANY, which holds when the comparison is true for at least one element.

SELECT * FROM $planets WHERE id = ANY(ARRAY[1, 2]);
SELECT * FROM $planets WHERE name LIKE ANY ('E%', 'M%');

The pattern operators (LIKE, ILIKE, RLIKE) take the quantifier too. The patterns must be bracketed.

Warning

ALL — = ALL(...), <> ALL(...), > ALL(...) — parses but does not execute. No universally-quantified comparison has an implementation, so a query using one fails at execution rather than at planning. NOT IN UNNEST(...) lowers to this form and is affected too.

MATCH ... AGAINST

MATCH scores a column against a query string by embedding cosine similarity — it is not MySQL-style full-text search.

SELECT * FROM $planets WHERE MATCH (name) AGAINST ('Earth');

Exactly one column may be matched at a time. The threshold is set with SET match_threshold. MySQL's search modifiers (IN NATURAL LANGUAGE MODE and friends) are rejected rather than accepted and ignored.

Literals

Array literals

Both the bracket form ['a', 'b'] and the parenthesised form (1, 2) build an array, and every element must share one type.

An array literal is an operand, not a projectable value: it can appear on the right of IN UNNEST, = ANY or the array containment operators, but SELECT ['a', 'b'] is rejected.

INTERVAL

INTERVAL is the one type-prefixed string literal the dialect accepts. The value must be quoted, and a unit is required.

SELECT NOW() - INTERVAL '1' DAY;
SELECT INTERVAL '1 3' YEAR TO MONTH;

Units are YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. The compound form spans a contiguous run of them starting at the leading unit.

Intervals are carried as a months-and-microseconds pair — the two components that cannot be converted into one another without a calendar.

Subscripts

An array element is read positionally with a subscript. The subscript must be an integer literal.

SELECT name[0] FROM $planets;

Struct fields are read with the arrow operators (->, ->>) instead, not with a subscript.

Type Casting

Opteryx supports two equivalent syntaxes for casting values between types.

CAST function

CAST(value AS type)

Double-colon shorthand

value::type

Both forms are interchangeable. The :: shorthand is more concise and common in practice.

TRY_CAST

TRY_CAST (and its alias SAFE_CAST) is the non-raising form: where CAST raises on a value it cannot convert, TRY_CAST yields null for that value.

SELECT TRY_CAST('not a number' AS INTEGER);  -- null
SELECT CAST('not a number' AS INTEGER);      -- error

There is no :: shorthand for the try form.

Temporal types

When casting to temporal types, a precision unit is required for TIMESTAMP:

TIMESTAMP without a unit is not supported.

String literals are not implicitly cast to temporal types

Comparing a temporal column against an uncast string literal will raise an IncompatibleTypesError. An explicit cast is always required:

-- Correct
SELECT * FROM missions WHERE launched_at >= '1957-10-04'::DATE;
SELECT * FROM missions WHERE launched_at >= '1957-10-04'::TIMESTAMP[ms];

-- Error: IncompatibleTypesError
SELECT * FROM missions WHERE launched_at >= '1957-10-04';