spiceai/docs

spiceai/

docs

Help Login

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.10.x/reference/sql/operators.md

spiceai/docs | Spice Cloud Platform

spiceai/docs/README.md

title: 'Operators' sidebar_label: 'Operators' sidebar_position: 3

:::info Spice is built on Apache DataFusion and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.
:::

`+` {#op_plus}

Addition

`-` {#op_minus}

Subtraction

`*` {#op_multiply}

Multiplication

`/` {#op_divide}

Division (integer division truncates toward zero)

`%` {#op_modulo}

Modulo (remainder)

Comparison Operators

= (equal)
!= (not equal)
< (less than)
<= (less than or equal to)
> (greater than)
>= (greater than or equal to)
<=> (three-way comparison, alias for IS NOT DISTINCT FROM)
IS DISTINCT FROM
IS NOT DISTINCT FROM
~ (regex match)
~* (regex case-insensitive match)
!~ (not regex match)
!~* (not regex case-insensitive match)

`=` {#op_eq}

Equal

`!=` {#op_neq}

Not Equal

`<` {#op_lt}

Less Than

`<=` {#op_le}

Less Than or Equal To

`>` {#op_gt}

Greater Than

`>=` {#op_ge}

Greater Than or Equal To

`<=>` {#op_spaceship}

Three-way comparison operator. A NULL-safe operator that returns true if both operands are equal or both are NULL, false otherwise.

`IS DISTINCT FROM`

Guarantees the result of a comparison is true or false and not an empty set

`IS NOT DISTINCT FROM`

The negation of IS DISTINCT FROM

`~` {#op_re_match}

Regex Match

`~*` {#op_re_match_i}

Regex Case-Insensitive Match

`!~` {#op_re_not_match}

Not Regex Match

`!~*` {#op_re_not_match_i}

Not Regex Case-Insensitive Match

`~~`

Like Match

`~~*`

Case-Insensitive Like Match

`!~~`

Not Like Match

`!~~*`

Not Case-Insensitive Like Match

Logical Operators

`AND`

Logical And

`OR`

Logical Or

Bitwise Operators

& (bitwise and)
| (bitwise or)
# (bitwise xor)
>> (bitwise shift right)
<< (bitwise shift left)

`&` {#op_bit_and}

Bitwise And

`|` {#op_bit_or}

Bitwise Or

`#` {#op_bit_xor}

Bitwise Xor (interchangeable with ^)

`>>` {#op_shift_r}

Bitwise Shift Right

`<<` {#op_shift_l}

Bitwise Shift Left

Other Operators

|| (string concatenation)
`@>` (array contains)
<@ (array is contained by)

`||` {#op_str_cat}

String Concatenation

`@>` {#op_arr_contains}

Array Contains

`<@` {#op_arr_contained_by}

Array Is Contained By

> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3                   |
+---------------------+

> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3                   |
+---------------------+

> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6                   |
+---------------------+

> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6                   |
+---------------------+

> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2                   |
+---------------------+

> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2                   |
+---------------------+

> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true                |
+---------------------+

> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true                |
+---------------------+

> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true                 |
+----------------------+

> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true                 |
+----------------------+

> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true                |
+---------------------+

> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true                |
+---------------------+

> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true                 |
+----------------------+

> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true                 |
+----------------------+

> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true                |
+---------------------+

> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true                |
+---------------------+

> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true                 |
+----------------------+

> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true                 |
+----------------------+

> SELECT NULL <=> NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT NULL <=> NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT 1 <=> NULL;
+------------------------------------+
| Int64(1) IS NOT DISTINCT FROM NULL |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 1 <=> NULL;
+------------------------------------+
| Int64(1) IS NOT DISTINCT FROM NULL |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 1 <=> 2;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(2) |
+----------------------------------------+
| false                                  |
+----------------------------------------+

> SELECT 1 <=> 2;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(2) |
+----------------------------------------+
| false                                  |
+----------------------------------------+

> SELECT 1 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

> SELECT 1 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT 'foo' ~ '^foo(-cli)*';
+-----------------------------------+
| Utf8("foo") ~ Utf8("^foo(-cli)*") |
+-----------------------------------+
| true                              |
+-----------------------------------+

> SELECT 'foo' ~ '^foo(-cli)*';
+-----------------------------------+
| Utf8("foo") ~ Utf8("^foo(-cli)*") |
+-----------------------------------+
| true                              |
+-----------------------------------+

> SELECT 'foo' ~* '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") ~* Utf8("^foo(-cli)*") |
+------------------------------------+
| true                               |
+------------------------------------+

> SELECT 'foo' ~* '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") ~* Utf8("^foo(-cli)*") |
+------------------------------------+
| true                               |
+------------------------------------+

> SELECT 'foo' !~ '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") !~ Utf8("^foo(-cli)*") |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 'foo' !~ '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") !~ Utf8("^foo(-cli)*") |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 'foo' !~* '^FOO(-cli)+';
+-------------------------------------+
| Utf8("foo") !~* Utf8("^FOO(-cli)+") |
+-------------------------------------+
| true                                |
+-------------------------------------+

> SELECT 'foo' !~* '^FOO(-cli)+';
+-------------------------------------+
| Utf8("foo") !~* Utf8("^FOO(-cli)+") |
+-------------------------------------+
| true                                |
+-------------------------------------+

SELECT 'foobar' ~~ 'f_o%r';
+---------------------------------+
| Utf8("foobar") ~~ Utf8("f_o%r") |
+---------------------------------+
| true                            |
+---------------------------------+

SELECT 'foobar' ~~ 'f_o%r';
+---------------------------------+
| Utf8("foobar") ~~ Utf8("f_o%r") |
+---------------------------------+
| true                            |
+---------------------------------+

SELECT 'foobar' ~~* 'F_o%r';
+----------------------------------+
| Utf8("foobar") ~~* Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

SELECT 'foobar' ~~* 'F_o%r';
+----------------------------------+
| Utf8("foobar") ~~* Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

SELECT 'foobar' !~~ 'F_o%r';
+----------------------------------+
| Utf8("foobar") !~~ Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

SELECT 'foobar' !~~ 'F_o%r';
+----------------------------------+
| Utf8("foobar") !~~ Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

SELECT 'foobar' !~~* 'F_o%Br';
+------------------------------------+
| Utf8("foobar") !~~* Utf8("F_o%Br") |
+------------------------------------+
| true                               |
+------------------------------------+

SELECT 'foobar' !~~* 'F_o%Br';
+------------------------------------+
| Utf8("foobar") !~~* Utf8("F_o%Br") |
+------------------------------------+
| true                               |
+------------------------------------+

> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1                   |
+---------------------+

> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7                   |
+---------------------+

> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7                   |
+---------------------+

> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6                   |
+---------------------+

> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6                   |
+---------------------+

> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0                    |
+----------------------+

> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0                    |
+----------------------+

> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40                   |
+----------------------+

> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40                   |
+----------------------+

> SELECT 'Hello, ' || 'Spice!';
+-----------------------------------+
| Utf8("Hello, ") || Utf8("Spice!") |
+-----------------------------------+
| Hello, Spice!                     |
+-----------------------------------+

> SELECT 'Hello, ' || 'Spice!';
+-----------------------------------+
| Utf8("Hello, ") || Utf8("Spice!") |
+-----------------------------------+
| Hello, Spice!                     |
+-----------------------------------+

> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

title: 'Operators' sidebar_label: 'Operators' sidebar_position: 3

Numerical Operators

+ {#op_plus}

- {#op_minus}

* {#op_multiply}

/ {#op_divide}

% {#op_modulo}

Comparison Operators

= {#op_eq}

!= {#op_neq}

< {#op_lt}

<= {#op_le}

> {#op_gt}

>= {#op_ge}

<=> {#op_spaceship}

IS DISTINCT FROM

IS NOT DISTINCT FROM

~ {#op_re_match}

~* {#op_re_match_i}

!~ {#op_re_not_match}

!~* {#op_re_not_match_i}

~~

~~*

!~~

!~~*

Logical Operators

AND

OR

Bitwise Operators

& {#op_bit_and}

| {#op_bit_or}

# {#op_bit_xor}

>> {#op_shift_r}

<< {#op_shift_l}

Other Operators

|| {#op_str_cat}

@> {#op_arr_contains}

<@ {#op_arr_contained_by}

title: 'Operators' sidebar_label: 'Operators' sidebar_position: 3

Numerical Operators

+ {#op_plus}

- {#op_minus}

* {#op_multiply}

/ {#op_divide}

% {#op_modulo}

Comparison Operators

= {#op_eq}

!= {#op_neq}

< {#op_lt}

<= {#op_le}

> {#op_gt}

>= {#op_ge}

<=> {#op_spaceship}

IS DISTINCT FROM

IS NOT DISTINCT FROM

~ {#op_re_match}

~* {#op_re_match_i}

!~ {#op_re_not_match}

!~* {#op_re_not_match_i}

~~

~~*

!~~

!~~*

Logical Operators

AND

OR

Bitwise Operators

& {#op_bit_and}

| {#op_bit_or}

# {#op_bit_xor}

>> {#op_shift_r}

<< {#op_shift_l}

Other Operators

|| {#op_str_cat}

@> {#op_arr_contains}

<@ {#op_arr_contained_by}

`+` {#op_plus}

`-` {#op_minus}

`*` {#op_multiply}

`/` {#op_divide}

`%` {#op_modulo}

`=` {#op_eq}

`!=` {#op_neq}

`<` {#op_lt}

`<=` {#op_le}

`>` {#op_gt}

`>=` {#op_ge}

`<=>` {#op_spaceship}

`IS DISTINCT FROM`

`IS NOT DISTINCT FROM`

`~` {#op_re_match}

`~*` {#op_re_match_i}

`!~` {#op_re_not_match}

`!~*` {#op_re_not_match_i}

`~~`

`~~*`

`!~~`

`!~~*`

`AND`

`OR`

`&` {#op_bit_and}

`|` {#op_bit_or}

`#` {#op_bit_xor}

`>>` {#op_shift_r}

`<<` {#op_shift_l}

`||` {#op_str_cat}

`@>` {#op_arr_contains}

`<@` {#op_arr_contained_by}

`+` {#op_plus}

`-` {#op_minus}

`*` {#op_multiply}

`/` {#op_divide}

`%` {#op_modulo}

`=` {#op_eq}

`!=` {#op_neq}

`<` {#op_lt}

`<=` {#op_le}

`>` {#op_gt}

`>=` {#op_ge}

`<=>` {#op_spaceship}

`IS DISTINCT FROM`

`IS NOT DISTINCT FROM`

`~` {#op_re_match}

`~*` {#op_re_match_i}

`!~` {#op_re_not_match}

`!~*` {#op_re_not_match_i}

`~~`

`~~*`

`!~~`

`!~~*`

`AND`

`OR`

`&` {#op_bit_and}

`|` {#op_bit_or}

`#` {#op_bit_xor}

`>>` {#op_shift_r}

`<<` {#op_shift_l}

`||` {#op_str_cat}

`@>` {#op_arr_contains}

`<@` {#op_arr_contained_by}