Operators

The following operators are supported by Opteryx. Click a name for details.

Binary

• Division /
• Subtraction -
• Modulo %
• Multiplication *
• Integer division DIV
• Addition +
• Concatenation ||

Bitwise

• Bitwise AND &
• Bitwise OR |
• Bitwise XOR ^
• Left shift <<
• Right shift >>

Comparison

• Array contains all @>>
• Array contains any @>
• JSON path exists @?
• Equals =
• Greater than >
• Greater than or equal >=
• Case-insensitive like ILIKE
• IP contained by <<=
• IP contains >>=
• In list IN
• Like LIKE
• Less than <
• Less than or equal <=
• Not equals !=
• Not case-insensitive like NOT ILIKE
• Not in list NOT IN
• Not like NOT LIKE
• Not regex like NOT RLIKE
• Regex like RLIKE

Extraction

• JSON extract ->
• JSON extract text ->>
• Subscript access []

Logical

• Logical AND AND
• Logical OR OR
• Logical XOR XOR

Operator precedence

When an expression is written without parentheses, operators higher in this list bind first. Operators on the same line bind equally and group left to right, so a - b + c is (a - b) + c. A prefix operator applies to everything that binds tighter than it: NOT a = b is NOT (a = b), and -a * b is (-a) * b. Parentheses always override.

Some of these differ from other SQL engines; where they do, the line says so.

1. @>> -> @> @? ->> — Opteryx's own choice, above :: and []: payload->>'id'::INTEGER casts the extracted value, and a->'b'[1] indexes the extracted value. Postgres binds :: tighter and casts the key. A cast on a containment operand regroups too: a @> ['x']::ARRAY<VARCHAR> is (a @> ['x'])::ARRAY<VARCHAR>.
2. [] ::
3. unary - unary +
4. / % * DIV || — || binds like *. Postgres puts || below + and -; the two only meet in an expression that mixes strings and numbers.
5. - +
6. &
7. ^ <<= >>= << >> — ^, << and >> share one tier, between & and |. MySQL binds ^ above * and the shifts above &, so a & b << c is (a & b) << c here and a & (b << c) in MySQL. The IPv4 containment operators <<= and >>= begin with the shift tokens and bind at this tier, not as comparisons.
8. |
9. = > >= IN < <= != <> NOT IN BETWEEN NOT BETWEEN
10. ILIKE LIKE NOT ILIKE NOT LIKE NOT RLIKE RLIKE SIMILAR TO NOT SIMILAR TO — Pattern matching binds LOOSER than comparison. Postgres has it tighter, so a = b LIKE c is (a = b) LIKE c here and a = (b LIKE c) there.
11. IS FALSE IS JSON ARRAY IS JSON OBJECT IS JSON SCALAR IS JSON IS NOT FALSE IS NOT JSON ARRAY IS NOT JSON OBJECT IS NOT JSON SCALAR IS NOT JSON IS NOT NULL IS NOT TRUE IS NULL IS TRUE IS DISTINCT FROM IS NOT DISTINCT FROM
12. unary NOT
13. AND
14. XOR
15. OR