Functions
This document describes the supported SQL functions and operators.
Generally functions will return null
on null
input, although note that this is not true in all circumstances, especially for null-aware functions like COALESCE
and IFNULL
.
Definitions noted with a accept different input arguments.
New functions for this version are annotated with the icon.
Conversion Functions
BOOLEAN
any: any → boolean
Cast any to a BOOLEAN
, raises an error if cast is not possible. Note BOOLEAN
does not require parenthesis, however any aliases do.
Alias for CAST
(any AS BOOLEAN)
BLOB
(varchar: varchar) → blob
Cast varchar to blob, raises an error if cast is not possible.
Note: prefixing can also be used to define a literal blob string, b'value'
is equivalent to blob('value')
.
CAST
(any: any AS type) → type
Cast any to type, raises an error if cast is not possible.
Also implemented as individual cast functions (BOOLEAN
, INTEGER
, FLOAT
, VARCHAR
).
INT
(num: numeric) → numeric
Alias for INTEGER
INTEGER
(num: numeric) → numeric
Convert num to an integer.
INTEGER
is a psuedo-type, CAST
is not supported and values may be coerced to NUMERIC
.
FLOAT
(num: numeric) → numeric
Convert num to a floating point number.
FLOAT
is a psuedo-type, CAST
is not supported and values may be coerced to NUMERIC
.
NUMERIC
any: any → numeric
Cast any to a floating point number, raises an error if cast is not possible. Note NUMERIC
does not require parenthesis, however any aliases do.
Alias for CAST
(any AS NUMERIC)
SAFE_CAST
(any: any AS type) → type
Alias for TRY_CAST
(any AS type)
STR
(any: any) → varchar
Alias of VARCHAR
(any) and CAST
(any AS VARCHAR)
STRING
(any: any) → varchar
Alias of VARCHAR
(any) and CAST
(any AS VARCHAR)
STRUCT
(any: any) → struct
Cast any to a struct / dictionary.
Alias of CAST
(any AS STRUCT)
TIMESTAMP
iso8601: varchar → timestamp
Cast an ISO 8601 format string to a timestamp, raises an error if cast is not possible. Note TIMESTAMP
does not require parenthesis, however any aliases do.
Alias for CAST
(iso8601 AS TIMESTAMP)
TRY_CAST
(any: any AS type) → type
Cast any to type, if cast is not possible, returns null
.
VARCHAR
(any) → varchar
Cast any to a string, raises an error if cast is not possible.
Alias for CAST
(any AS VARCHAR)
Date & Time Functions
For more details, see Working with Timestamps.
current_date
→ timestamp
Return the current date, in UTC. Note current_date
does not require parenthesis.
current_time
→ timestamp
Return the current date and time, in UTC. Note current_time
does not require parenthesis.
DATE
(ts: timestamp) → timestamp
Remove any time information, leaving just the date part of ts.
DATE_FORMAT
(ts: timestamp, format: varchar) → varchar
Formats ts as a string using format.
DATEPART
(unit: varchar, ts: timestamp) → numeric
Alias of EXTRACT
(unit FROM ts)
DATE_TRUNC
(unit: varchar, ts: timestamp) → varchar
Returns ts truncated to unit.
DATEDIFF
(unit: varchar, start: timestamp, end: timestamp) → numeric
Calculate the difference between the start and end timestamps in a given unit.
DAY
(timestamp) → numeric
Extract day number from a timestamp. See EXTRACT
.
EXTRACT
(unit FROM timestamp) → numeric
Extract unit of a timestamp.
- NANOSECOND
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- TIME
- DATE
- DAY
- DAYOFWEEK
/ DOW
- WEEK
- ISOWEEK
- MONTH
- QUARTER
- DAYOFYEAR
/ DOY
- YEAR
- ISOYEAR
- DECADE
- CENTURY
- EPOCH
- JULIAN
FROM_UNIXTIME
(timestamp: numeric) → timestamp
Return a timestamp representation of an Unix Timestamp.
Related: UNIXTIME
NOW
() → timestamp
Alias for current_time
TIME
() → timestamp
Returns the current iime (UTC).
TIME_BUCKET
(timestamp, multiple: numeric, unit: varchar) → timestamp
Floor timestamps into fixed time interval buckets. unit is optional and will be day
if not provided.
TODAY
() → timestamp
Alias for current_date
HOUR
(ts: timestamp) → numeric
Returns the hour of the day from ts. The value ranges from 0
to 23
.
Alias for EXTRACT
(hour FROM ts)
MINUTE
(ts: timestamp) → numeric
Returns the minute of the hour from ts. The value ranges from 0
to 59
.
Alias for EXTRACT
(minute FROM ts)
MONTH
(ts: timestamp) → numeric
Returns the month of the year from ts. The value ranges from 1
to 12
.
Alias for EXTRACT
(month FROM ts)
QUARTER
(ts: timestamp) → numeric
Returns the quarter of the year from ts. The value ranges from 1
to 4
.
Alias for EXTRACT
(quarter FROM ts)
SECOND
(ts: timestamp) → numeric
Returns the second of the minute from ts. The value ranges from 0
to 59
.
Alias for EXTRACT
(second FROM ts)
TIMEDIFF
(unit: varchar, start: timestamp) → numeric
Calculate the difference between the start and end times.
UNIXTIME
() → numeric
Return the current time as a Unix Timestamp.
Related: FROM_UNIXTIME
, current_time
UNIXTIME
(timestamp: timestamp) → numeric
Return timestamp in Unix Timestamp representation.
Related: FROM_UNIXTIME
UTC_TIMESTAMP
() → timestamp
Alias for current_time
WEEK
(ts: timestamp) → numeric
Returns the week of the year from ts. The value ranges from 1
to 53
.
Alias for EXTRACT
(week FROM ts)
YEAR
(ts: timestamp) → numeric
Returns the year from ts.
Alias for EXTRACT
(year FROM ts)
Infix Functions
These are functions that are called similar to comparison operators:
numeric +
numeric → numeric
Numeric addition
timestamp +
interval → timestamp
Timestamp and Interval addition
numeric -
numeric → numeric
Numeric subtraction
timestamp -
interval → timestamp
Timestamp and Interval subtraction
timestamp -
timestamp → interval
Timestamp subtraction
numeric *
numeric → numeric
Numeric multiplication
numeric /
numeric → numeric
Numeric division
numeric DIV
numeric → integer
Integer division
numeric %
numeric → numeric
Numeric modulo (remainder)
varchar ||
varchar → varchar
String concatenation
inet |
inet → bool
IP address containment
integer |
integer → integer
Bitwise OR
integer &
integer → integer
Bitwise AND
integer ^
integer → integer
Bitwise XOR
Array Functions
For more details, see Working with Arrays.
array: array[
index: numeric]
→ value
Return the indexth element from array.
array: array @>
values: array → boolean
Return true
if array contains any of the elements in values.
Related: ARRAY_CONTAINS_ANY
ARRAY_CONTAINS
(array: array, value) → boolean
Return true
if array contains value.
See also SEARCH
(array, value)
See also ANY
ARRAY_CONTAINS_ANY
(array: array, values: array) → boolean
Return true
if array contains any of the elements in values.
ARRAY_CONTAINS_ALL
(array: array, values: array) → boolean
Return true
if array contains all of elements in values.
GET
(array: array, index: numeric) → value
Alias of array[
index]
GREATEST
(array: array) → value
Return the greatest value in array.
Related: LEAST
LEAST
(array: array) → value
Return the smallest value in array.
Related: GREATEST
LEN
(array: array) → numeric
Alias of LENGTH
(array)
LENGTH
(array: array) → numeric
Returns the number of elements in array.
LIST_CONTAINS
(array: array, value) → boolean
DEPRECATED
Alias of ARRAY_CONTAINS
LIST_CONTAINS_ANY
(array: array, values: array) → boolean
DEPRECATED
Alias of ARRAY_CONTAINS_ANY
Related: @>
LIST_CONTAINS_ALL
(array: array, values: array) → boolean
DEPRECATED
Alias of ARRAY_CONTAINS_ALL
SEARCH
(array: array, value) → boolean
Return true
if array contains value.
SORT
(array: array) → array
Return array in ascending order.
Numeric Functions
ABS
(x: numeric) → numeric
Alias of ABSOLUTE
ABSOLUTE
(x: numeric) → numeric
Returns the absolute value of x.
CEIL
(x: numeric, scale: integer) → double
Returns the nearest equal or larger whole number to x, or to the nearest equal or larger double with scale places after the decimal point.
Related: FLOOR
CEILING
(x: numeric, scale: integer) → double
DEPRECATED
E
() → numeric
Returns the constant e, also known as Euler's number.
Related: LN
.
FLOOR
(x: numeric) → numeric
Returns the nearest equal or lesser whole number to x, or to the nearest equal or lesser double with scale places after the decimal point.
Related: CEIL
PHI
() → numeric
Returns the constant φ (phi), also known as the golden ratio.
PI
() → numeric
Returns the constant π (pi).
POWER
(base: numeric, exponent: numeric**) → _numeric
Returns base to the power of exponent.
LN
(x: numeric) → numeric
Returns the natural logarithm of x.
Related: E
, LOG
, LOG10
, LOG2
LOG
(x: numeric, base: numeric) → numeric
Returns the logarithm of x for base base.
Related: LN
, LOG10
, LOG2
LOG10
(x: numeric) → numeric
Returns the logarithm for base 10 of x.
Related: LN
, LOG
, LOG2
LOG2
(x: numeric) → numeric
Returns the logarithm for base 2 of x.
Related: LN
, LOG
, LOG10
ROUND
(x: numeric) → numeric
Returns x rounded to the nearest integer.
ROUND
(x: numeric, places: numeric) → numeric
Returns x rounded to places decimal places.
SIGN
(x: numeric) → numeric
Returns the signum function of x; 0 if x is 0, -1 if x is less than 0 and 1 if x is greater than 0.
SIGNUM
(x: numeric) → numeric
Alias for SIGN
SQRT
(x: numeric) → numeric
Returns the square root of x.
TRUNC
(x: numeric) → numeric
Alias of TRUNCATE
TRUNCATE
(x: numeric) → numeric
Returns x rounded to integer by dropping digits after decimal point.
String Functions
Functions for examining and manipulating string values.
str: varchar[
index: numeric]
→ varchar
Subscript operator, return the indexth character from str.
ASCII
(string: char) → integer
Returns the ASCII code for a given character.
Related: CHAR
CHAR
(code: integer) → char
Returns the character for a given ASCII code.
Related: ASCII
CONCAT
(list: array<varchar>) → varchar
Returns the result of concatenating, or joining, of two or more string values in an end-to-end manner.
Related: CONCAT_WS
CONCAT_WS
(separator: varchar, list: array<varchar>) → varchar
Returns the result of concatenating, or joining, of two or more string values with a separator used to delimit individual values.
Related: CONCAT
ENDS_WITH
(str: varchar, value: varchar) → boolean
Return true
if str ends with value.
Related: STARTS_WITH
GET
(str: varchar, index: numeric) → varchar
Alias of str[
index]
LEFT
(str: varchar, n: numeric) → varchar
Extract the left-most n characters of str.
Related: RIGHT
LEN
(str: varchar) → numeric
Alias of LENGTH
LENGTH
(str: varchar) → numeric
Returns the length of str in characters.
LEVENSHTEIN
(str1: varchar, str2: varchar) → numeric
Returns the Levenshtein Distance between str1 and str2
LOWER
(str: varchar) → varchar
Converts str to lowercase.
Related: UPPER
, TITLE
LPAD
(string: varchar, width: integer, fill: char) → varchar
Returns a string at least width characters wide, with fill used to pad the string, to the left, to fill to the required width.
Related: RPAD
LTRIM
(str: varchar) → varchar
Remove leading whitespace from str.
Related: RTRIM
, TRIM
MATCH
(column: varchar) AGAINST
(query: varchar) → boolean
Perform a fulltext search of column for the values in query.
Note: Values in $stop_words
are ignored.
POSITION
(substring: varchar IN string: varchar) → numeric
Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.
REGEXP_REPLACE
(str: varchar, pattern: varchar, replace: varchar) → varchar
Performs a replace based on regular expressions.
REVERSE
(str: varchar) → varchar
Returns str with the characters in reverse order.
RIGHT
(str: varchar, n: numeric) → varchar
Extract the right-most n characters of str.
Related: LEFT
RPAD
(string: varchar, width: integer, fill: char) → varchar
Returns a string at least width characters wide, with fill used to pad the string, to the right, to fill to the required width.
Related: LPAD
RTRIM
(str: varchar) → varchar
Remove trailing whitespace from str.
Related: LTRIM
, TRIM
SOUNDEX
(str: varchar) → varchar
Returns a character string containing the phonetic representation of char. See Soundex 🡕.
SEARCH
(str: varchar, substring: varchar) → boolean
Return true
if str contains substring.
SPLIT
(str: varchar) → array
Splits str on commas (,
) and returns an array.
SPLIT
(str: varchar, delimiter: varchar) → array
Splits str on delimiter and returns an array.
SPLIT
(str: varchar, delimiter: varchar, limit: integer) → array
Splits str on delimiter and returns an array of size at most limit. The last element in the array contains the remaining part of the string. limit must be greater than zero.
SUBSTRING
(str: varchar, start: numeric) → varchar
Return substring from a string from start position to the end of str.
SUBSTRING
(str: varchar, start: numeric, length: numeric) → varchar
Return substring from a string from start position for length characters.
STARTS_WITH
(str: varchar, value: varchar) → boolean
Return true
if str starts with value.
Related: ENDS_WITH
TITLE
(str: varchar) → varchar
Returns str with the first letter of each work in upper case.
Related: LOWER
, UPPER
TRIM
( [ LEADING | TRAILING | BOTH ] [ chars: varchar FROM ] str: varchar ) → varchar
Removes leading and trailing chars from str, if chars is not specified, whitespace is removed. Note that any instance of a character in chars is removed in any order they appear.
The LEADING modifier removes chars from the start of str.
The TRAILING modifier removes chars from the end of str.
The BOTH modifier removes chars from both the start and end of str, this is the default behaviour if no positional modifier is supplied.
Related: LTRIM
, RTRIM
UPPER
(str: varchar) → varchar
Converts str to uppercase.
Related: LOWER
, TITLE
Struct Functions
For more details, see Working with Structs.
struct ->
key → value
Return the value for key from object.
Struct values can be VARCHAR
or BLOB
formatted JSON strings.
struct ->>
key → varchar
Return the value for key from object, non NULL
values are cast to VARCHAR
.
Struct values can be VARCHAR
or BLOB
formatted JSON strings.
Related: ->
operator
struct @?
key → value
Return true if struct contains the key key.
Struct values can be VARCHAR
or BLOB
formatted JSON strings.
struct @?
jsonpath → value
New in 0.19
Return true if struct contains a value at jsonpath.
Struct values can be VARCHAR
or BLOB
formatted JSON strings.
object: struct[
key: varchar]
→ value
Subscript operator
Alias of object->
key
GET
(object: struct, key: varchar) → value
Alias of object->
key
JSONB_OBJECT_KEYS
(object: struct) → array
Returnan array of the keys in a struct value.
Struct values can be STRUCT
values, or VARCHAR
or BLOB
formatted JSON strings.
SEARCH
(object: struct, value: varchar) → boolean
Return true
if any of the values in object is value. Note SEARCH
does not match struct keys.
System Functions
VERSION
() → varchar
Return the version of the query engine.
Other Functions
BASE64_DECODE
(str: varchar) → varchar
Decode BASE64 encoded value, str.
Related: BASE64_ENCODE
BASE64_ENCODE
(str: varchar) → varchar
Encode str with BASE64 encoding.
Related: BASE64_DECODE
BASE85_DECODE
(str: varchar) → varchar
Decode BASE85 encoded value, str.
Related: BASE85_ENCODE
BASE85_ENCODE
(str: varchar) → varchar
Encode str with BASE85 encoding.
Related: BASE85_DECODE
COALESCE
(arg1, arg2, ...) → value
Return the first item from args which is not null
.
Related: IFNULL
COSINE_SIMILARITY
(str: varchar, value: varchar) → double
Perform an ad hoc cosine similarity comparison between str and value.
Note: Values in $stop_words
are ignored.
GENERATE_SERIES
(start: numeric, stop: numeric) → array<numeric>
Return a numeric list between start and stop, with a step of 1.
GENERATE_SERIES
(start: numeric, stop: numeric, step: numeric) → array<numeric>
Return a numeric list between start and stop, with an increment of step.
GENERATE_SERIES
(start: timestamp, stop: timestamp, interval) → array<timestamp>
Return a timestamp list between start and stop, with a interval of step.
HASH
(value: any) → varchar
Calculate the CityHash (64 bit) of value.
HEX_DECODE
(str: varchar) → varchar
Decode hexidecimal (BASE16) encoded value, str.
Related: HEX_ENCODE
HEX_ENCODE
(str: varchar) → varchar
Encode str with hexadecimal (BASE16) encoding.
Related: HEX_DECODE
IIF
(condition, true_value, false_value) → input type
Return the true_value if the condition evaluates to True
, otherwise return the false_value.
IFNOTNULL
(check_expression: any, replacement_value: any) → input type
New in 0.19
Returns check_expression if not null
, otherwise returns replacement_value.
Related: IFNULL
IFNULL
(check_expression: any, replacement_value: any) → input type
Returns check_expression if null
, otherwise returns replacement_value.
Related: COALESCE
, IFNOTNULL
NORMAL
() → numeric
Random number from a normal (Gaussian) distribution; distribution is centred at 0.0 and has a standard deviation of 1.0. Per record.
NULLIF
(value1: any, value2: any) → input type
Returns null
if value1 equals value2, otherwise returns value1.
MD5
(str: varchar) → varchar
Calculate the MD5 hash of str.
RAND
() → numeric
Returns a random number between 0 and 1. Per record.
RANDOM
() → numeric
Alias of RAND
RANDOM_STRING
(length: numeric) → varchar
Returns a random string of lowercase alphabetic characters with a length of length. Per record.
SHA1
(str: varchar) → varchar
Calculate the SHA1 hash of str.
Related: SHA224
, SHA256
, SHA384
, SHA512
SHA224
(str: varchar) → varchar
Calculate the SHA224 hash of str.
Related: SHA1
, SHA256
, SHA384
, SHA512
SHA256
(str: varchar) → varchar
Calculate the SHA256 hash of str.
Related: SHA1
, SHA224
, SHA384
, SHA512
SHA384
(str: varchar) → varchar
Calculate the SHA384 hash of stre.
Related: SHA1
, SHA224
, SHA256
, SHA512
SHA512
(str: varchar) → varchar
Calculate the SHA512 hash of str.
Related: SHA1
, SHA224
, SHA256
, SHA384
UNNEST
(array: array) → relation
Create a virtual relation with a row for each element in array.