Powerful nodes to transform your data

Transform and model your data with 200+ nodes and functions.

Categories

Filter by categories
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Name
Description
Cleanse

Cleanse

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

Cleanse
Nodes
All

example usage

Select rows from Country and Replace text austria with Austria

Returns

Cleanse
Edit columns

Edit columns

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

Edit columns
Nodes
All

example usage

Remove ID, rename sales to Sales and change its type from text to number

Returns

Edit columns
Fake data

Fake data

Description

Create data sets using fake data

Create data sets using fake data

Expression

Fake data

Description

Create data sets using fake data

Ports

Fake data
Nodes
All

example usage

Generate a table with 3 rows and columns Person, Email and Phone

Returns

Fake data
Filter

Filter

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

Filter
Nodes
All

example usage

Keep rows that meet all the conditions: Sales > 200

Returns

Filter
Forecast

Forecast

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

Forecast
Nodes
All

example usage

Predict values of the column Sales for future times in column Day and generate predictions every 1 day over a period of 2 days using a linear model

Returns

Forecast
Formula

Formula

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

Formula
Nodes
All

example usage

Create a new column named Profit, placed at the end and with a value equal to: Sales – Expenses

Returns

Formula
Join

Join

Description

Join two tables

Join two tables

Expression

Join

Description

Join two tables

Ports

Join
Nodes
All

example usage

Left join on Country = Country

Returns

Join
Pivot

Pivot

Description

Build pivot table

Build pivot table

Expression

Pivot

Description

Build pivot table

Ports

Pivot
Nodes
All

example usage

Group rows by Country and create columns for each Category Ascending. With value equal to sum() of Sales

Returns

Pivot
Row ID

Row ID

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

Row ID
Nodes
All

example usage

Create a new column named Id as the first column of the table, containing row identifiers of type text, of length 6, start at 000001 and increment by 1

Returns

Row ID
Running total

Running total

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

Running total
Nodes
All

example usage

Create a new column named Profit, placed at the end and with a value equal to: Sales – Expenses

Returns

Running total
SQL

SQL

Description

Executes SQL queries

Executes SQL queries

Expression

SQL

Description

Executes SQL queries

Ports

SQL
Nodes
All

example usage

Run SQL query: SELECT Country FROM A WHERE Sales > 100

Returns

SQL
Sink

Sink

Description

Export data transformation results

Export data transformation results

Expression

Sink

Description

Export data transformation results

Ports

Sink
Nodes
All

example usage

Returns

Sink
Sort

Sort

Description

Sort rows

Sort rows

Expression

Sort

Description

Sort rows

Ports

Sort
Nodes
All

example usage

Sort rows by Sales Descending

Returns

Sort
Source

Source

Description

Add tables from different Data Sources

Add tables from different Data Sources

Expression

Source

Description

Add tables from different Data Sources

Ports

Source
Nodes
All

example usage

Returns

Source
Split text

Split text

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

Split text
Nodes
All

example usage

Split the column Location by all the occurrence/s of the delimiter , into new columns

Returns

Split text
Summarize

Summarize

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

Summarize
Nodes
All

example usage

Group rows by Country and summarize them with sum() of Sales as Total Sales

Returns

Summarize
Union

Union

Description

Concatenate two tables vertically

Concatenate two tables vertically

Expression

Union

Description

Concatenate two tables vertically

Ports

Union
Nodes
All

example usage

Returns

Union
abs

abs (x)

Description

absolute value

absolute value

Expression

abs

(x)

Description

absolute value

Ports

abs
Functions
Numeric
All

example usage

abs(-17.4)

Returns

17.4
abs
acos

acos (x)

Description

computes the arccosine of x

computes the arccosine of x

Expression

acos

(x)

Description

computes the arccosine of x

Ports

acos
Functions
Numeric
All

example usage

acos(0.5)

Returns

1.047197551
acos
age

age (timestamp)

Description

Subtract from current_date

Subtract from current_date

Expression

age

(timestamp)

Description

Subtract from current_date

Ports

age
Functions
Timestamp
All

example usage

age(TIMESTAMP '1992-09-20')

Returns

29 years 1 month 27 days 12:39:00.844
age
age

age (timestamp, timestamp)

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

age
Functions
Timestamp
All

example usage

age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20')

Returns

8 years 6 months 20 days
age
alias

alias (column)

Description

Return the name of the column

Return the name of the column

Expression

alias

(column)

Description

Return the name of the column

Ports

alias
Functions
Utility
All

example usage

alias(column1)

Returns

column1'
alias
array_aggr

array_aggr (list, name)

Description

Alias for list_aggregate.

Alias for list_aggregate.

Expression

array_aggr

(list, name)

Description

Alias for list_aggregate.

Ports

array_aggr
Functions
Nested list
All

example usage

array_aggr([1, 2, NULL], 'min')

Returns

1
array_aggr
array_aggregate

array_aggregate (list, name)

Description

Alias for list_aggregate.

Alias for list_aggregate.

Expression

array_aggregate

(list, name)

Description

Alias for list_aggregate.

Ports

array_aggregate
Functions
Nested list
All

example usage

array_aggregate([1, 2, NULL], 'min')

Returns

1
array_aggregate
array_append

array_append (list, element)

Description

Alias for list_append.

Alias for list_append.

Expression

array_append

(list, element)

Description

Alias for list_append.

Ports

array_append
Functions
Nested list
All

example usage

array_append([2, 3], 4)

Returns

[2, 3, 4]
array_append
array_cat

array_cat (list1, list2)

Description

Alias for list_concat.

Alias for list_concat.

Expression

array_cat

(list1, list2)

Description

Alias for list_concat.

Ports

array_cat
Functions
Nested list
All

example usage

array_cat([2, 3], [4, 5, 6])

Returns

[2, 3, 4, 5, 6]
array_cat
array_concat

array_concat (list1, list2)

Description

Alias for list_concat.

Alias for list_concat.

Expression

array_concat

(list1, list2)

Description

Alias for list_concat.

Ports

array_concat
Functions
Nested list
All

example usage

array_concat([2, 3], [4, 5, 6])

Returns

[2, 3, 4, 5, 6]
array_concat
array_contains

array_contains (list, element)

Description

Alias for list_contains.

Alias for list_contains.

Expression

array_contains

(list, element)

Description

Alias for list_contains.

Ports

array_contains
Functions
Nested list
All

example usage

array_contains([1, 2, NULL], 1)

Returns

TRUE
array_contains
array_extract

array_extract (list, index)

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

array_extract
Functions
Text
All

example usage

array_extract('DuckDB', 2)

Returns

u'
array_extract
array_extract

array_extract (list, index)

Description

Alias for list_extract.

Alias for list_extract.

Expression

array_extract

(list, index)

Description

Alias for list_extract.

Ports

array_extract
Functions
Nested list
All

example usage

array_extract([4, 5, 6], 3)

Returns

6
array_extract
array_has

array_has (list, element)

Description

Alias for list_contains.

Alias for list_contains.

Expression

array_has

(list, element)

Description

Alias for list_contains.

Ports

array_has
Functions
Nested list
All

example usage

array_has([1, 2, NULL], 1)

Returns

TRUE
array_has
array_indexof

array_indexof (list, element)

Description

Alias for list_position.

Alias for list_position.

Expression

array_indexof

(list, element)

Description

Alias for list_position.

Ports

array_indexof
Functions
Nested list
All

example usage

array_indexof([1, 2, NULL], 2)

Returns

2
array_indexof
array_length

array_length (list)

Description

Alias for len.

Alias for len.

Expression

array_length

(list)

Description

Alias for len.

Ports

array_length
Functions
Nested list
All

example usage

array_length([1, 2, 3])

Returns

3
array_length
array_pop_back

array_pop_back (list)

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

array_pop_back
Functions
Nested list
All

example usage

array_pop_back([4, 5, 6])

Returns

[4, 5]
array_pop_back
array_pop_front

array_pop_front (list)

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

array_pop_front
Functions
Nested list
All

example usage

array_pop_front([4, 5, 6])

Returns

[5, 6]
array_pop_front
array_position

array_position (list, element)

Description

Alias for list_position.

Alias for list_position.

Expression

array_position

(list, element)

Description

Alias for list_position.

Ports

array_position
Functions
Nested list
All

example usage

array_position([1, 2, NULL], 2)

Returns

2
array_position
array_prepend

array_prepend (element, list)

Description

Alias for list_prepend.

Alias for list_prepend.

Expression

array_prepend

(element, list)

Description

Alias for list_prepend.

Ports

array_prepend
Functions
Nested list
All

example usage

array_prepend(3, [4, 5, 6])

Returns

[3, 4, 5, 6]
array_prepend
array_push_back

array_push_back (list, element)

Description

Alias for list_append.

Alias for list_append.

Expression

array_push_back

(list, element)

Description

Alias for list_append.

Ports

array_push_back
Functions
Nested list
All

example usage

array_push_back([4, 5, 6], 7)

Returns

[4, 5, 6, 7]
array_push_back
array_push_front

array_push_front (list, element)

Description

Alias for list_prepend.

Alias for list_prepend.

Expression

array_push_front

(list, element)

Description

Alias for list_prepend.

Ports

array_push_front
Functions
Nested list
All

example usage

array_push_front([4, 5, 6], 3)

Returns

[3, 4, 5, 6]
array_push_front
array_reverse_sort

array_reverse_sort (list)

Description

Alias for list_reverse_sort.

Alias for list_reverse_sort.

Expression

array_reverse_sort

(list)

Description

Alias for list_reverse_sort.

Ports

array_reverse_sort
Functions
Nested list
All

example usage

array_reverse_sort([3, 6, 1, 2])

Returns

[6, 3, 2, 1]
array_reverse_sort
array_slice

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.

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

array_slice
Functions
Text
All

example usage

array_slice('DuckDB', 5, NULL)

Returns

DB'
array_slice
array_slice

array_slice (list, begin, end)

Description

Alias for list_slice.

Alias for list_slice.

Expression

array_slice

(list, begin, end)

Description

Alias for list_slice.

Ports

array_slice
Functions
Nested list
All

example usage

array_slice([4, 5, 6], 2, NULL)

Returns

[5, 6]
array_slice
array_sort

array_sort (list)

Description

Alias for list_sort.

Alias for list_sort.

Expression

array_sort

(list)

Description

Alias for list_sort.

Ports

array_sort
Functions
Nested list
All

example usage

array_sort([3, 6, 1, 2])

Returns

[1, 2, 3, 6]
array_sort
ascii

ascii (string)

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

ascii
Functions
Text
All

example usage

ascii('Ω')

Returns

937
ascii
asin

asin (x)

Description

computes the arcsine of x

computes the arcsine of x

Expression

asin

(x)

Description

computes the arcsine of x

Ports

asin
Functions
Numeric
All

example usage

asin(0.5)

Returns

0.5235987756
asin
atan

atan (x)

Description

computes the arctangent of x

computes the arctangent of x

Expression

atan

(x)

Description

computes the arctangent of x

Ports

atan
Functions
Numeric
All

example usage

atan(0.5)

Returns

0.463647609
atan
atan2

atan2 (x, y)

Description

computes the arctangent (x, y)

computes the arctangent (x, y)

Expression

atan2

(x, y)

Description

computes the arctangent (x, y)

Ports

atan2
Functions
Numeric
All

example usage

atan2(0.5, 0.5)

Returns

0.7853981634
atan2
base64

base64 (blob)

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

base64
Functions
Text
All

example usage

base64('A'::blob)

Returns

QQ=='
base64
bit_count

bit_count (x)

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

bit_count
Functions
Numeric
All

example usage

bit_count(31)

Returns

5
bit_count
bit_length

bit_length (string)

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

bit_length
Functions
Text
All

example usage

bit_length('abc')

Returns

24
bit_length
cardinality

cardinality (map)

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

cardinality
Functions
Nested map
All

example usage

cardinality(map([4, 2], ['a', 'b']))

Returns

2
cardinality
cbrt

cbrt (x)

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

cbrt
Functions
Numeric
All

example usage

cbrt(8)

Returns

2
cbrt
ceil

ceil (x)

Description

rounds the number up

rounds the number up

Expression

ceil

(x)

Description

rounds the number up

Ports

ceil
Functions
Numeric
All

example usage

ceil(17.4)

Returns

18
ceil
ceiling

ceiling (x)

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

ceiling
Functions
Numeric
All

example usage

ceiling(17.4)

Returns

18
ceiling
century

century (date)

Description

Century

Century

Expression

century

(date)

Description

Century

Ports

century
Functions
Date parts
All

example usage

century(date '1992-02-15')

Returns

20
century
century

century (timestamp)

Description

Extracts the century of a timestamp

Extracts the century of a timestamp

Expression

century

(timestamp)

Description

Extracts the century of a timestamp

Ports

century
Functions
Timestamp
All

example usage

century(TIMESTAMP '1992-03-22')

Returns

20
century
chr

chr (x)

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

chr
Functions
Numeric
All

example usage

chr(65)

Returns

A
chr
coalesce

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.

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

coalesce
Functions
Utility
All

example usage

coalesce(NULL,NULL,'default_string')

Returns

default_string'
coalesce
concat

concat (string, ...)

Description

Concatenate many strings together

Concatenate many strings together

Expression

concat

(string, ...)

Description

Concatenate many strings together

Ports

concat
Functions
Text
All

example usage

concat('Hello', ' ', 'World')

Returns

Hello World
concat
concat_ws

concat_ws (separator, string, ...)

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

concat_ws
Functions
Text
All

example usage

concat_ws(',', 'Banana', 'Apple', 'Melon')

Returns

Banana,Apple,Melon
concat_ws
contains

contains (string, search_string)

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

contains
Functions
Text
All

example usage

contains('abc','a')

Returns

TRUE
contains
cos

cos (x)

Description

computes the cosine of x

computes the cosine of x

Expression

cos

(x)

Description

computes the cosine of x

Ports

cos
Functions
Numeric
All

example usage

cos(90)

Returns

-0.4480736161
cos
cot

cot (x)

Description

computes the cotangent of x

computes the cotangent of x

Expression

cot

(x)

Description

computes the cotangent of x

Ports

cot
Functions
Numeric
All

example usage

cot(0.5)

Returns

1.830487722
cot
current_schema

current_schema ()

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

current_schema
Functions
Utility
All

example usage

current_schema()

Returns

main'
current_schema
current_schemas

current_schemas (boolean)

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

current_schemas
Functions
Utility
All

example usage

current_schemas(true)

Returns

['temp', 'main', 'pg_catalog']
current_schemas
current_setting

current_setting ('setting_name')

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

current_setting
Functions
Utility
All

example usage

current_setting('access_mode')

Returns

automatic'
current_setting
currval

currval ('sequence_name')

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

currval
Functions
Utility
All

example usage

currval('my_sequence_name')

Returns

1
currval
date_diff

date_diff (part, startdate, enddate)

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

date_diff
Functions
Date
All

example usage

date_diff('month', DATE '1992-09-15', DATE '1992-11-14')

Returns

2
date_diff
date_diff

date_diff (part, startdate, enddate)

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

date_diff
Functions
Timestamp
All

example usage

date_diff('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00')

Returns

2
date_diff
date_diff

date_diff (part, starttime, endtime)

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

date_diff
Functions
Time
All

example usage

date_diff('hour', TIME '01:02:03', TIME '06:01:03')

Returns

5
date_diff
date_part

date_part (part, date)

Description

Get the subfield

Get the subfield

Expression

date_part

(part, date)

Description

Get the subfield

Ports

date_part
Functions
Date
All

example usage

date_part('year', DATE '1992-09-20')

Returns

1992
date_part
date_part

date_part (part, interval)

Description

Get subfield

Get subfield

Expression

date_part

(part, interval)

Description

Get subfield

Ports

date_part
Functions
Interval
All

example usage

date_part('year', INTERVAL '14 months')

Returns

1
date_part
date_part

date_part (part, time)

Description

Get subfield

Get subfield

Expression

date_part

(part, time)

Description

Get subfield

Ports

date_part
Functions
Time
All

example usage

date_part('minute', TIME '14:21:13')

Returns

21
date_part
date_part

date_part (part, timestamp)

Description

Get subfield

Get subfield

Expression

date_part

(part, timestamp)

Description

Get subfield

Ports

date_part
Functions
Timestamp
All

example usage

date_part('minute', TIMESTAMP '1992-09-20 20:38:40')

Returns

38
date_part
date_sub

date_sub (part, startdate, enddate)

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

date_sub
Functions
Date
All

example usage

date_sub('month', DATE '1992-09-15', DATE '1992-11-14')

Returns

1
date_sub
date_sub

date_sub (part, startdate, enddate)

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

date_sub
Functions
Timestamp
All

example usage

date_sub('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00')

Returns

1
date_sub
date_sub

date_sub (part, starttime, endtime)

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

date_sub
Functions
Time
All

example usage

date_sub('hour', TIME '01:02:03', TIME '06:01:03')

Returns

4
date_sub
date_trunc

date_trunc (part, date)

Description

Truncate to specified precision

Truncate to specified precision

Expression

date_trunc

(part, date)

Description

Truncate to specified precision

Ports

date_trunc
Functions
Date
All

example usage

date_trunc('month', DATE '1992-03-07')

Returns

1992-03-01
date_trunc
date_trunc

date_trunc (part, timestamp)

Description

Truncate to specified precision

Truncate to specified precision

Expression

date_trunc

(part, timestamp)

Description

Truncate to specified precision

Ports

date_trunc
Functions
Timestamp
All

example usage

date_trunc('hour', TIMESTAMP '1992-09-20 20:38:40')

Returns

1992-09-20 20:00:00
date_trunc
datediff

datediff (part, startdate, enddate)

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

datediff
Functions
Date
All

example usage

datediff('month', DATE '1992-09-15', DATE '1992-11-14')

Returns

2
datediff
datepart

datepart (part, date)

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

datepart
Functions
Date
All

example usage

datepart('year', DATE '1992-09-20')

Returns

1992
datepart
datesub

datesub (part, startdate, enddate)

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

datesub
Functions
Date
All

example usage

datesub('month', DATE '1992-09-15', DATE '1992-11-14')

Returns

1
datesub
datetrunc

datetrunc (part, date)

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

datetrunc
Functions
Date
All

example usage

datetrunc('month', DATE '1992-03-07')

Returns

1992-03-01
datetrunc
day

day (date)

Description

Day

Day

Expression

day

(date)

Description

Day

Ports

day
Functions
Date parts
All

example usage

day(date '1992-02-15')

Returns

15
day
dayname

dayname (date)

Description

The (English) name of the weekday

The (English) name of the weekday

Expression

dayname

(date)

Description

The (English) name of the weekday

Ports

dayname
Functions
Date
All

example usage

dayname(DATE '1992-09-20')

Returns

Sunday
dayname
dayname

dayname (timestamp)

Description

The (English) name of the weekday

The (English) name of the weekday

Expression

dayname

(timestamp)

Description

The (English) name of the weekday

Ports

dayname
Functions
Timestamp
All

example usage

dayname(TIMESTAMP '1992-03-22')

Returns

Sunday
dayname
dayofmonth

dayofmonth (date)

Description

Day (synonym)

Day (synonym)

Expression

dayofmonth

(date)

Description

Day (synonym)

Ports

dayofmonth
Functions
Date parts
All

example usage

dayofmonth(date '1992-02-15')

Returns

15
dayofmonth
dayofweek

dayofweek (date)

Description

Numeric weekday (Sunday = 0, Saturday = 6)

Numeric weekday (Sunday = 0, Saturday = 6)

Expression

dayofweek

(date)

Description

Numeric weekday (Sunday = 0, Saturday = 6)

Ports

dayofweek
Functions
Date parts
All

example usage

dayofweek(date '1992-02-15')

Returns

6
dayofweek
dayofyear

dayofyear (date)

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

dayofyear
Functions
Date parts
All

example usage

isodow(date '1992-02-15')

Returns

46
dayofyear
decade

decade (date)

Description

Decade (year / 10)

Decade (year / 10)

Expression

decade

(date)

Description

Decade (year / 10)

Ports

decade
Functions
Date parts
All

example usage

decade(date '1992-02-15')

Returns

199
decade
decode

decode (blob)

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

decode
Functions
Blob
All

example usage

decode('\xC3\xBC'::BLOB)

Returns

ü
decode
degrees

degrees (x)

Description

converts radians to degrees

converts radians to degrees

Expression

degrees

(x)

Description

converts radians to degrees

Ports

degrees
Functions
Numeric
All

example usage

degrees(pi())

Returns

180
degrees
editdist3

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.

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

editdist3
Functions
Text similarity
All

example usage

editdist3('duck','db')

Returns

3
editdist3
element_at

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.

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

element_at
Functions
Nested map
All

example usage

element_at(map([100, 5], [42, 43]),100)

Returns

[42]
element_at
encode

encode (string)

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

encode
Functions
Blob
All

example usage

encode('my_string_with_ü')

Returns

my_string_with_\xC3\xBC
encode
epoch

epoch (date)

Description

Seconds since 1970-01-01

Seconds since 1970-01-01

Expression

epoch

(date)

Description

Seconds since 1970-01-01

Ports

epoch
Functions
Date parts
All

example usage

epoch(date '1992-02-15')

Returns

698112000
epoch
epoch_ms

epoch_ms (ms)

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

epoch_ms
Functions
Timestamp
All

example usage

epoch_ms(701222400000)

Returns

1992-03-22 0:00:00
epoch_ms
era

era (date)

Description

Calendar era

Calendar era

Expression

era

(date)

Description

Calendar era

Ports

era
Functions
Date parts
All

example usage

era(date '0044-03-15 (BC)')

Returns

0
era
even

even (x)

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

even
Functions
Numeric
All

example usage

even(2.9)

Returns

4
even
extract

extract (field from timestamp)

Description

Get subfield from a timestamp

Get subfield from a timestamp

Expression

extract

(field from timestamp)

Description

Get subfield from a timestamp

Ports

extract
Functions
Timestamp
All

example usage

extract('hour' FROM TIMESTAMP '1992-09-20 20:38:48')

Returns

20
extract
extract

extract (part from date)

Description

Get subfield from a date

Get subfield from a date

Expression

extract

(part from date)

Description

Get subfield from a date

Ports

extract
Functions
Date
All

example usage

extract('year' FROM DATE '1992-09-20')

Returns

1992
extract
extract

extract (part from interval)

Description

Get subfield from a date

Get subfield from a date

Expression

extract

(part from interval)

Description

Get subfield from a date

Ports

extract
Functions
Interval
All

example usage

extract('month' FROM INTERVAL '14 months')

Returns

2
extract
extract

extract (part from time)

Description

Get subfield from a time

Get subfield from a time

Expression

extract

(part from time)

Description

Get subfield from a time

Ports

extract
Functions
Time
All

example usage

extract('hour' FROM TIME '14:21:13')

Returns

14
extract
factorial

factorial (x)

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

factorial
Functions
Numeric
All

example usage

factorial(4)

Returns

24
factorial
floor

floor (x)

Description

rounds the number down

rounds the number down

Expression

floor

(x)

Description

rounds the number down

Ports

floor
Functions
Numeric
All

example usage

floor(17.4)

Returns

17
floor
format

format (format, parameters...)

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

format
Functions
Text
All

example usage

format('Benchmark "{}" took {} seconds', 'CSV', 42)

Returns

Benchmark "CSV" took 42 seconds
format
from_base64

from_base64 (string)

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

from_base64
Functions
Text
All

example usage

from_base64('QQ==')

Returns

A'
from_base64
gamma

gamma (x)

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

gamma
Functions
Numeric
All

example usage

gamma(5.5)

Returns

52.34277778
gamma
gen_random_uuid

gen_random_uuid ()

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

gen_random_uuid
Functions
Utility
All

example usage

gen_random_uuid()

Returns

various
gen_random_uuid
greatest

greatest (date, date)

Description

The later of two dates

The later of two dates

Expression

greatest

(date, date)

Description

The later of two dates

Ports

greatest
Functions
Date
All

example usage

greatest(DATE '1992-09-20', DATE '1992-03-07')

Returns

1992-09-20
greatest
greatest

greatest (timestamp, timestamp)

Description

The later of two timestamps

The later of two timestamps

Expression

greatest

(timestamp, timestamp)

Description

The later of two timestamps

Ports

greatest
Functions
Timestamp
All

example usage

greatest(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234')

Returns

1992-09-20 20:38:48
greatest
greatest

greatest (x1, x2, ...)

Description

selects the largest value

selects the largest value

Expression

greatest

(x1, x2, ...)

Description

selects the largest value

Ports

greatest
Functions
Numeric
All

example usage

greatest(3, 2, 4, 4)

Returns

4
greatest
hamming

hamming (string, string)

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

hamming
Functions
Text similarity
All

example usage

hamming('duck','luck')

Returns

1
hamming
hour

hour (date)

Description

Hours

Hours

Expression

hour

(date)

Description

Hours

Ports

hour
Functions
Date parts
All

example usage

hour(timestamp '2021-08-03 11:59:44.123456')

Returns

11
hour
icu_sort_key

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.

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

icu_sort_key
Functions
Utility
All

example usage

icu_sort_key('ö','DE')

Returns

460145960106
icu_sort_key
instr

instr (string, search_string)

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

instr
Functions
Text
All

example usage

instr('test test','es')

Returns

2
instr
isfinite

isfinite (date)

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

isfinite
Functions
Date
All

example usage

isfinite(DATE '1992-03-07')

Returns

TRUE
isfinite
isfinite

isfinite (timestamp)

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

isfinite
Functions
Timestamp
All

example usage

isfinite(TIMESTAMP '1992-03-07')

Returns

TRUE
isfinite
isfinite

isfinite (x)

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

isfinite
Functions
Numeric
All

example usage

isfinite(5.5)

Returns

TRUE
isfinite
isinf

isinf (date)

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

isinf
Functions
Date
All

example usage

isinf(DATE '-infinity')

Returns

TRUE
isinf
isinf

isinf (timestamp)

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

isinf
Functions
Timestamp
All

example usage

isinf(TIMESTAMP '-infinity')

Returns

TRUE
isinf
isinf

isinf (x)

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

isinf
Functions
Numeric
All

example usage

isinf(Infinity)

Returns

TRUE
isinf
isnan

isnan (x)

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

isnan
Functions
Numeric
All

example usage

isnan(NaN)

Returns

TRUE
isnan
isodow

isodow (date)

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

isodow
Functions
Date parts
All

example usage

isodow(date '1992-02-15')

Returns

6
isodow
isoyear

isoyear (date)

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

isoyear
Functions
Date parts
All

example usage

isoyear(date '2022-01-01')

Returns

2021
isoyear
jaccard

jaccard (string, string)

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

jaccard
Functions
Text similarity
All

example usage

jaccard('duck','luck')

Returns

0.6
jaccard
last_day

last_day (date)

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

last_day
Functions
Date
All

example usage

last_day(DATE '1992-09-20')

Returns

1992-09-30
last_day
last_day

last_day (timestamp)

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

last_day
Functions
Timestamp
All

example usage

last_day(TIMESTAMP '1992-03-22 01:02:03.1234')

Returns

1992-03-31
last_day
lcase

lcase (string)

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

lcase
Functions
Text
All

example usage

lcase('Hello')

Returns

hello
lcase
least

least (date, date)

Description

The earlier of two dates

The earlier of two dates

Expression

least

(date, date)

Description

The earlier of two dates

Ports

least
Functions
Date
All

example usage

least(DATE '1992-09-20', DATE '1992-03-07')

Returns

1992-03-07
least
least

least (timestamp, timestamp)

Description

The earlier of two timestamps

The earlier of two timestamps

Expression

least

(timestamp, timestamp)

Description

The earlier of two timestamps

Ports

least
Functions
Timestamp
All

example usage

least(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234')

Returns

1992-03-22 1:02:03
least
least

least (x1, x2, ...)

Description

selects the smallest value

selects the smallest value

Expression

least

(x1, x2, ...)

Description

selects the smallest value

Ports

least
Functions
Numeric
All

example usage

least(3, 2, 4, 4)

Returns

2
least
left

left (string, count)

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

left
Functions
Text
All

example usage

left('hello', 2)

Returns

he
left
len

len (list)

Description

Return the length of the list.

Return the length of the list.

Expression

len

(list)

Description

Return the length of the list.

Ports

len
Functions
Nested list
All

example usage

len([1, 2, 3])

Returns

3
len
length

length (string)

Description

Number of characters in string

Number of characters in string

Expression

length

(string)

Description

Number of characters in string

Ports

length
Functions
Text
All

example usage

length('Hello')

Returns

5
length
levenshtein

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.

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

levenshtein
Functions
Text similarity
All

example usage

levenshtein('duck','db')

Returns

3
levenshtein
lgamma

lgamma (x)

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

lgamma
Functions
Numeric
All

example usage

lgamma(2)

Returns

0
lgamma
like_escape

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.

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

like_escape
Functions
Text
All

example usage

like_escape('a%c', 'a$%c', '$')

Returns

TRUE
like_escape
list_aggr

list_aggr (list, name)

Description

Alias for list_aggregate.

Alias for list_aggregate.

Expression

list_aggr

(list, name)

Description

Alias for list_aggregate.

Ports

list_aggr
Functions
Nested list
All

example usage

list_aggr([1, 2, NULL], 'min')

Returns

1
list_aggr
list_aggregate

list_aggregate (list, name)

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

list_aggregate
Functions
Nested list
All

example usage

list_aggregate([1, 2, NULL], 'min')

Returns

1
list_aggregate
list_append

list_append (list, element)

Description

Appends element to list.

Appends element to list.

Expression

list_append

(list, element)

Description

Appends element to list.

Ports

list_append
Functions
Nested list
All

example usage

list_append([2, 3], 4)

Returns

[2, 3, 4]
list_append
list_cat

list_cat (list1, list2)

Description

Alias for list_concat.

Alias for list_concat.

Expression

list_cat

(list1, list2)

Description

Alias for list_concat.

Ports

list_cat
Functions
Nested list
All

example usage

list_cat([2, 3], [4, 5, 6])

Returns

[2, 3, 4, 5, 6]
list_cat
list_concat

list_concat (list1, list2)

Description

Concatenates two lists.

Concatenates two lists.

Expression

list_concat

(list1, list2)

Description

Concatenates two lists.

Ports

list_concat
Functions
Nested list
All

example usage

list_concat([2, 3], [4, 5, 6])

Returns

[2, 3, 4, 5, 6]
list_concat
list_contains

list_contains (list, element)

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

list_contains
Functions
Nested list
All

example usage

list_contains([1, 2, NULL], 1)

Returns

TRUE
list_contains
list_element

list_element (list, index)

Description

Alias for list_extract.

Alias for list_extract.

Expression

list_element

(list, index)

Description

Alias for list_extract.

Ports

list_element
Functions
Nested list
All

example usage

list_element([4, 5, 6], 3)

Returns

6
list_element
list_element

list_element (string, index)

Description

An alias for array_extract.

An alias for array_extract.

Expression

list_element

(string, index)

Description

An alias for array_extract.

Ports

list_element
Functions
Text
All

example usage

list_element('DuckDB', 2)

Returns

u'
list_element
list_extract

list_extract (list, index)

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

list_extract
Functions
Nested list
All

example usage

list_extract([4, 5, 6], 3)

Returns

6
list_extract
list_extract

list_extract (string, index)

Description

An alias for array_extract.

An alias for array_extract.

Expression

list_extract

(string, index)

Description

An alias for array_extract.

Ports

list_extract
Functions
Text
All

example usage

list_extract('DuckDB', 2)

Returns

u'
list_extract
list_has

list_has (list, element)

Description

Alias for list_contains.

Alias for list_contains.

Expression

list_has

(list, element)

Description

Alias for list_contains.

Ports

list_has
Functions
Nested list
All

example usage

list_has([1, 2, NULL], 1)

Returns

TRUE
list_has
list_indexof

list_indexof (list, element)

Description

Alias for list_position.

Alias for list_position.

Expression

list_indexof

(list, element)

Description

Alias for list_position.

Ports

list_indexof
Functions
Nested list
All

example usage

list_indexof([1, 2, NULL], 2)

Returns

2
list_indexof
list_pack

list_pack (any, ...)

Description

Alias for list_value.

Alias for list_value.

Expression

list_pack

(any, ...)

Description

Alias for list_value.

Ports

list_pack
Functions
Nested list
All

example usage

list_pack(4, 5, 6)

Returns

[4, 5, 6]
list_pack
list_position

list_position (list, element)

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

list_position
Functions
Nested list
All

example usage

list_contains([1, 2, NULL], 2)

Returns

2
list_position
list_prepend

list_prepend (element, list)

Description

Prepends element to list.

Prepends element to list.

Expression

list_prepend

(element, list)

Description

Prepends element to list.

Ports

list_prepend
Functions
Nested list
All

example usage

list_prepend(3, [4, 5, 6])

Returns

[3, 4, 5, 6]
list_prepend
list_reverse_sort

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.

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

list_reverse_sort
Functions
Nested list
All

example usage

list_reverse_sort([3, 6, 1, 2])

Returns

[6, 3, 2, 1]
list_reverse_sort
list_slice

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.

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

list_slice
Functions
Nested list
All

example usage

list_slice([4, 5, 6], 2, NULL)

Returns

[5, 6]
list_slice
list_sort

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.

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

list_sort
Functions
Nested list
All

example usage

list_sort([3, 6, 1, 2])

Returns

[1, 2, 3, 6]
list_sort
list_value

list_value (any, ...)

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

list_value
Functions
Nested list
All

example usage

list_value(4, 5, 6)

Returns

[4, 5, 6]
list_value
ln

ln (x)

Description

computes the natural logarithm of x

computes the natural logarithm of x

Expression

ln

(x)

Description

computes the natural logarithm of x

Ports

ln
Functions
Numeric
All

example usage

ln(2)

Returns

0.693
ln
log

log (x)

Description

computes the 10-log of x

computes the 10-log of x

Expression

log

(x)

Description

computes the 10-log of x

Ports

log
Functions
Numeric
All

example usage

log(100)

Returns

2
log
log10

log10 (x)

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

log10
Functions
Numeric
All

example usage

log10(1000)

Returns

3
log10
log2

log2 (x)

Description

computes the 2-log of x

computes the 2-log of x

Expression

log2

(x)

Description

computes the 2-log of x

Ports

log2
Functions
Numeric
All

example usage

log2(8)

Returns

3
log2
lower

lower (string)

Description

Convert string to lower case

Convert string to lower case

Expression

lower

(string)

Description

Convert string to lower case

Ports

lower
Functions
Text
All

example usage

lower('Hello')

Returns

hello
lower
lpad

lpad (string, count, character)

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

lpad
Functions
Text
All

example usage

lpad('hello', 10, '>')

Returns

>>>>>hello
lpad
ltrim

ltrim (string)

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

ltrim
Functions
Text
All

example usage

ltrim('␣␣␣␣test␣␣')

Returns

test␣␣
ltrim
ltrim

ltrim (string, characters)

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

ltrim
Functions
Text
All

example usage

ltrim('>>>>test<<', '><')

Returns

test<<
ltrim
make_date

make_date (bigint, bigint, bigint)

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

make_date
Functions
Date
All

example usage

make_date(1992, 9, 20)

Returns

1992-09-20
make_date
make_time

make_time (bigint, bigint, double)

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

make_time
Functions
Time
All

example usage

make_time(13, 34, 27.123456)

Returns

13:34:27
make_time
make_timestamp

make_timestamp (bigint, bigint, bigint, bigint, bigint, double)

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

make_timestamp
Functions
Timestamp
All

example usage

make_timestamp(1992, 9, 20, 13, 34, 27.123456)

Returns

1992-09-20 13:34:27
make_timestamp
map

map ()

Description

Returns an empty map.

Returns an empty map.

Expression

map

()

Description

Returns an empty map.

Ports

map
Functions
Nested map
All

example usage

map()

Returns

{}
map
map_extract

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.

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

map_extract
Functions
Nested map
All

example usage

map_extract(map([100, 5], [42, 43]),100)

Returns

[42]
map_extract
md5

md5 (string)

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

md5
Functions
Utility
All

example usage

md5('123')

Returns

202cb962ac59075b964b07152d234b70'
md5
md5

md5 (value)

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

md5
Functions
Text
All

example usage

md5('123')

Returns

202cb962ac59075b964b07152d234b70'
md5
microsecond

microsecond (date)

Description

Sub-minute microseconds

Sub-minute microseconds

Expression

microsecond

(date)

Description

Sub-minute microseconds

Ports

microsecond
Functions
Date parts
All

example usage

microsecond(timestamp '2021-08-03 11:59:44.123456')

Returns

44123456
microsecond
millennium

millennium (date)

Description

Millennium

Millennium

Expression

millennium

(date)

Description

Millennium

Ports

millennium
Functions
Date parts
All

example usage

millennium(date '1992-02-15')

Returns

2
millennium
millisecond

millisecond (date)

Description

Sub-minute milliseconds

Sub-minute milliseconds

Expression

millisecond

(date)

Description

Sub-minute milliseconds

Ports

millisecond
Functions
Date parts
All

example usage

millisecond(timestamp '2021-08-03 11:59:44.123456')

Returns

44123
millisecond
minute

minute (date)

Description

Minutes

Minutes

Expression

minute

(date)

Description

Minutes

Ports

minute
Functions
Date parts
All

example usage

minute(timestamp '2021-08-03 11:59:44.123456')

Returns

59
minute
mismatches

mismatches (string, string)

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

mismatches
Functions
Text similarity
All

example usage

mismatches('duck','luck')

Returns

1
mismatches
month

month (date)

Description

Month

Month

Expression

month

(date)

Description

Month

Ports

month
Functions
Date parts
All

example usage

month(date '1992-02-15')

Returns

2
month
monthname

monthname (date)

Description

The (English) name of the month

The (English) name of the month

Expression

monthname

(date)

Description

The (English) name of the month

Ports

monthname
Functions
Date
All

example usage

monthname(DATE '1992-09-20')

Returns

September
monthname
monthname

monthname (timestamp)

Description

The (English) name of the month.

The (English) name of the month.

Expression

monthname

(timestamp)

Description

The (English) name of the month.

Ports

monthname
Functions
Timestamp
All

example usage

monthname(TIMESTAMP '1992-09-20')

Returns

September
monthname
nextafter

nextafter (x, y)

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

nextafter
Functions
Numeric
All

example usage

nextafter(1::float, 2::float)

Returns

1.0000001
nextafter
nextval

nextval ('sequence_name')

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

nextval
Functions
Utility
All

example usage

nextval('my_sequence_name')

Returns

2
nextval
nfc_normalize

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.

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

nfc_normalize
Functions
Text
All

example usage

nfc_normalize('ardèch')

Returns

ardech
nfc_normalize
not_like_escape

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.

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

not_like_escape
Functions
Text
All

example usage

not_like_escape('a%c', 'a$%c', '$')

Returns

FALSE
not_like_escape
now

now ()

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

now
Functions
Timestamp
All

example usage

now()

Returns

TIMESTAMP 2022-08-02 08:32:20.014000
now
octet_length

octet_length (blob)

Description

Number of bytes in blob

Number of bytes in blob

Expression

octet_length

(blob)

Description

Number of bytes in blob

Ports

octet_length
Functions
Blob
All

example usage

octet_length('\xAABB'::BLOB)

Returns

2
octet_length
ord

ord (string)

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

ord
Functions
Text
All

example usage

ord('ü')

Returns

252
ord
pg_typeof

pg_typeof (expression)

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

pg_typeof
Functions
Utility
All

example usage

pg_typeof('abc')

Returns

varchar'
pg_typeof
pi

pi ()

Description

returns the value of pi

returns the value of pi

Expression

pi

()

Description

returns the value of pi

Ports

pi
Functions
Numeric
All

example usage

pi()

Returns

3.141592654
pi
position

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.

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

position
Functions
Text
All

example usage

position('b' in 'abc')

Returns

2
position
pow

pow (x, y)

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

pow
Functions
Numeric
All

example usage

pow(2, 3)

Returns

8
pow
power

power (x, y)

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

power
Functions
Numeric
All

example usage

power(2, 3)

Returns

8
power
prefix

prefix (string, search_string)

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

prefix
Functions
Text
All

example usage

prefix('abc', 'ab')

Returns

TRUE
prefix
printf

printf (format, parameters...)

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

printf
Functions
Text
All

example usage

printf('Benchmark "%s" took %d seconds', 'CSV', 42)

Returns

Benchmark "CSV" took 42 seconds
printf
quarter

quarter (date)

Description

Quarter

Quarter

Expression

quarter

(date)

Description

Quarter

Ports

quarter
Functions
Date parts
All

example usage

quarter(date '1992-02-15')

Returns

1
quarter
radians

radians (x)

Description

converts degrees to radians

converts degrees to radians

Expression

radians

(x)

Description

converts degrees to radians

Ports

radians
Functions
Numeric
All

example usage

radians(90)

Returns

1.570796327
radians
random

random ()

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

random
Functions
Numeric
All

example usage

random()

Returns

various
random
regexp_full_match

regexp_full_match (string, regex)

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

regexp_full_match
Functions
Text
All

example usage

regexp_full_match('anabanana', '(an)')

Returns

FALSE
regexp_full_match
regexp_matches

regexp_matches (string, regex)

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

regexp_matches
Functions
Text
All

example usage

regexp_matches('anabanana', '(an)')

Returns

TRUE
regexp_matches
regexp_replace

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

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

regexp_replace
Functions
Text
All

example usage

regexp_replace('hello', '[lo]', '-')

Returns

he-lo
regexp_replace
regexp_split_to_array

regexp_split_to_array (string, regex)

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

regexp_split_to_array
Functions
Text
All

example usage

regexp_split_to_array('hello␣world; 42', ';?␣')

Returns

['hello', 'world', '42']
regexp_split_to_array
repeat

repeat (string, count)

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

repeat
Functions
Text
All

example usage

repeat('A', 5)

Returns

AAAAA
repeat
replace

replace (string, source, target)

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

replace
Functions
Text
All

example usage

replace('hello', 'l', '-')

Returns

he--o
replace
reverse

reverse (string)

Description

Reverses the string

Reverses the string

Expression

reverse

(string)

Description

Reverses the string

Ports

reverse
Functions
Text
All

example usage

reverse('hello')

Returns

olleh
reverse
right

right (string, count)

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

right
Functions
Text
All

example usage

right('hello', 3)

Returns

llo
right
round

round (v numeric, s int)

Description

round to s decimal places

round to s decimal places

Expression

round

(v numeric, s int)

Description

round to s decimal places

Ports

round
Functions
Numeric
All

example usage

round(42.4332, 2)

Returns

42.43
round
row

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.

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

row
Functions
Nested struct
All

example usage

row(i, i % 4, i / 4)

Returns

{'i': 3, 'v2': 3, 'v3': 0}
row
rpad

rpad (string, count, character)

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

rpad
Functions
Text
All

example usage

rpad('hello', 10, '<')

Returns

hello<<<<<
rpad
rtrim

rtrim (string)

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

rtrim
Functions
Text
All

example usage

rtrim('␣␣␣␣test␣␣')

Returns

␣␣␣␣test
rtrim
rtrim

rtrim (string, characters)

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

rtrim
Functions
Text
All

example usage

rtrim('>>>>test<<', '><')

Returns

>>>>test
rtrim
second

second (date)

Description

Seconds

Seconds

Expression

second

(date)

Description

Seconds

Ports

second
Functions
Date parts
All

example usage

second(timestamp '2021-08-03 11:59:44.123456')

Returns

44
second
setseed

setseed (x)

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

setseed
Functions
Numeric
All

example usage

setseed(0.42)

Returns

setseed
sign

sign (x)

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

sign
Functions
Numeric
All

example usage

sign(-349)

Returns

-1
sign
sin

sin (x)

Description

computes the sin of x

computes the sin of x

Expression

sin

(x)

Description

computes the sin of x

Ports

sin
Functions
Numeric
All

example usage

sin(90)

Returns

0.8939966636
sin
sqrt

sqrt (x)

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

sqrt
Functions
Numeric
All

example usage

sqrt(9)

Returns

3
sqrt
stats

stats (expression)

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

stats
Functions
Utility
All

example usage

stats(5)

Returns

[Min: 5, Max: 5][Has Null: false]'
stats
str_split

str_split (string, separator)

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

str_split
Functions
Text
All

example usage

str_split('hello␣world', '␣')

Returns

['hello', 'world']
str_split
str_split_regex

str_split_regex (string, regex)

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

str_split_regex
Functions
Text
All

example usage

str_split_regex('hello␣world; 42', ';?␣')

Returns

['hello', 'world', '42']
str_split_regex
strftime

strftime (date, format)

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

strftime
Functions
Date
All

example usage

strftime(date '1992-01-01', '%a, %-d %B %Y')

Returns

Wed, 1 January 1992
strftime
strftime

strftime (timestamp, format)

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

strftime
Functions
Timestamp
All

example usage

strftime(timestamp '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p')

Returns

Wed, 1 January 1992 - 08:38:40 PM
strftime
string_split

string_split (string, separator)

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

string_split
Functions
Text
All

example usage

string_split('hello␣world', '␣')

Returns

['hello', 'world']
string_split
string_split_regex

string_split_regex (string, regex)

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

string_split_regex
Functions
Text
All

example usage

string_split_regex('hello␣world; 42', ';?␣')

Returns

['hello', 'world', '42']
string_split_regex
string_to_array

string_to_array (string, separator)

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

string_to_array
Functions
Text
All

example usage

string_to_array('hello␣world', '␣')

Returns

['hello', 'world']
string_to_array
strip_accents

strip_accents (string)

Description

Strips accents from string

Strips accents from string

Expression

strip_accents

(string)

Description

Strips accents from string

Ports

strip_accents
Functions
Text
All

example usage

strip_accents('mühleisen')

Returns

muhleisen
strip_accents
strlen

strlen (string)

Description

Number of bytes in string

Number of bytes in string

Expression

strlen

(string)

Description

Number of bytes in string

Ports

strlen
Functions
Text
All

example usage

strlen('a')

Returns

1
strlen
strpos

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.

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

strpos
Functions
Text
All

example usage

strpos('test test','es')

Returns

2
strpos
strptime

strptime (text, format)

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

strptime
Functions
Timestamp
All

example usage

strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')

Returns

1992-01-01 20:38:40
strptime
struct_extract

struct_extract (struct, 'entry')

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

struct_extract
Functions
Nested struct
All

example usage

struct_extract(s, 'i')

Returns

4
struct_extract
struct_pack

struct_pack (name := any, ...)

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

struct_pack
Functions
Nested struct
All

example usage

struct_pack(i := 4, s := 'string')

Returns

{'i': 3, 's': 'string'}
struct_pack
substr

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.

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

substr
Functions
Text
All

example usage

substr('Hello', 2, 2)

Returns

el
substr
substring

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.

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

substring
Functions
Text
All

example usage

substring('Hello', 2, 2)

Returns

el
substring
suffix

suffix (string, search_string)

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

suffix
Functions
Text
All

example usage

suffix('abc', 'bc')

Returns

TRUE
suffix
tan

tan (x)

Description

computes the tangent of x

computes the tangent of x

Expression

tan

(x)

Description

computes the tangent of x

Ports

tan
Functions
Numeric
All

example usage

tan(90)

Returns

-1.995200412
tan
timezone

timezone (date)

Description

Time Zone offset in minutes

Time Zone offset in minutes

Expression

timezone

(date)

Description

Time Zone offset in minutes

Ports

timezone
Functions
Date parts
All

example usage

timezone(date '1992-02-15')

Returns

0
timezone
timezone_hour

timezone_hour (date)

Description

Time zone offset hour portion

Time zone offset hour portion

Expression

timezone_hour

(date)

Description

Time zone offset hour portion

Ports

timezone_hour
Functions
Date parts
All

example usage

timezone_hour(date '1992-02-15')

Returns

0
timezone_hour
timezone_minute

timezone_minute (date)

Description

Time zone offset minutes portion

Time zone offset minutes portion

Expression

timezone_minute

(date)

Description

Time zone offset minutes portion

Ports

timezone_minute
Functions
Date parts
All

example usage

timezone_minute(date '1992-02-15')

Returns

0
timezone_minute
to_base64

to_base64 (blob)

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

to_base64
Functions
Text
All

example usage

to_base64('A'::blob)

Returns

QQ==
to_base64
to_days

to_days (integer)

Description

Construct a day interval

Construct a day interval

Expression

to_days

(integer)

Description

Construct a day interval

Ports

to_days
Functions
Interval
All

example usage

to_days(5)

Returns

INTERVAL 5 DAY
to_days
to_hours

to_hours (integer)