Operators and Literals#

Numerical Operators#

+ (plus)
- (minus)
* (multiply)
/ (divide)
% (modulo)

`+`#

Addition

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

`-`#

Subtraction

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

`*`#

Multiplication

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

`/`#

Division (integer division truncates toward zero)

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

`%`#

Modulo (remainder)

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

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)

`=`#

Equal

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

`!=`#

Not Equal

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

`<`#

Less Than

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

`<=`#

Less Than or Equal To

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

`>`#

Greater Than

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

`>=`#

Greater Than or Equal To

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

`<=>`#

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

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

> 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 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`IS DISTINCT FROM`#

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

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

`IS NOT DISTINCT FROM`#

The negation of IS DISTINCT FROM

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

`~`#

Regex Match

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

`~*`#

Regex Case-Insensitive Match

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

`!~`#

Not Regex Match

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

`!~*`#

Not Regex Case-Insensitive Match

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

`~~`#

Like Match

SELECT 'datafusion' ~~ 'dat_f%n';
+---------------------------------------+
| Utf8("datafusion") ~~ Utf8("dat_f%n") |
+---------------------------------------+
| true                                  |
+---------------------------------------+

`~~*`#

Case-Insensitive Like Match

SELECT 'datafusion' ~~* 'Dat_F%n';
+----------------------------------------+
| Utf8("datafusion") ~~* Utf8("Dat_F%n") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`!~~`#

Not Like Match

SELECT 'datafusion' !~~ 'Dat_F%n';
+----------------------------------------+
| Utf8("datafusion") !~~ Utf8("Dat_F%n") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`!~~*`#

Not Case-Insensitive Like Match

SELECT 'datafusion' !~~* 'Dat%F_n';
+-----------------------------------------+
| Utf8("datafusion") !~~* Utf8("Dat%F_n") |
+-----------------------------------------+
| true                                    |
+-----------------------------------------+

Logical Operators#

`AND`#

Logical And

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

`OR`#

Logical Or

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

Bitwise Operators#

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

`&`#

Bitwise And

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

`|`#

Bitwise Or

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

`^`#

Bitwise Xor

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

`>>`#

Bitwise Shift Right

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

`<<`#

Bitwise Shift Left

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

Other Operators#

|| (string concatenation)
@> (array contains)
<@ (array is contained by)
AT TIME ZONE

`||`#

String Concatenation

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

`@>`#

Array Contains

> 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                                                                    |
+-------------------------------------------------------------------------+

`<@`#

Array Is Contained By

> 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                                                                    |
+-------------------------------------------------------------------------+

`AT TIME ZONE`#

Converts between timezone-naive and timezone-aware timestamps, following PostgreSQL. AT TIME ZONE always returns the other kind of timestamp from the one it is given:

Input type	Result type	Meaning
`Timestamp(unit, None)` (timezone-naive)	`Timestamp(unit, Some(tz))` (tz-aware)	Read the value as a wall clock in `tz`; the result is that instant.
`Timestamp(unit, Some(_))` (tz-aware)	`Timestamp(unit, None)` (timezone-naive)	Return the wall clock that instant has in `tz`.

A timezone-naive timestamp is a wall clock with no instant attached, so AT TIME ZONE pins it to one:

> SET datafusion.execution.time_zone = 'UTC';
> SELECT
  '2024-01-01 12:00:00'::timestamp AT TIME ZONE 'America/Denver' AS instant,
  arrow_typeof('2024-01-01 12:00:00'::timestamp AT TIME ZONE 'America/Denver') AS type;
+---------------------------+---------------------------------+
| instant                   | type                            |
+---------------------------+---------------------------------+
| 2024-01-01T12:00:00-07:00 | Timestamp(ns, "America/Denver") |
+---------------------------+---------------------------------+

A timezone-aware timestamp already is an instant, so AT TIME ZONE reads off its wall clock in tz and drops the timezone. Daylight saving time is taken into account:

> SELECT
  '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver' AS wall_clock,
  arrow_typeof('2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver') AS type;
+---------------------+---------------+
| wall_clock          | type          |
+---------------------+---------------+
| 2024-01-01T05:00:00 | Timestamp(ns) |
+---------------------+---------------+

expression AT TIME ZONE tz is the same as the function call timezone(tz, expression), as in PostgreSQL and DuckDB. The planner uses the input type after type coercion. For example, CASE WHEN c THEN naive ELSE aware END has a timezone-aware type, so AT TIME ZONE returns a timezone-naive timestamp for it, whatever the order of the arms.

Because the two forms return different types, applying AT TIME ZONE twice returns a timezone-aware timestamp again: the wall clock produced by the first application is re-read as a local time in the second timezone.

AT TIME ZONE never changes the precision (TimeUnit) of its input. A dictionary-encoded timestamp follows its value type. Inputs that are not timestamps at all (a string literal, for example) are cast to Timestamp(Nanosecond, Some(tz)).

Note that a timezone written as a fixed offset string follows the ISO 8601 convention, so '+05:30' is 5 hours 30 minutes east of UTC. PostgreSQL applies the opposite (POSIX) convention to offsets spelled this way.

Literals#

Use single quotes for literal values. For example, the string foo bar is referred to using 'foo bar'

select 'foo';

Escaping#

Unlike many other languages, SQL literals do not by default support C-style escape sequences such as \n for newline. Instead all characters in a ' string are treated literally.

To escape ' in SQL literals, use '':

> select 'it''s escaped';
+----------------------+
| Utf8("it's escaped") |
+----------------------+
| it's escaped         |
+----------------------+
1 row(s) fetched.

Strings such as foo\nbar mean \ followed by n (not newline):

> select 'foo\nbar';
+------------------+
| Utf8("foo\nbar") |
+------------------+
| foo\nbar         |
+------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.

To add escaped characters such as newline or tab, instead of \n you use the E style strings. For example, to add the text with a newline

foo
bar

You can use E'foo\nbar'

> select E'foo\nbar';
+-----------------+
| Utf8("foo
bar") |
+-----------------+
| foo
bar         |
+-----------------+