Powerful nodes to transform your data
Transform and model your data with 200+ nodes and functions.
Description
Cleanse is used to fix common data quality issues. You can replace null values, remove punctuation, modify capitalization, and more.
Cleanse is used to fix common data quality issues. You can replace null values, remove punctuation, modify capitalization, and more.
Expression
Cleanse
Description
Cleanse is used to fix common data quality issues. You can replace null values, remove punctuation, modify capitalization, and more.
Ports
example usage
Returns
Description
Exclude, rename, or modify data types for the columns in the selected table
Exclude, rename, or modify data types for the columns in the selected table
Expression
Edit columns
Description
Exclude, rename, or modify data types for the columns in the selected table
Ports
example usage
Returns
Description
Create data sets using fake data
Create data sets using fake data
Expression
Fake data
Description
Create data sets using fake data
Ports
example usage
Returns
Description
Filter table rows based on the given conditions
Filter table rows based on the given conditions
Expression
Filter
Description
Filter table rows based on the given conditions
Ports
example usage
Returns
Description
Forecast time series on the basis of past data
Forecast time series on the basis of past data
Expression
Forecast
Description
Forecast time series on the basis of past data
Ports
example usage
Returns
Description
Add a new column based on a given expression
Add a new column based on a given expression
Expression
Formula
Description
Add a new column based on a given expression
Ports
example usage
Returns
Description
Join two tables
Join two tables
Expression
Join
Description
Join two tables
Ports
example usage
Returns
Description
Build pivot table
Build pivot table
Expression
Pivot
Description
Build pivot table
Ports
example usage
Returns
Description
Add a new column with unique row IDs
Add a new column with unique row IDs
Expression
Row ID
Description
Add a new column with unique row IDs
Ports
example usage
Returns
Description
Calculate a cumulative sum on a numeric column per record in a table.
Calculate a cumulative sum on a numeric column per record in a table.
Expression
Running total
Description
Calculate a cumulative sum on a numeric column per record in a table.
Ports
example usage
Returns
Description
Executes SQL queries
Executes SQL queries
Expression
SQL
Description
Executes SQL queries
Ports
example usage
Returns
Description
Export data transformation results
Export data transformation results
Expression
Sink
Description
Export data transformation results
Ports
example usage
Returns
Description
Sort rows
Sort rows
Expression
Sort
Description
Sort rows
Ports
example usage
Returns
Description
Add tables from different Data Sources
Add tables from different Data Sources
Expression
Source
Description
Add tables from different Data Sources
Ports
example usage
Returns
Description
Split column text by given occurrences, into new columns/rows
Split column text by given occurrences, into new columns/rows
Expression
Split text
Description
Split column text by given occurrences, into new columns/rows
Ports
example usage
Returns
Description
Group rows and summarize them with an expression.
Group rows and summarize them with an expression.
Expression
Summarize
Description
Group rows and summarize them with an expression.
Ports
example usage
Returns
Description
Concatenate two tables vertically
Concatenate two tables vertically
Expression
Union
Description
Concatenate two tables vertically
Ports
example usage
Returns
Description
absolute value
absolute value
Expression
abs
(x)
Description
absolute value
Ports
example usage
Returns
Description
computes the arccosine of x
computes the arccosine of x
Expression
acos
(x)
Description
computes the arccosine of x
Ports
example usage
Returns
Description
Subtract from current_date
Subtract from current_date
Expression
age
(timestamp)
Description
Subtract from current_date
Ports
example usage
Returns
Description
Subtract arguments, resulting in the time difference between the two timestamps
Subtract arguments, resulting in the time difference between the two timestamps
Expression
age
(timestamp, timestamp)
Description
Subtract arguments, resulting in the time difference between the two timestamps
Ports
example usage
Returns
Description
Return the name of the column
Return the name of the column
Expression
alias
(column)
Description
Return the name of the column
Ports
example usage
Returns
Description
Alias for list_aggregate.
Alias for list_aggregate.
Expression
array_aggr
(list, name)
Description
Alias for list_aggregate.
Ports
example usage
Returns
Description
Alias for list_aggregate.
Alias for list_aggregate.
Expression
array_aggregate
(list, name)
Description
Alias for list_aggregate.
Ports
example usage
Returns
Description
Alias for list_append.
Alias for list_append.
Expression
array_append
(list, element)
Description
Alias for list_append.
Ports
example usage
Returns
Description
Alias for list_concat.
Alias for list_concat.
Expression
array_cat
(list1, list2)
Description
Alias for list_concat.
Ports
example usage
Returns
Description
Alias for list_concat.
Alias for list_concat.
Expression
array_concat
(list1, list2)
Description
Alias for list_concat.
Ports
example usage
Returns
Description
Alias for list_contains.
Alias for list_contains.
Expression
array_contains
(list, element)
Description
Alias for list_contains.
Ports
example usage
Returns
Description
Extract a single character using a (1-based) index.
Extract a single character using a (1-based) index.
Expression
array_extract
(list, index)
Description
Extract a single character using a (1-based) index.
Ports
example usage
Returns
Description
Alias for list_extract.
Alias for list_extract.
Expression
array_extract
(list, index)
Description
Alias for list_extract.
Ports
example usage
Returns
Description
Alias for list_contains.
Alias for list_contains.
Expression
array_has
(list, element)
Description
Alias for list_contains.
Ports
example usage
Returns
Description
Alias for list_position.
Alias for list_position.
Expression
array_indexof
(list, element)
Description
Alias for list_position.
Ports
example usage
Returns
Description
Alias for len.
Alias for len.
Expression
array_length
(list)
Description
Alias for len.
Ports
example usage
Returns
Description
Returns the list without the last element.
Returns the list without the last element.
Expression
array_pop_back
(list)
Description
Returns the list without the last element.
Ports
example usage
Returns
Description
Returns the list without the first element.
Returns the list without the first element.
Expression
array_pop_front
(list)
Description
Returns the list without the first element.
Ports
example usage
Returns
Description
Alias for list_position.
Alias for list_position.
Expression
array_position
(list, element)
Description
Alias for list_position.
Ports
example usage
Returns
Description
Alias for list_prepend.
Alias for list_prepend.
Expression
array_prepend
(element, list)
Description
Alias for list_prepend.
Ports
example usage
Returns
Description
Alias for list_append.
Alias for list_append.
Expression
array_push_back
(list, element)
Description
Alias for list_append.
Ports
example usage
Returns
Description
Alias for list_prepend.
Alias for list_prepend.
Expression
array_push_front
(list, element)
Description
Alias for list_prepend.
Ports
example usage
Returns
Description
Alias for list_reverse_sort.
Alias for list_reverse_sort.
Expression
array_reverse_sort
(list)
Description
Alias for list_reverse_sort.
Ports
example usage
Returns
Description
Extract a string using slice conventions. NULLs are interpreted as the bounds of the string. Negative values are accepted.
Extract a string using slice conventions. NULLs are interpreted as the bounds of the string. Negative values are accepted.
Expression
array_slice
(list, begin, end)
Description
Extract a string using slice conventions. NULLs are interpreted as the bounds of the string. Negative values are accepted.
Ports
example usage
Returns
Description
Alias for list_slice.
Alias for list_slice.
Expression
array_slice
(list, begin, end)
Description
Alias for list_slice.
Ports
example usage
Returns
Description
Alias for list_sort.
Alias for list_sort.
Expression
array_sort
(list)
Description
Alias for list_sort.
Ports
example usage
Returns
Description
Returns an integer that represents the Unicode code point of the first character of the string
Returns an integer that represents the Unicode code point of the first character of the string
Expression
ascii
(string)
Description
Returns an integer that represents the Unicode code point of the first character of the string
Ports
example usage
Returns
Description
computes the arcsine of x
computes the arcsine of x
Expression
asin
(x)
Description
computes the arcsine of x
Ports
example usage
Returns
Description
computes the arctangent of x
computes the arctangent of x
Expression
atan
(x)
Description
computes the arctangent of x
Ports
example usage
Returns
Description
computes the arctangent (x, y)
computes the arctangent (x, y)
Expression
atan2
(x, y)
Description
computes the arctangent (x, y)
Ports
example usage
Returns
Description
Convert a blob to a base64 encoded string. Alias of to_base64.
Convert a blob to a base64 encoded string. Alias of to_base64.
Expression
base64
(blob)
Description
Convert a blob to a base64 encoded string. Alias of to_base64.
Ports
example usage
Returns
Description
returns the number of bits that are set
returns the number of bits that are set
Expression
bit_count
(x)
Description
returns the number of bits that are set
Ports
example usage
Returns
Description
Number of bits in a string.
Number of bits in a string.
Expression
bit_length
(string)
Description
Number of bits in a string.
Ports
example usage
Returns
Description
Return the size of the map (or the number of entries in the map).
Return the size of the map (or the number of entries in the map).
Expression
cardinality
(map)
Description
Return the size of the map (or the number of entries in the map).
Ports
example usage
Returns
Description
returns the cube root of the number
returns the cube root of the number
Expression
cbrt
(x)
Description
returns the cube root of the number
Ports
example usage
Returns
Description
rounds the number up
rounds the number up
Expression
ceil
(x)
Description
rounds the number up
Ports
example usage
Returns
Description
rounds the number up. Alias of ceil.
rounds the number up. Alias of ceil.
Expression
ceiling
(x)
Description
rounds the number up. Alias of ceil.
Ports
example usage
Returns
Description
Century
Century
Expression
century
(date)
Description
Century
Ports
example usage
Returns
Description
Extracts the century of a timestamp
Extracts the century of a timestamp
Expression
century
(timestamp)
Description
Extracts the century of a timestamp
Ports
example usage
Returns
Description
returns a character which is corresponding the the ASCII code value or Unicode code point
returns a character which is corresponding the the ASCII code value or Unicode code point
Expression
chr
(x)
Description
returns a character which is corresponding the the ASCII code value or Unicode code point
Ports
example usage
Returns
Description
Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.
Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.
Expression
coalesce
(expr, ...)
Description
Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.
Ports
example usage
Returns
Description
Concatenate many strings together
Concatenate many strings together
Expression
concat
(string, ...)
Description
Concatenate many strings together
Ports
example usage
Returns
Description
Concatenate strings together separated by the specified separator
Concatenate strings together separated by the specified separator
Expression
concat_ws
(separator, string, ...)
Description
Concatenate strings together separated by the specified separator
Ports
example usage
Returns
Description
Return true if search_string is found within string
Return true if search_string is found within string
Expression
contains
(string, search_string)
Description
Return true if search_string is found within string
Ports
example usage
Returns
Description
computes the cosine of x
computes the cosine of x
Expression
cos
(x)
Description
computes the cosine of x
Ports
example usage
Returns
Description
computes the cotangent of x
computes the cotangent of x
Expression
cot
(x)
Description
computes the cotangent of x
Ports
example usage
Returns
Description
Return the name of the currently active schema. Default is main.
Return the name of the currently active schema. Default is main.
Expression
current_schema
()
Description
Return the name of the currently active schema. Default is main.
Ports
example usage
Returns
Description
Return list of schemas. Pass a parameter of True to include implicit schemas.
Return list of schemas. Pass a parameter of True to include implicit schemas.
Expression
current_schemas
(boolean)
Description
Return list of schemas. Pass a parameter of True to include implicit schemas.
Ports
example usage
Returns
Description
Return the current value of the configuration setting
Return the current value of the configuration setting
Expression
current_setting
('setting_name')
Description
Return the current value of the configuration setting
Ports
example usage
Returns
Description
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
Expression
currval
('sequence_name')
Description
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
Ports
example usage
Returns
Description
The number of partition boundaries between the dates
The number of partition boundaries between the dates
Expression
date_diff
(part, startdate, enddate)
Description
The number of partition boundaries between the dates
Ports
example usage
Returns
Description
The number of partition boundaries between the timestamps
The number of partition boundaries between the timestamps
Expression
date_diff
(part, startdate, enddate)
Description
The number of partition boundaries between the timestamps
Ports
example usage
Returns
Description
The number of partition boundaries between the times
The number of partition boundaries between the times
Expression
date_diff
(part, starttime, endtime)
Description
The number of partition boundaries between the times
Ports
example usage
Returns
Description
Get the subfield
Get the subfield
Expression
date_part
(part, date)
Description
Get the subfield
Ports
example usage
Returns
Description
Get subfield
Get subfield
Expression
date_part
(part, interval)
Description
Get subfield
Ports
example usage
Returns
Description
Get subfield
Get subfield
Expression
date_part
(part, time)
Description
Get subfield
Ports
example usage
Returns
Description
Get subfield
Get subfield
Expression
date_part
(part, timestamp)
Description
Get subfield
Ports
example usage
Returns
Description
The number of complete partitions between the dates
The number of complete partitions between the dates
Expression
date_sub
(part, startdate, enddate)
Description
The number of complete partitions between the dates
Ports
example usage
Returns
Description
The number of complete partitions between the timestamps
The number of complete partitions between the timestamps
Expression
date_sub
(part, startdate, enddate)
Description
The number of complete partitions between the timestamps
Ports
example usage
Returns
Description
The number of complete partitions between the times
The number of complete partitions between the times
Expression
date_sub
(part, starttime, endtime)
Description
The number of complete partitions between the times
Ports
example usage
Returns
Description
Truncate to specified precision
Truncate to specified precision
Expression
date_trunc
(part, date)
Description
Truncate to specified precision
Ports
example usage
Returns
Description
Truncate to specified precision
Truncate to specified precision
Expression
date_trunc
(part, timestamp)
Description
Truncate to specified precision
Ports
example usage
Returns
Description
Alias of date_diff. The number of partition boundaries between the dates
Alias of date_diff. The number of partition boundaries between the dates
Expression
datediff
(part, startdate, enddate)
Description
Alias of date_diff. The number of partition boundaries between the dates
Ports
example usage
Returns
Description
Alias of date_part. Get the subfield
Alias of date_part. Get the subfield
Expression
datepart
(part, date)
Description
Alias of date_part. Get the subfield
Ports
example usage
Returns
Description
Alias of date_sub. The number of complete partitions between the dates
Alias of date_sub. The number of complete partitions between the dates
Expression
datesub
(part, startdate, enddate)
Description
Alias of date_sub. The number of complete partitions between the dates
Ports
example usage
Returns
Description
Alias of date_trunc. Truncate to specified precision
Alias of date_trunc. Truncate to specified precision
Expression
datetrunc
(part, date)
Description
Alias of date_trunc. Truncate to specified precision
Ports
example usage
Returns
Description
Day
Day
Expression
day
(date)
Description
Day
Ports
example usage
Returns
Description
The (English) name of the weekday
The (English) name of the weekday
Expression
dayname
(date)
Description
The (English) name of the weekday
Ports
example usage
Returns
Description
The (English) name of the weekday
The (English) name of the weekday
Expression
dayname
(timestamp)
Description
The (English) name of the weekday
Ports
example usage
Returns
Description
Day (synonym)
Day (synonym)
Expression
dayofmonth
(date)
Description
Day (synonym)
Ports
example usage
Returns
Description
Numeric weekday (Sunday = 0, Saturday = 6)
Numeric weekday (Sunday = 0, Saturday = 6)
Expression
dayofweek
(date)
Description
Numeric weekday (Sunday = 0, Saturday = 6)
Ports
example usage
Returns
Description
Numeric ISO weekday (Monday = 1, Sunday = 7)
Numeric ISO weekday (Monday = 1, Sunday = 7)
Expression
dayofyear
(date)
Description
Numeric ISO weekday (Monday = 1, Sunday = 7)
Ports
example usage
Returns
Description
Decade (year / 10)
Decade (year / 10)
Expression
decade
(date)
Description
Decade (year / 10)
Ports
example usage
Returns
Description
Convert blob to varchar. Fails if blob is not valid utf-8.
Convert blob to varchar. Fails if blob is not valid utf-8.
Expression
decode
(blob)
Description
Convert blob to varchar. Fails if blob is not valid utf-8.
Ports
example usage
Returns
Description
converts radians to degrees
converts radians to degrees
Expression
degrees
(x)
Description
converts radians to degrees
Ports
example usage
Returns
Description
Alias of levenshtein for SQLite compatibility. The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
Alias of levenshtein for SQLite compatibility. The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
Expression
editdist3
(string, string)
Description
Alias of levenshtein for SQLite compatibility. The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
Ports
example usage
Returns
Description
Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Expression
element_at
(map, key)
Description
Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Ports
example usage
Returns
Description
Convert varchar to blob. Converts utf-8 characters into literal encoding.
Convert varchar to blob. Converts utf-8 characters into literal encoding.
Expression
encode
(string)
Description
Convert varchar to blob. Converts utf-8 characters into literal encoding.
Ports
example usage
Returns
Description
Seconds since 1970-01-01
Seconds since 1970-01-01
Expression
epoch
(date)
Description
Seconds since 1970-01-01
Ports
example usage
Returns
Description
Converts ms since epoch to a timestamp
Converts ms since epoch to a timestamp
Expression
epoch_ms
(ms)
Description
Converts ms since epoch to a timestamp
Ports
example usage
Returns
Description
Calendar era
Calendar era
Expression
era
(date)
Description
Calendar era
Ports
example usage
Returns
Description
round to next even number by rounding away from zero.
round to next even number by rounding away from zero.
Expression
even
(x)
Description
round to next even number by rounding away from zero.
Ports
example usage
Returns
Description
Get subfield from a timestamp
Get subfield from a timestamp
Expression
extract
(field from timestamp)
Description
Get subfield from a timestamp
Ports
example usage
Returns
Description
Get subfield from a date
Get subfield from a date
Expression
extract
(part from date)
Description
Get subfield from a date
Ports
example usage
Returns
Description
Get subfield from a date
Get subfield from a date
Expression
extract
(part from interval)
Description
Get subfield from a date
Ports
example usage
Returns
Description
Get subfield from a time
Get subfield from a time
Expression
extract
(part from time)
Description
Get subfield from a time
Ports
example usage
Returns
Description
See ! operator. Computes the product of the current integer and all integers below it
See ! operator. Computes the product of the current integer and all integers below it
Expression
factorial
(x)
Description
See ! operator. Computes the product of the current integer and all integers below it
Ports
example usage
Returns
Description
rounds the number down
rounds the number down
Expression
floor
(x)
Description
rounds the number down
Ports
example usage
Returns
Description
Formats a string using fmt syntax
Formats a string using fmt syntax
Expression
format
(format, parameters...)
Description
Formats a string using fmt syntax
Ports
example usage
Returns
Description
Convert a base64 encoded string to a character string.
Convert a base64 encoded string to a character string.
Expression
from_base64
(string)
Description
Convert a base64 encoded string to a character string.
Ports
example usage
Returns
Description
interpolation of (x-1) factorial (so decimal inputs are allowed)
interpolation of (x-1) factorial (so decimal inputs are allowed)
Expression
gamma
(x)
Description
interpolation of (x-1) factorial (so decimal inputs are allowed)
Ports
example usage
Returns
Description
Alias of uuid. Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
Alias of uuid. Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
Expression
gen_random_uuid
()
Description
Alias of uuid. Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
Ports
example usage
Returns
Description
The later of two dates
The later of two dates
Expression
greatest
(date, date)
Description
The later of two dates
Ports
example usage
Returns
Description
The later of two timestamps
The later of two timestamps
Expression
greatest
(timestamp, timestamp)
Description
The later of two timestamps
Ports
example usage
Returns
Description
selects the largest value
selects the largest value
Expression
greatest
(x1, x2, ...)
Description
selects the largest value
Ports
example usage
Returns
Description
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
Expression
hamming
(string, string)
Description
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
Ports
example usage
Returns
Description
Hours
Hours
Expression
hour
(date)
Description
Hours
Ports
example usage
Returns
Description
Surrogate key used to sort special characters according to the specific locale. Collator parameter is optional. Valid only when ICU extension is installed.
Surrogate key used to sort special characters according to the specific locale. Collator parameter is optional. Valid only when ICU extension is installed.
Expression
icu_sort_key
(string , collator)
Description
Surrogate key used to sort special characters according to the specific locale. Collator parameter is optional. Valid only when ICU extension is installed.
Ports
example usage
Returns
Description
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Expression
instr
(string, search_string)
Description
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Ports
example usage
Returns
Description
Returns true if the date is finite, false otherwise
Returns true if the date is finite, false otherwise
Expression
isfinite
(date)
Description
Returns true if the date is finite, false otherwise
Ports
example usage
Returns
Description
Returns true if the timestamp is finite, false otherwise
Returns true if the timestamp is finite, false otherwise
Expression
isfinite
(timestamp)
Description
Returns true if the timestamp is finite, false otherwise
Ports
example usage
Returns
Description
Returns true if the floating point value is finite, false otherwise
Returns true if the floating point value is finite, false otherwise
Expression
isfinite
(x)
Description
Returns true if the floating point value is finite, false otherwise
Ports
example usage
Returns
Description
Returns true if the date is infinite, false otherwise
Returns true if the date is infinite, false otherwise
Expression
isinf
(date)
Description
Returns true if the date is infinite, false otherwise
Ports
example usage
Returns
Description
Returns true if the timestamp is infinite, false otherwise
Returns true if the timestamp is infinite, false otherwise
Expression
isinf
(timestamp)
Description
Returns true if the timestamp is infinite, false otherwise
Ports
example usage
Returns
Description
Returns true if the floating point value is infinite, false otherwise
Returns true if the floating point value is infinite, false otherwise
Expression
isinf
(x)
Description
Returns true if the floating point value is infinite, false otherwise
Ports
example usage
Returns
Description
Returns true if the floating point value is not a number, false otherwise
Returns true if the floating point value is not a number, false otherwise
Expression
isnan
(x)
Description
Returns true if the floating point value is not a number, false otherwise
Ports
example usage
Returns
Description
Numeric ISO weekday (Monday = 1, Sunday = 7)
Numeric ISO weekday (Monday = 1, Sunday = 7)
Expression
isodow
(date)
Description
Numeric ISO weekday (Monday = 1, Sunday = 7)
Ports
example usage
Returns
Description
ISO Year number (Starts on Monday of week containing Jan 4th)
ISO Year number (Starts on Monday of week containing Jan 4th)
Expression
isoyear
(date)
Description
ISO Year number (Starts on Monday of week containing Jan 4th)
Ports
example usage
Returns
Description
The Jaccard similarity between two strings. Different case is considered different. Returns a number between 0 and 1.
The Jaccard similarity between two strings. Different case is considered different. Returns a number between 0 and 1.
Expression
jaccard
(string, string)
Description
The Jaccard similarity between two strings. Different case is considered different. Returns a number between 0 and 1.
Ports
example usage
Returns
Description
The last day of the corresponding month in the date
The last day of the corresponding month in the date
Expression
last_day
(date)
Description
The last day of the corresponding month in the date
Ports
example usage
Returns
Description
The last day of the month.
The last day of the month.
Expression
last_day
(timestamp)
Description
The last day of the month.
Ports
example usage
Returns
Description
Alias of lower. Convert string to lower case
Alias of lower. Convert string to lower case
Expression
lcase
(string)
Description
Alias of lower. Convert string to lower case
Ports
example usage
Returns
Description
The earlier of two dates
The earlier of two dates
Expression
least
(date, date)
Description
The earlier of two dates
Ports
example usage
Returns
Description
The earlier of two timestamps
The earlier of two timestamps
Expression
least
(timestamp, timestamp)
Description
The earlier of two timestamps
Ports
example usage
Returns
Description
selects the smallest value
selects the smallest value
Expression
least
(x1, x2, ...)
Description
selects the smallest value
Ports
example usage
Returns
Description
Extract the left-most count characters
Extract the left-most count characters
Expression
left
(string, count)
Description
Extract the left-most count characters
Ports
example usage
Returns
Description
Return the length of the list.
Return the length of the list.
Expression
len
(list)
Description
Return the length of the list.
Ports
example usage
Returns
Description
Number of characters in string
Number of characters in string
Expression
length
(string)
Description
Number of characters in string
Ports
example usage
Returns
Description
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
Expression
levenshtein
(string, string)
Description
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different.
Ports
example usage
Returns
Description
computes the log of the gamma function.
computes the log of the gamma function.
Expression
lgamma
(x)
Description
computes the log of the gamma function.
Ports
example usage
Returns
Description
Returns true if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Returns true if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Expression
like_escape
(string, like_specifier, escape_character)
Description
Returns true if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Ports
example usage
Returns
Description
Alias for list_aggregate.
Alias for list_aggregate.
Expression
list_aggr
(list, name)
Description
Alias for list_aggregate.
Ports
example usage
Returns
Description
Executes the aggregate function name on the elements of list. See the List Aggregates section for more details.
Executes the aggregate function name on the elements of list. See the List Aggregates section for more details.
Expression
list_aggregate
(list, name)
Description
Executes the aggregate function name on the elements of list. See the List Aggregates section for more details.
Ports
example usage
Returns
Description
Appends element to list.
Appends element to list.
Expression
list_append
(list, element)
Description
Appends element to list.
Ports
example usage
Returns
Description
Alias for list_concat.
Alias for list_concat.
Expression
list_cat
(list1, list2)
Description
Alias for list_concat.
Ports
example usage
Returns
Description
Concatenates two lists.
Concatenates two lists.
Expression
list_concat
(list1, list2)
Description
Concatenates two lists.
Ports
example usage
Returns
Description
Returns true if the list contains the element.
Returns true if the list contains the element.
Expression
list_contains
(list, element)
Description
Returns true if the list contains the element.
Ports
example usage
Returns
Description
Alias for list_extract.
Alias for list_extract.
Expression
list_element
(list, index)
Description
Alias for list_extract.
Ports
example usage
Returns
Description
An alias for array_extract.
An alias for array_extract.
Expression
list_element
(string, index)
Description
An alias for array_extract.
Ports
example usage
Returns
Description
Extract the indexth (1-based) value from the list.
Extract the indexth (1-based) value from the list.
Expression
list_extract
(list, index)
Description
Extract the indexth (1-based) value from the list.
Ports
example usage
Returns
Description
An alias for array_extract.
An alias for array_extract.
Expression
list_extract
(string, index)
Description
An alias for array_extract.
Ports
example usage
Returns
Description
Alias for list_contains.
Alias for list_contains.
Expression
list_has
(list, element)
Description
Alias for list_contains.
Ports
example usage
Returns
Description
Alias for list_position.
Alias for list_position.
Expression
list_indexof
(list, element)
Description
Alias for list_position.
Ports
example usage
Returns
Description
Alias for list_value.
Alias for list_value.
Expression
list_pack
(any, ...)
Description
Alias for list_value.
Ports
example usage
Returns
Description
Returns the index of the element if the list contains the element.
Returns the index of the element if the list contains the element.
Expression
list_position
(list, element)
Description
Returns the index of the element if the list contains the element.
Ports
example usage
Returns
Description
Prepends element to list.
Prepends element to list.
Expression
list_prepend
(element, list)
Description
Prepends element to list.
Ports
example usage
Returns
Description
Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about the null sorting order.
Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about the null sorting order.
Expression
list_reverse_sort
(list)
Description
Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about the null sorting order.
Ports
example usage
Returns
Description
Extract a sublist using slice conventions. NULLs are interpreted as the bounds of the LIST. Negative values are accepted.
Extract a sublist using slice conventions. NULLs are interpreted as the bounds of the LIST. Negative values are accepted.
Expression
list_slice
(list, begin, end)
Description
Extract a sublist using slice conventions. NULLs are interpreted as the bounds of the LIST. Negative values are accepted.
Ports
example usage
Returns
Description
Sorts the elements of the list. See the Sorting Lists section for more details about the sorting order and the null sorting order.
Sorts the elements of the list. See the Sorting Lists section for more details about the sorting order and the null sorting order.
Expression
list_sort
(list)
Description
Sorts the elements of the list. See the Sorting Lists section for more details about the sorting order and the null sorting order.
Ports
example usage
Returns
Description
Create a LIST containing the argument values.
Create a LIST containing the argument values.
Expression
list_value
(any, ...)
Description
Create a LIST containing the argument values.
Ports
example usage
Returns
Description
computes the natural logarithm of x
computes the natural logarithm of x
Expression
ln
(x)
Description
computes the natural logarithm of x
Ports
example usage
Returns
Description
computes the 10-log of x
computes the 10-log of x
Expression
log
(x)
Description
computes the 10-log of x
Ports
example usage
Returns
Description
alias of log. computes the 10-log of x
alias of log. computes the 10-log of x
Expression
log10
(x)
Description
alias of log. computes the 10-log of x
Ports
example usage
Returns
Description
computes the 2-log of x
computes the 2-log of x
Expression
log2
(x)
Description
computes the 2-log of x
Ports
example usage
Returns
Description
Convert string to lower case
Convert string to lower case
Expression
lower
(string)
Description
Convert string to lower case
Ports
example usage
Returns
Description
Pads the string with the character from the left until it has count characters
Pads the string with the character from the left until it has count characters
Expression
lpad
(string, count, character)
Description
Pads the string with the character from the left until it has count characters
Ports
example usage
Returns
Description
Removes any spaces from the left side of the string
Removes any spaces from the left side of the string
Expression
ltrim
(string)
Description
Removes any spaces from the left side of the string
Ports
example usage
Returns
Description
Removes any occurrences of any of the characters from the left side of the string
Removes any occurrences of any of the characters from the left side of the string
Expression
ltrim
(string, characters)
Description
Removes any occurrences of any of the characters from the left side of the string
Ports
example usage
Returns
Description
The date for the given parts
The date for the given parts
Expression
make_date
(bigint, bigint, bigint)
Description
The date for the given parts
Ports
example usage
Returns
Description
The time for the given parts
The time for the given parts
Expression
make_time
(bigint, bigint, double)
Description
The time for the given parts
Ports
example usage
Returns
Description
The timestamp for the given parts
The timestamp for the given parts
Expression
make_timestamp
(bigint, bigint, bigint, bigint, bigint, double)
Description
The timestamp for the given parts
Ports
example usage
Returns
Description
Returns an empty map.
Returns an empty map.
Expression
map
()
Description
Returns an empty map.
Ports
example usage
Returns
Description
Alias of element_at. Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Alias of element_at. Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Expression
map_extract
(map, key)
Description
Alias of element_at. Return a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys else an error is returned.
Ports
example usage
Returns
Description
Return an md5 one-way hash of the string.
Return an md5 one-way hash of the string.
Expression
md5
(string)
Description
Return an md5 one-way hash of the string.
Ports
example usage
Returns
Description
Returns the MD5 hash of the value
Returns the MD5 hash of the value
Expression
md5
(value)
Description
Returns the MD5 hash of the value
Ports
example usage
Returns
Description
Sub-minute microseconds
Sub-minute microseconds
Expression
microsecond
(date)
Description
Sub-minute microseconds
Ports
example usage
Returns
Description
Millennium
Millennium
Expression
millennium
(date)
Description
Millennium
Ports
example usage
Returns
Description
Sub-minute milliseconds
Sub-minute milliseconds
Expression
millisecond
(date)
Description
Sub-minute milliseconds
Ports
example usage
Returns
Description
Minutes
Minutes
Expression
minute
(date)
Description
Minutes
Ports
example usage
Returns
Description
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
Expression
mismatches
(string, string)
Description
The number of positions with different characters for 2 strings of equal length. Different case is considered different.
Ports
example usage
Returns
Description
Month
Month
Expression
month
(date)
Description
Month
Ports
example usage
Returns
Description
The (English) name of the month
The (English) name of the month
Expression
monthname
(date)
Description
The (English) name of the month
Ports
example usage
Returns
Description
The (English) name of the month.
The (English) name of the month.
Expression
monthname
(timestamp)
Description
The (English) name of the month.
Ports
example usage
Returns
Description
return the next floating point value after x in the direction of y
return the next floating point value after x in the direction of y
Expression
nextafter
(x, y)
Description
return the next floating point value after x in the direction of y
Ports
example usage
Returns
Description
Return the following value of the sequence.
Return the following value of the sequence.
Expression
nextval
('sequence_name')
Description
Return the following value of the sequence.
Ports
example usage
Returns
Description
Convert string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.
Convert string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.
Expression
nfc_normalize
(string)
Description
Convert string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.
Ports
example usage
Returns
Description
Returns false if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Returns false if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Expression
not_like_escape
(string, like_specifier, escape_character)
Description
Returns false if the string matches the like_specifier (see Pattern Matching. escape_character is used to search for wildcard characters in the string.
Ports
example usage
Returns
Description
Current date and time (start of current transaction)
Current date and time (start of current transaction)
Expression
now
()
Description
Current date and time (start of current transaction)
Ports
example usage
Returns
Description
Number of bytes in blob
Number of bytes in blob
Expression
octet_length
(blob)
Description
Number of bytes in blob
Ports
example usage
Returns
Description
Return ASCII character code of the leftmost character in a string.
Return ASCII character code of the leftmost character in a string.
Expression
ord
(string)
Description
Return ASCII character code of the leftmost character in a string.
Ports
example usage
Returns
Description
Returns the lower case name of the data type of the result of the expression. For Postgres compatibility.
Returns the lower case name of the data type of the result of the expression. For Postgres compatibility.
Expression
pg_typeof
(expression)
Description
Returns the lower case name of the data type of the result of the expression. For Postgres compatibility.
Ports
example usage
Returns
Description
returns the value of pi
returns the value of pi
Expression
pi
()
Description
returns the value of pi
Ports
example usage
Returns
Description
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Expression
position
(search_string in string)
Description
Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Ports
example usage
Returns
Description
computes x to the power of y
computes x to the power of y
Expression
pow
(x, y)
Description
computes x to the power of y
Ports
example usage
Returns
Description
Alias of pow. computes x to the power of y
Alias of pow. computes x to the power of y
Expression
power
(x, y)
Description
Alias of pow. computes x to the power of y
Ports
example usage
Returns
Description
Return true if string starts with search_string.
Return true if string starts with search_string.
Expression
prefix
(string, search_string)
Description
Return true if string starts with search_string.
Ports
example usage
Returns
Description
Formats a string using printf syntax
Formats a string using printf syntax
Expression
printf
(format, parameters...)
Description
Formats a string using printf syntax
Ports
example usage
Returns
Description
Quarter
Quarter
Expression
quarter
(date)
Description
Quarter
Ports
example usage
Returns
Description
converts degrees to radians
converts degrees to radians
Expression
radians
(x)
Description
converts degrees to radians
Ports
example usage
Returns
Description
returns a random number between 0 and 1
returns a random number between 0 and 1
Expression
random
()
Description
returns a random number between 0 and 1
Ports
example usage
Returns
Description
Returns true if the entire string matches the regex (see Pattern Matching
Returns true if the entire string matches the regex (see Pattern Matching
Expression
regexp_full_match
(string, regex)
Description
Returns true if the entire string matches the regex (see Pattern Matching
Ports
example usage
Returns
Description
Returns true if a part of string matches the regex (see Pattern Matching
Returns true if a part of string matches the regex (see Pattern Matching
Expression
regexp_matches
(string, regex)
Description
Returns true if a part of string matches the regex (see Pattern Matching
Ports
example usage
Returns
Description
Replaces the first occurrence of regex with the replacement, use 'g' modifier to replace all occurrences instead (see Pattern Matching
Replaces the first occurrence of regex with the replacement, use 'g' modifier to replace all occurrences instead (see Pattern Matching
Expression
regexp_replace
(string, regex, replacement, modifiers)
Description
Replaces the first occurrence of regex with the replacement, use 'g' modifier to replace all occurrences instead (see Pattern Matching
Ports
example usage
Returns
Description
Alias of string_split_regex. Splits the string along the regex
Alias of string_split_regex. Splits the string along the regex
Expression
regexp_split_to_array
(string, regex)
Description
Alias of string_split_regex. Splits the string along the regex
Ports
example usage
Returns
Description
Repeats the string count number of times
Repeats the string count number of times
Expression
repeat
(string, count)
Description
Repeats the string count number of times
Ports
example usage
Returns
Description
Replaces any occurrences of the source with target in string
Replaces any occurrences of the source with target in string
Expression
replace
(string, source, target)
Description
Replaces any occurrences of the source with target in string
Ports
example usage
Returns
Description
Reverses the string
Reverses the string
Expression
reverse
(string)
Description
Reverses the string
Ports
example usage
Returns
Description
Extract the right-most count characters
Extract the right-most count characters
Expression
right
(string, count)
Description
Extract the right-most count characters
Ports
example usage
Returns
Description
round to s decimal places
round to s decimal places
Expression
round
(v numeric, s int)
Description
round to s decimal places
Ports
example usage
Returns
Description
Create a STRUCT containing the argument values. If the values are column references, the entry name will be the column name; otherwise it will be the string 'vN' where N is the (1-based) position of the argument.
Create a STRUCT containing the argument values. If the values are column references, the entry name will be the column name; otherwise it will be the string 'vN' where N is the (1-based) position of the argument.
Expression
row
(any, ...)
Description
Create a STRUCT containing the argument values. If the values are column references, the entry name will be the column name; otherwise it will be the string 'vN' where N is the (1-based) position of the argument.
Ports
example usage
Returns
Description
Pads the string with the character from the right until it has count characters
Pads the string with the character from the right until it has count characters
Expression
rpad
(string, count, character)
Description
Pads the string with the character from the right until it has count characters
Ports
example usage
Returns
Description
Removes any spaces from the right side of the string
Removes any spaces from the right side of the string
Expression
rtrim
(string)
Description
Removes any spaces from the right side of the string
Ports
example usage
Returns
Description
Removes any occurrences of any of the characters from the right side of the string
Removes any occurrences of any of the characters from the right side of the string
Expression
rtrim
(string, characters)
Description
Removes any occurrences of any of the characters from the right side of the string
Ports
example usage
Returns
Description
Seconds
Seconds
Expression
second
(date)
Description
Seconds
Ports
example usage
Returns
Description
sets the seed to be used for the random function
sets the seed to be used for the random function
Expression
setseed
(x)
Description
sets the seed to be used for the random function
Ports
example usage
Returns
Description
returns the sign of x as -1, 0 or 1
returns the sign of x as -1, 0 or 1
Expression
sign
(x)
Description
returns the sign of x as -1, 0 or 1
Ports
example usage
Returns
Description
computes the sin of x
computes the sin of x
Expression
sin
(x)
Description
computes the sin of x
Ports
example usage
Returns
Description
returns the square root of the number
returns the square root of the number
Expression
sqrt
(x)
Description
returns the square root of the number
Ports
example usage
Returns
Description
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
Expression
stats
(expression)
Description
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
Ports
example usage
Returns
Description
Alias of string_split. Splits the string along the separator
Alias of string_split. Splits the string along the separator
Expression
str_split
(string, separator)
Description
Alias of string_split. Splits the string along the separator
Ports
example usage
Returns
Description
Alias of string_split_regex. Splits the string along the regex
Alias of string_split_regex. Splits the string along the regex
Expression
str_split_regex
(string, regex)
Description
Alias of string_split_regex. Splits the string along the regex
Ports
example usage
Returns
Description
Converts a date to a string according to the format string
Converts a date to a string according to the format string
Expression
strftime
(date, format)
Description
Converts a date to a string according to the format string
Ports
example usage
Returns
Description
Converts timestamp to string according to the format string
Converts timestamp to string according to the format string
Expression
strftime
(timestamp, format)
Description
Converts timestamp to string according to the format string
Ports
example usage
Returns
Description
Splits the string along the separator
Splits the string along the separator
Expression
string_split
(string, separator)
Description
Splits the string along the separator
Ports
example usage
Returns
Description
Splits the string along the regex
Splits the string along the regex
Expression
string_split_regex
(string, regex)
Description
Splits the string along the regex
Ports
example usage
Returns
Description
Alias of string_split. Splits the string along the separator
Alias of string_split. Splits the string along the separator
Expression
string_to_array
(string, separator)
Description
Alias of string_split. Splits the string along the separator
Ports
example usage
Returns
Description
Strips accents from string
Strips accents from string
Expression
strip_accents
(string)
Description
Strips accents from string
Ports
example usage
Returns
Description
Number of bytes in string
Number of bytes in string
Expression
strlen
(string)
Description
Number of bytes in string
Ports
example usage
Returns
Description
Alias of instr. Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Alias of instr. Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Expression
strpos
(string, search_string)
Description
Alias of instr. Return location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
Ports
example usage
Returns
Description
Converts string to timestamp according to the format string
Converts string to timestamp according to the format string
Expression
strptime
(text, format)
Description
Converts string to timestamp according to the format string
Ports
example usage
Returns
Description
Extract the named entry from the struct.
Extract the named entry from the struct.
Expression
struct_extract
(struct, 'entry')
Description
Extract the named entry from the struct.
Ports
example usage
Returns
Description
Create a STRUCT containing the argument values. The entry name will be the bound variable name.
Create a STRUCT containing the argument values. The entry name will be the bound variable name.
Expression
struct_pack
(name := any, ...)
Description
Create a STRUCT containing the argument values. The entry name will be the bound variable name.
Ports
example usage
Returns
Description
Alias of substring. Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Alias of substring. Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Expression
substr
(string, start, length)
Description
Alias of substring. Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Ports
example usage
Returns
Description
Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Expression
substring
(string, start, length)
Description
Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.
Ports
example usage
Returns
Description
Return true if string ends with search_string.
Return true if string ends with search_string.
Expression
suffix
(string, search_string)
Description
Return true if string ends with search_string.
Ports
example usage
Returns
Description
computes the tangent of x
computes the tangent of x
Expression
tan
(x)
Description
computes the tangent of x
Ports
example usage
Returns
Description
Time Zone offset in minutes
Time Zone offset in minutes
Expression
timezone
(date)
Description
Time Zone offset in minutes
Ports
example usage
Returns
Description
Time zone offset hour portion
Time zone offset hour portion
Expression
timezone_hour
(date)
Description
Time zone offset hour portion
Ports
example usage
Returns
Description
Time zone offset minutes portion
Time zone offset minutes portion
Expression
timezone_minute
(date)
Description
Time zone offset minutes portion
Ports
example usage
Returns
Description
Convert a blob to a base64 encoded string. Alias of base64.
Convert a blob to a base64 encoded string. Alias of base64.
Expression
to_base64
(blob)
Description
Convert a blob to a base64 encoded string. Alias of base64.
Ports
example usage
Returns
Description
Construct a day interval
Construct a day interval
Expression
to_days
(integer)
Description
Construct a day interval
Ports
example usage
Returns
Description
Construct a hour interval
Construct a hour interval
Expression
to_hours
(integer)
Description
Construct a hour interval
Ports
example usage
Returns