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 (BLOB, BOOLEAN, INTEGER, FLOAT, VARCHAR).

FLOAT (num: numeric) → numeric

Convert num to a floating point number.
Alias for CAST(any AS FLOAT)

HUMANIZE (num: numeric) → varchar

New in 0.20
Convert large numbers to human-readable formats (e.g., 1000 becomes "1K", 1000000 becomes "1M").

INTEGER (num: numeric) → numeric

Convert num to an integer.
Alias for CAST(any AS INTEGER)

SAFE_CAST (any: any AS type) → type

Alias for TRY_CAST(any AS type)

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 → data

Return the current date, in UTC. Note current_date does not require parenthesis.

current_time → time

Return the current time, in UTC. Note current_time does not require parenthesis.

current_timestamp → timestamp

New in 0.24
Return the current date and time, in UTC. Note current_timestamp does not require parenthesis.

DATE (ts: timestamp) → timestamp

Extract the date portion from ts, removing any time information.

DATE_FORMAT (ts: timestamp, format: varchar) → varchar

Format ts as a string according to the specified format string. Common format codes include %Y (year), %m (month), %d (day), %H (hour), %M (minute), %S (second).

DATEPART(unit: varchar, ts: timestamp) → numeric

Alias of EXTRACT(unit FROM ts)

DATE_TRUNC (unit: varchar, ts: timestamp) → varchar

Truncate ts to the specified unit (e.g., 'day', 'month', 'year'), setting all less significant fields to their minimum values.

DATEDIFF (unit: varchar, start: timestamp, end: timestamp) → numeric

Calculate the difference between start and end timestamps measured in the specified unit (e.g., 'day', 'hour', 'second').

DAY (timestamp) → numeric

Extract the day of the month from a timestamp (value ranges from 1 to 31). See EXTRACT.

EXTRACT (unit FROM timestamp) → numeric

Extract unit of a timestamp.
- NANOSECOND
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- 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_timestamp

TIME_BUCKET (timestamp, multiple: numeric, unit: varchar) → timestamp

Round timestamps down into fixed time interval buckets. For example, TIME_BUCKET(timestamp, 5, 'minute') groups timestamps into 5-minute buckets. The unit parameter is optional and defaults to 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.
If no ts provided, returns the current hour.
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.
If no ts provided, returns the current minute.
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.
If no ts provided, returns the current month.
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.
If no ts provided, returns the current second.
Alias for EXTRACT(second FROM ts)

TIMEDIFF (unit: varchar, start: timestamp) → numeric

Calculate the difference between start and the current time, measured in the specified unit.

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.
If no ts provided, returns the current year.
Alias for EXTRACT(year FROM ts)

Infix Functions / Operations

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 (set overlap).
Related: ARRAY_CONTAINS_ANY

values: array @>> array: array → boolean

Return true if array contains all of the elements in values.
Related: ARRAY_CONTAINS_ALL

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. Related: @>

ARRAY_CONTAINS_ALL (array: array, values: array) → boolean

Return true if array contains all of elements in values. Related: @>>

GET (array: array, index: numeric) → value

Will be deprecated after version 0.28
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

LENGTH (array: array) → numeric

Returns the number of elements in array.

SEARCH (array: array, value) → boolean

Will be deprecated after version 0.28
Return true if array contains value.

SORT (array: array) → array

Return array in ascending order.

Numeric Functions

ABS (x: numeric) → numeric

Returns the absolute value of x.

CEIL (x: numeric, scale: integer) → double

Return the smallest integer greater than or equal to x, or when scale is provided, round up to scale decimal places.
Related: FLOOR

E () → numeric

Returns the constant e, also known as Euler's number.
Related: LN.

FLOOR (x: numeric) → numeric

Return the largest integer less than or equal to x, or when scale is provided, round down to scale decimal places.
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

Return base raised to the power of exponent (i.e., base^exponent).

LN (x: numeric) → numeric

Return the natural logarithm of x (logarithm to the base e).
Related: E, LOG, LOG10, LOG2

LOG (x: numeric, base: numeric) → numeric

Return the logarithm of x using base as the logarithm base.
Related: LN, LOG10, LOG2

LOG10 (x: numeric) → numeric

Return the base-10 logarithm of x.
Related: LN, LOG, LOG2

LOG2 (x: numeric) → numeric

Return the base-2 logarithm 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

Return the sign of x: returns 0 if x is 0, -1 if x is negative, and 1 if x is positive.

SIGNUM (x: numeric) → numeric

Alias for SIGN

SQRT (x: numeric) → numeric

Return the square root of x.

TRUNC (x: numeric) → numeric

Return x truncated to an integer by removing digits after the 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

Concatenate all string values in list into a single string.
Related: CONCAT_WS

CONCAT_WS (separator: varchar, list: array<varchar>) → varchar

Concatenate all string values in list into a single string, using separator to delimit each value.
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

Will be deprecated after version 0.28
Alias of str[index]

INITCAP (str: varchar) → varchar

New in 0.26
Capitalize the initial letter of each word in a string.

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

Calculate the Levenshtein Distance between str1 and str2. This measures the minimum number of single-character edits required to change one string into the other.

LOWER (str: varchar) → varchar

Converts str to lowercase.
Related: UPPER, TITLE

LPAD (string: varchar, width: integer, fill: char) → varchar

Return a string that is at least width characters wide. If string is shorter than width, pad it on the left with fill characters to reach 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 full-text search of column for the values in query. This function is useful for searching text content.
Note: Values in $stop_words are ignored.

POSITION (substring: varchar IN string: varchar) → numeric

Return the starting position of the first occurrence of substring within string. Positions start at 1. If substring is not found, 0 is returned.

REGEXP_REPLACE (str: varchar, pattern: varchar, replace: varchar) → varchar

Replace all occurrences of the regular expression pattern in str with replace.

REPLACE (str: varchar, pattern: varchar, replace: varchar) → varchar

New in 0.26
Replace all occurrences of pattern in str with replace.

REVERSE (str: varchar) → varchar

Return str with its 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

Return a string that is at least width characters wide. If string is shorter than width, pad it on the right with fill characters to reach the required width. Related: LPAD

RTRIM (str: varchar) → varchar

Remove trailing whitespace from str.
Related: LTRIM, TRIM

SOUNDEX (str: varchar) → varchar

Return a character string containing the phonetic representation of str. This is useful for finding similar-sounding words. See Soundex 🡕.

SEARCH (str: varchar, substring: varchar) → boolean

Will be deprecated after version 0.28
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

Extract a substring from str starting at position start and continuing to the end of the string.

SUBSTRING (str: varchar, start: numeric, length: numeric) → varchar

Extract a substring from str starting at position start for length characters.

STARTS_WITH (str: varchar, value: varchar) → boolean

Return true if str starts with value.
Related: ENDS_WITH

TITLE (str: varchar) → varchar

Return str with the first letter of each word capitalized.
Related: LOWER, UPPER

TRIM ( [ LEADING | TRAILING | BOTH ] [ chars: varchar FROM ] str: varchar ) → varchar

Remove leading and/or trailing characters from str. If chars is not specified, whitespace is removed. Note that any character present in chars will be removed, regardless of order.
The LEADING modifier removes chars from the beginning of str.
The TRAILING modifier removes chars from the end of str.
The BOTH modifier removes chars from both the beginning and end of str. This is the default behavior if no modifier is specified.
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

Will be deprecated after version 0.28
Alias of object->key

JSONB_OBJECT_KEYS (object: struct) → array

Return an 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

Will be deprecated after version 0.28
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

Calculate the cosine similarity between str and value. This measures the similarity of two text strings based on their content.
Note: Values in $stop_words are ignored.

GENERATE_SERIES (start: numeric, stop: numeric) → array<numeric>

Generate an array of numbers from start to stop (inclusive) with a step of 1.

GENERATE_SERIES (start: numeric, stop: numeric, step: numeric) → array<numeric>

Generate an array of numbers from start to stop (inclusive) with an increment of step.

GENERATE_SERIES (start: timestamp, stop: timestamp, interval) → array<timestamp>

Generate an array of timestamps from start to stop (inclusive) with the specified interval.

HASH (value: any) → varchar

Calculate the CityHash (64-bit) hash value of value.

HEX_DECODE (str: varchar) → varchar

Decode a hexadecimal (BASE16) encoded string str.
Related: HEX_ENCODE

HEX_ENCODE (str: varchar) → varchar

Encode str using hexadecimal (BASE16) encoding.
Related: HEX_DECODE

IIF (condition, true_value, false_value) → input type

Return true_value if condition evaluates to True, otherwise return false_value. This is a shorthand for a simple CASE expression.

IFNOTNULL (check_expression: any, replacement_value: any) → input type

New in 0.19
Return check_expression if it is not null, otherwise return replacement_value.
Related: IFNULL

IFNULL (check_expression: any, replacement_value: any) → input type

Return check_expression if it is not null, otherwise return replacement_value.
Related: COALESCE, IFNOTNULL

NORMAL () → numeric

Generate a random number from a normal (Gaussian) distribution with mean 0.0 and standard deviation 1.0. A different value is generated for each row.

NULLIF (value1: any, value2: any) → input type

Return null if value1 equals value2, otherwise return value1. This is useful for converting specific values to null.

MD5 (str: varchar) → varchar

Calculate the MD5 hash of str.

RAND () → numeric

Generate a random number between 0 and 1. A different value is generated for each row.

RANDOM () → numeric

Alias of RAND

RANDOM_STRING (length: numeric) → varchar

Generate a random string of lowercase alphabetic characters with the specified length. A different value is generated for each row.

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 str.
Related: SHA1, SHA224, SHA256, SHA512

SHA512 (str: varchar) → varchar

Calculate the SHA512 hash of str.
Related: SHA1, SHA224, SHA256, SHA384

UNNEST (array: array) → relation

Convert an array into a virtual relation where each array element becomes a separate row.