| Title: | Functions Provided by DuckDB |
|---|---|
| Description: | Lists DuckDB functions for integration in R's help system. |
| Authors: | Kirill Müller [aut, cre] (ORCID: <https://orcid.org/0000-0002-1416-3412>), cynkra GmbH [fnd, cph] (ROR: <https://ror.org/0335t7e62>) |
| Maintainer: | Kirill Müller <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.0.0.9000 |
| Built: | 2026-06-23 19:14:20 UTC |
| Source: | https://github.com/cynkra/dd |
DuckDB function /().
col0 |
|
col1 |
|
FLOAT | DOUBLE | INTERVAL
`/`(col0 = FLOAT, col1 = FLOAT)
`/`(col0 = DOUBLE, col1 = DOUBLE)
`/`(col0 = INTERVAL, col1 = DOUBLE)
Bitwise AND.
left |
|
right |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
`&`(left = TINYINT, right = TINYINT)
`&`(left = SMALLINT, right = SMALLINT)
`&`(left = INTEGER, right = INTEGER)
`&`(left = BIGINT, right = BIGINT)
`&`(left = HUGEINT, right = HUGEINT)
`&`(left = UTINYINT, right = UTINYINT)
`&`(left = USMALLINT, right = USMALLINT)
`&`(left = UINTEGER, right = UINTEGER)
`&`(left = UBIGINT, right = UBIGINT)
`&`(left = UHUGEINT, right = UHUGEINT)
`&`(left = BIT, right = BIT)
91 & 15
Bitwise shift left.
input |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
`<<`(input = TINYINT, col1 = TINYINT)
`<<`(input = SMALLINT, col1 = SMALLINT)
`<<`(input = INTEGER, col1 = INTEGER)
`<<`(input = BIGINT, col1 = BIGINT)
`<<`(input = HUGEINT, col1 = HUGEINT)
`<<`(input = UTINYINT, col1 = UTINYINT)
`<<`(input = USMALLINT, col1 = USMALLINT)
`<<`(input = UINTEGER, col1 = UINTEGER)
`<<`(input = UBIGINT, col1 = UBIGINT)
`<<`(input = UHUGEINT, col1 = UHUGEINT)
`<<`(input = BIT, col1 = INTEGER)
1 << 4
Bitwise shift right.
input |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
`>>`(input = TINYINT, col1 = TINYINT)
`>>`(input = SMALLINT, col1 = SMALLINT)
`>>`(input = INTEGER, col1 = INTEGER)
`>>`(input = BIGINT, col1 = BIGINT)
`>>`(input = HUGEINT, col1 = HUGEINT)
`>>`(input = UTINYINT, col1 = UTINYINT)
`>>`(input = USMALLINT, col1 = USMALLINT)
`>>`(input = UINTEGER, col1 = UINTEGER)
`>>`(input = UBIGINT, col1 = UBIGINT)
`>>`(input = UHUGEINT, col1 = UHUGEINT)
`>>`(input = BIT, col1 = INTEGER)
8 >> 2
Bitwise NOT.
input |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
`~`(input = TINYINT)
`~`(input = SMALLINT)
`~`(input = INTEGER)
`~`(input = BIGINT)
`~`(input = HUGEINT)
`~`(input = UTINYINT)
`~`(input = USMALLINT)
`~`(input = UINTEGER)
`~`(input = UBIGINT)
`~`(input = UHUGEINT)
`~`(input = BIT)
~15
DuckDB function ~~().
`~~`(col0 = VARCHAR, col1 = VARCHAR)`~~`(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
BOOLEAN
DuckDB function ~~*().
`~~*`(col0 = VARCHAR, col1 = VARCHAR)`~~*`(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
BOOLEAN
DuckDB function ~~~().
`~~~`(col0 = VARCHAR, col1 = VARCHAR)`~~~`(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
BOOLEAN
Absolute value.
x |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT
abs(x = TINYINT)
abs(x = SMALLINT)
abs(x = INTEGER)
abs(x = BIGINT)
abs(x = HUGEINT)
abs(x = FLOAT)
abs(x = DOUBLE)
abs(x = DECIMAL)
abs(x = UTINYINT)
abs(x = USMALLINT)
abs(x = UINTEGER)
abs(x = UBIGINT)
abs(x = UHUGEINT)
abs(-17.4)
Computes the arccosine of x.
acos(x = DOUBLE)acos(x = DOUBLE)
x |
|
DOUBLE
acos(0.5)
Computes the inverse hyperbolic cos of x.
acosh(x = DOUBLE)acosh(x = DOUBLE)
x |
|
DOUBLE
acosh(2.3)
DuckDB function add().
col0 |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | DATE | INTERVAL | TIMESTAMP | TIME | TIME WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | ANY[] | BIGNUM
add(col0 = TINYINT)
add(col0 = TINYINT, col1 = TINYINT)
add(col0 = SMALLINT)
add(col0 = SMALLINT, col1 = SMALLINT)
add(col0 = INTEGER)
add(col0 = INTEGER, col1 = INTEGER)
add(col0 = BIGINT)
add(col0 = BIGINT, col1 = BIGINT)
add(col0 = HUGEINT)
add(col0 = HUGEINT, col1 = HUGEINT)
add(col0 = FLOAT)
add(col0 = FLOAT, col1 = FLOAT)
add(col0 = DOUBLE)
add(col0 = DOUBLE, col1 = DOUBLE)
add(col0 = DECIMAL)
add(col0 = DECIMAL, col1 = DECIMAL)
add(col0 = UTINYINT)
add(col0 = UTINYINT, col1 = UTINYINT)
add(col0 = USMALLINT)
add(col0 = USMALLINT, col1 = USMALLINT)
add(col0 = UINTEGER)
add(col0 = UINTEGER, col1 = UINTEGER)
add(col0 = UBIGINT)
add(col0 = UBIGINT, col1 = UBIGINT)
add(col0 = UHUGEINT)
add(col0 = UHUGEINT, col1 = UHUGEINT)
add(col0 = DATE, col1 = INTEGER)
add(col0 = INTEGER, col1 = DATE)
add(col0 = INTERVAL, col1 = INTERVAL)
add(col0 = DATE, col1 = INTERVAL)
add(col0 = INTERVAL, col1 = DATE)
add(col0 = TIME, col1 = INTERVAL)
add(col0 = INTERVAL, col1 = TIME)
add(col0 = TIMESTAMP, col1 = INTERVAL)
add(col0 = INTERVAL, col1 = TIMESTAMP)
add(col0 = `TIME WITH TIME ZONE`, col1 = INTERVAL)
add(col0 = INTERVAL, col1 = `TIME WITH TIME ZONE`)
add(col0 = TIME, col1 = DATE)
add(col0 = DATE, col1 = TIME)
add(col0 = `TIME WITH TIME ZONE`, col1 = DATE)
add(col0 = DATE, col1 = `TIME WITH TIME ZONE`)
add()
add(col0 = BIGNUM, col1 = BIGNUM)
DuckDB function add_parquet_key().
add_parquet_key(col0 = VARCHAR, col1 = VARCHAR)add_parquet_key(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
Unspecified.
Subtract arguments, resulting in the time difference between the two timestamps.
timestamp |
|
INTERVAL
age(timestamp = TIMESTAMP)
age(timestamp = TIMESTAMP, timestamp = TIMESTAMP)
age(timestamp = `TIMESTAMP WITH TIME ZONE`)
age(timestamp = `TIMESTAMP WITH TIME ZONE`, timestamp = `TIMESTAMP WITH TIME ZONE`)
age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20')
DuckDB macro ago().
ago(i)ago(i)
i |
Unspecified. |
Unspecified.
Returns the name of a given expression.
alias(expr = ANY)alias(expr = ANY)
expr |
|
VARCHAR
alias(42 + 1)
DuckDB function all_profiling_output().
all_profiling_output()all_profiling_output()
Unspecified.
Returns the first non-NULL value from arg. This function is affected by ordering.
arg |
|
DECIMAL | ANY
any_value(arg = DECIMAL)
any_value(arg = ANY)
Computes the approximate count of distinct elements using HyperLogLog.
approx_count_distinct(any = ANY)approx_count_distinct(any = ANY)
any |
|
BIGINT
approx_count_distinct(A)
Computes the approximate quantile using T-Digest.
x |
|
pos |
|
DECIMAL | SMALLINT | INTEGER | BIGINT | HUGEINT | DOUBLE | DATE | TIME | TIME WITH TIME ZONE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | DECIMAL[] | TINYINT[] | SMALLINT[] | INTEGER[] | BIGINT[] | HUGEINT[] | FLOAT[] | DOUBLE[] | DATE[] | TIME[] | TIME WITH TIME ZONE[] | TIMESTAMP[] | TIMESTAMP WITH TIME ZONE[]
approx_quantile(x = DECIMAL, pos = FLOAT)
approx_quantile(x = SMALLINT, pos = FLOAT)
approx_quantile(x = INTEGER, pos = FLOAT)
approx_quantile(x = BIGINT, pos = FLOAT)
approx_quantile(x = HUGEINT, pos = FLOAT)
approx_quantile(x = DOUBLE, pos = FLOAT)
approx_quantile(x = DATE, pos = FLOAT)
approx_quantile(x = TIME, pos = FLOAT)
approx_quantile(x = `TIME WITH TIME ZONE`, pos = FLOAT)
approx_quantile(x = TIMESTAMP, pos = FLOAT)
approx_quantile(x = `TIMESTAMP WITH TIME ZONE`, pos = FLOAT)
approx_quantile(x = DECIMAL, pos = `FLOAT[]`)
approx_quantile(x = TINYINT, pos = `FLOAT[]`)
approx_quantile(x = SMALLINT, pos = `FLOAT[]`)
approx_quantile(x = INTEGER, pos = `FLOAT[]`)
approx_quantile(x = BIGINT, pos = `FLOAT[]`)
approx_quantile(x = HUGEINT, pos = `FLOAT[]`)
approx_quantile(x = FLOAT, pos = `FLOAT[]`)
approx_quantile(x = DOUBLE, pos = `FLOAT[]`)
approx_quantile(x = DATE, pos = `FLOAT[]`)
approx_quantile(x = TIME, pos = `FLOAT[]`)
approx_quantile(x = `TIME WITH TIME ZONE`, pos = `FLOAT[]`)
approx_quantile(x = TIMESTAMP, pos = `FLOAT[]`)
approx_quantile(x = `TIMESTAMP WITH TIME ZONE`, pos = `FLOAT[]`)
approx_quantile(x, 0.5)
Finds the k approximately most occurring values in the data set.
approx_top_k(val = ANY, k = BIGINT)approx_top_k(val = ANY, k = BIGINT)
val |
|
k |
|
ANY[]
approx_top_k(x, 5)
Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
arg |
|
val |
|
col2 |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY | ANY[]
arg_max(arg = INTEGER, val = INTEGER)
arg_max(arg = INTEGER, val = BIGINT)
arg_max(arg = INTEGER, val = HUGEINT)
arg_max(arg = INTEGER, val = DOUBLE)
arg_max(arg = INTEGER, val = VARCHAR)
arg_max(arg = INTEGER, val = DATE)
arg_max(arg = INTEGER, val = TIMESTAMP)
arg_max(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = INTEGER, val = BLOB)
arg_max(arg = BIGINT, val = INTEGER)
arg_max(arg = BIGINT, val = BIGINT)
arg_max(arg = BIGINT, val = HUGEINT)
arg_max(arg = BIGINT, val = DOUBLE)
arg_max(arg = BIGINT, val = VARCHAR)
arg_max(arg = BIGINT, val = DATE)
arg_max(arg = BIGINT, val = TIMESTAMP)
arg_max(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = BIGINT, val = BLOB)
arg_max(arg = DOUBLE, val = INTEGER)
arg_max(arg = DOUBLE, val = BIGINT)
arg_max(arg = DOUBLE, val = HUGEINT)
arg_max(arg = DOUBLE, val = DOUBLE)
arg_max(arg = DOUBLE, val = VARCHAR)
arg_max(arg = DOUBLE, val = DATE)
arg_max(arg = DOUBLE, val = TIMESTAMP)
arg_max(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = DOUBLE, val = BLOB)
arg_max(arg = VARCHAR, val = INTEGER)
arg_max(arg = VARCHAR, val = BIGINT)
arg_max(arg = VARCHAR, val = HUGEINT)
arg_max(arg = VARCHAR, val = DOUBLE)
arg_max(arg = VARCHAR, val = VARCHAR)
arg_max(arg = VARCHAR, val = DATE)
arg_max(arg = VARCHAR, val = TIMESTAMP)
arg_max(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = VARCHAR, val = BLOB)
arg_max(arg = DATE, val = INTEGER)
arg_max(arg = DATE, val = BIGINT)
arg_max(arg = DATE, val = HUGEINT)
arg_max(arg = DATE, val = DOUBLE)
arg_max(arg = DATE, val = VARCHAR)
arg_max(arg = DATE, val = DATE)
arg_max(arg = DATE, val = TIMESTAMP)
arg_max(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = DATE, val = BLOB)
arg_max(arg = TIMESTAMP, val = INTEGER)
arg_max(arg = TIMESTAMP, val = BIGINT)
arg_max(arg = TIMESTAMP, val = HUGEINT)
arg_max(arg = TIMESTAMP, val = DOUBLE)
arg_max(arg = TIMESTAMP, val = VARCHAR)
arg_max(arg = TIMESTAMP, val = DATE)
arg_max(arg = TIMESTAMP, val = TIMESTAMP)
arg_max(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = TIMESTAMP, val = BLOB)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_max(arg = BLOB, val = INTEGER)
arg_max(arg = BLOB, val = BIGINT)
arg_max(arg = BLOB, val = HUGEINT)
arg_max(arg = BLOB, val = DOUBLE)
arg_max(arg = BLOB, val = VARCHAR)
arg_max(arg = BLOB, val = DATE)
arg_max(arg = BLOB, val = TIMESTAMP)
arg_max(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = BLOB, val = BLOB)
arg_max(arg = DECIMAL, val = INTEGER)
arg_max(arg = DECIMAL, val = BIGINT)
arg_max(arg = DECIMAL, val = HUGEINT)
arg_max(arg = DECIMAL, val = DOUBLE)
arg_max(arg = DECIMAL, val = VARCHAR)
arg_max(arg = DECIMAL, val = DATE)
arg_max(arg = DECIMAL, val = TIMESTAMP)
arg_max(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = DECIMAL, val = BLOB)
arg_max(arg = ANY, val = INTEGER)
arg_max(arg = ANY, val = BIGINT)
arg_max(arg = ANY, val = HUGEINT)
arg_max(arg = ANY, val = DOUBLE)
arg_max(arg = ANY, val = VARCHAR)
arg_max(arg = ANY, val = DATE)
arg_max(arg = ANY, val = TIMESTAMP)
arg_max(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_max(arg = ANY, val = BLOB)
arg_max(arg = ANY, val = ANY)
arg_max(arg = ANY, val = ANY, col2 = BIGINT)
arg_max(A, B)
Finds the row with the maximum val. Calculates the arg expression at that row.
arg |
|
val |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY
arg_max_null(arg = INTEGER, val = INTEGER)
arg_max_null(arg = INTEGER, val = BIGINT)
arg_max_null(arg = INTEGER, val = HUGEINT)
arg_max_null(arg = INTEGER, val = DOUBLE)
arg_max_null(arg = INTEGER, val = VARCHAR)
arg_max_null(arg = INTEGER, val = DATE)
arg_max_null(arg = INTEGER, val = TIMESTAMP)
arg_max_null(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = INTEGER, val = BLOB)
arg_max_null(arg = BIGINT, val = INTEGER)
arg_max_null(arg = BIGINT, val = BIGINT)
arg_max_null(arg = BIGINT, val = HUGEINT)
arg_max_null(arg = BIGINT, val = DOUBLE)
arg_max_null(arg = BIGINT, val = VARCHAR)
arg_max_null(arg = BIGINT, val = DATE)
arg_max_null(arg = BIGINT, val = TIMESTAMP)
arg_max_null(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = BIGINT, val = BLOB)
arg_max_null(arg = DOUBLE, val = INTEGER)
arg_max_null(arg = DOUBLE, val = BIGINT)
arg_max_null(arg = DOUBLE, val = HUGEINT)
arg_max_null(arg = DOUBLE, val = DOUBLE)
arg_max_null(arg = DOUBLE, val = VARCHAR)
arg_max_null(arg = DOUBLE, val = DATE)
arg_max_null(arg = DOUBLE, val = TIMESTAMP)
arg_max_null(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = DOUBLE, val = BLOB)
arg_max_null(arg = VARCHAR, val = INTEGER)
arg_max_null(arg = VARCHAR, val = BIGINT)
arg_max_null(arg = VARCHAR, val = HUGEINT)
arg_max_null(arg = VARCHAR, val = DOUBLE)
arg_max_null(arg = VARCHAR, val = VARCHAR)
arg_max_null(arg = VARCHAR, val = DATE)
arg_max_null(arg = VARCHAR, val = TIMESTAMP)
arg_max_null(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = VARCHAR, val = BLOB)
arg_max_null(arg = DATE, val = INTEGER)
arg_max_null(arg = DATE, val = BIGINT)
arg_max_null(arg = DATE, val = HUGEINT)
arg_max_null(arg = DATE, val = DOUBLE)
arg_max_null(arg = DATE, val = VARCHAR)
arg_max_null(arg = DATE, val = DATE)
arg_max_null(arg = DATE, val = TIMESTAMP)
arg_max_null(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = DATE, val = BLOB)
arg_max_null(arg = TIMESTAMP, val = INTEGER)
arg_max_null(arg = TIMESTAMP, val = BIGINT)
arg_max_null(arg = TIMESTAMP, val = HUGEINT)
arg_max_null(arg = TIMESTAMP, val = DOUBLE)
arg_max_null(arg = TIMESTAMP, val = VARCHAR)
arg_max_null(arg = TIMESTAMP, val = DATE)
arg_max_null(arg = TIMESTAMP, val = TIMESTAMP)
arg_max_null(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = TIMESTAMP, val = BLOB)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_max_null(arg = BLOB, val = INTEGER)
arg_max_null(arg = BLOB, val = BIGINT)
arg_max_null(arg = BLOB, val = HUGEINT)
arg_max_null(arg = BLOB, val = DOUBLE)
arg_max_null(arg = BLOB, val = VARCHAR)
arg_max_null(arg = BLOB, val = DATE)
arg_max_null(arg = BLOB, val = TIMESTAMP)
arg_max_null(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = BLOB, val = BLOB)
arg_max_null(arg = DECIMAL, val = INTEGER)
arg_max_null(arg = DECIMAL, val = BIGINT)
arg_max_null(arg = DECIMAL, val = HUGEINT)
arg_max_null(arg = DECIMAL, val = DOUBLE)
arg_max_null(arg = DECIMAL, val = VARCHAR)
arg_max_null(arg = DECIMAL, val = DATE)
arg_max_null(arg = DECIMAL, val = TIMESTAMP)
arg_max_null(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = DECIMAL, val = BLOB)
arg_max_null(arg = ANY, val = INTEGER)
arg_max_null(arg = ANY, val = BIGINT)
arg_max_null(arg = ANY, val = HUGEINT)
arg_max_null(arg = ANY, val = DOUBLE)
arg_max_null(arg = ANY, val = VARCHAR)
arg_max_null(arg = ANY, val = DATE)
arg_max_null(arg = ANY, val = TIMESTAMP)
arg_max_null(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_null(arg = ANY, val = BLOB)
arg_max_null(arg = ANY, val = ANY)
arg_max_null(A, B)
Finds the rows with N maximum vals, including nulls. Calculates the arg expression at that row.
arg |
|
val |
|
N |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY | ANY[]
arg_max_nulls_last(arg = INTEGER, val = INTEGER)
arg_max_nulls_last(arg = INTEGER, val = BIGINT)
arg_max_nulls_last(arg = INTEGER, val = HUGEINT)
arg_max_nulls_last(arg = INTEGER, val = DOUBLE)
arg_max_nulls_last(arg = INTEGER, val = VARCHAR)
arg_max_nulls_last(arg = INTEGER, val = DATE)
arg_max_nulls_last(arg = INTEGER, val = TIMESTAMP)
arg_max_nulls_last(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = INTEGER, val = BLOB)
arg_max_nulls_last(arg = BIGINT, val = INTEGER)
arg_max_nulls_last(arg = BIGINT, val = BIGINT)
arg_max_nulls_last(arg = BIGINT, val = HUGEINT)
arg_max_nulls_last(arg = BIGINT, val = DOUBLE)
arg_max_nulls_last(arg = BIGINT, val = VARCHAR)
arg_max_nulls_last(arg = BIGINT, val = DATE)
arg_max_nulls_last(arg = BIGINT, val = TIMESTAMP)
arg_max_nulls_last(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = BIGINT, val = BLOB)
arg_max_nulls_last(arg = DOUBLE, val = INTEGER)
arg_max_nulls_last(arg = DOUBLE, val = BIGINT)
arg_max_nulls_last(arg = DOUBLE, val = HUGEINT)
arg_max_nulls_last(arg = DOUBLE, val = DOUBLE)
arg_max_nulls_last(arg = DOUBLE, val = VARCHAR)
arg_max_nulls_last(arg = DOUBLE, val = DATE)
arg_max_nulls_last(arg = DOUBLE, val = TIMESTAMP)
arg_max_nulls_last(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = DOUBLE, val = BLOB)
arg_max_nulls_last(arg = VARCHAR, val = INTEGER)
arg_max_nulls_last(arg = VARCHAR, val = BIGINT)
arg_max_nulls_last(arg = VARCHAR, val = HUGEINT)
arg_max_nulls_last(arg = VARCHAR, val = DOUBLE)
arg_max_nulls_last(arg = VARCHAR, val = VARCHAR)
arg_max_nulls_last(arg = VARCHAR, val = DATE)
arg_max_nulls_last(arg = VARCHAR, val = TIMESTAMP)
arg_max_nulls_last(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = VARCHAR, val = BLOB)
arg_max_nulls_last(arg = DATE, val = INTEGER)
arg_max_nulls_last(arg = DATE, val = BIGINT)
arg_max_nulls_last(arg = DATE, val = HUGEINT)
arg_max_nulls_last(arg = DATE, val = DOUBLE)
arg_max_nulls_last(arg = DATE, val = VARCHAR)
arg_max_nulls_last(arg = DATE, val = DATE)
arg_max_nulls_last(arg = DATE, val = TIMESTAMP)
arg_max_nulls_last(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = DATE, val = BLOB)
arg_max_nulls_last(arg = TIMESTAMP, val = INTEGER)
arg_max_nulls_last(arg = TIMESTAMP, val = BIGINT)
arg_max_nulls_last(arg = TIMESTAMP, val = HUGEINT)
arg_max_nulls_last(arg = TIMESTAMP, val = DOUBLE)
arg_max_nulls_last(arg = TIMESTAMP, val = VARCHAR)
arg_max_nulls_last(arg = TIMESTAMP, val = DATE)
arg_max_nulls_last(arg = TIMESTAMP, val = TIMESTAMP)
arg_max_nulls_last(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = TIMESTAMP, val = BLOB)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_max_nulls_last(arg = BLOB, val = INTEGER)
arg_max_nulls_last(arg = BLOB, val = BIGINT)
arg_max_nulls_last(arg = BLOB, val = HUGEINT)
arg_max_nulls_last(arg = BLOB, val = DOUBLE)
arg_max_nulls_last(arg = BLOB, val = VARCHAR)
arg_max_nulls_last(arg = BLOB, val = DATE)
arg_max_nulls_last(arg = BLOB, val = TIMESTAMP)
arg_max_nulls_last(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = BLOB, val = BLOB)
arg_max_nulls_last(arg = DECIMAL, val = INTEGER)
arg_max_nulls_last(arg = DECIMAL, val = BIGINT)
arg_max_nulls_last(arg = DECIMAL, val = HUGEINT)
arg_max_nulls_last(arg = DECIMAL, val = DOUBLE)
arg_max_nulls_last(arg = DECIMAL, val = VARCHAR)
arg_max_nulls_last(arg = DECIMAL, val = DATE)
arg_max_nulls_last(arg = DECIMAL, val = TIMESTAMP)
arg_max_nulls_last(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = DECIMAL, val = BLOB)
arg_max_nulls_last(arg = ANY, val = INTEGER)
arg_max_nulls_last(arg = ANY, val = BIGINT)
arg_max_nulls_last(arg = ANY, val = HUGEINT)
arg_max_nulls_last(arg = ANY, val = DOUBLE)
arg_max_nulls_last(arg = ANY, val = VARCHAR)
arg_max_nulls_last(arg = ANY, val = DATE)
arg_max_nulls_last(arg = ANY, val = TIMESTAMP)
arg_max_nulls_last(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_max_nulls_last(arg = ANY, val = BLOB)
arg_max_nulls_last(arg = ANY, val = ANY)
arg_max_nulls_last(arg = ANY, val = ANY, N = BIGINT)
arg_min_null_val(A, B, N)
Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
arg |
|
val |
|
col2 |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY | ANY[]
arg_min(arg = INTEGER, val = INTEGER)
arg_min(arg = INTEGER, val = BIGINT)
arg_min(arg = INTEGER, val = HUGEINT)
arg_min(arg = INTEGER, val = DOUBLE)
arg_min(arg = INTEGER, val = VARCHAR)
arg_min(arg = INTEGER, val = DATE)
arg_min(arg = INTEGER, val = TIMESTAMP)
arg_min(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = INTEGER, val = BLOB)
arg_min(arg = BIGINT, val = INTEGER)
arg_min(arg = BIGINT, val = BIGINT)
arg_min(arg = BIGINT, val = HUGEINT)
arg_min(arg = BIGINT, val = DOUBLE)
arg_min(arg = BIGINT, val = VARCHAR)
arg_min(arg = BIGINT, val = DATE)
arg_min(arg = BIGINT, val = TIMESTAMP)
arg_min(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = BIGINT, val = BLOB)
arg_min(arg = DOUBLE, val = INTEGER)
arg_min(arg = DOUBLE, val = BIGINT)
arg_min(arg = DOUBLE, val = HUGEINT)
arg_min(arg = DOUBLE, val = DOUBLE)
arg_min(arg = DOUBLE, val = VARCHAR)
arg_min(arg = DOUBLE, val = DATE)
arg_min(arg = DOUBLE, val = TIMESTAMP)
arg_min(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = DOUBLE, val = BLOB)
arg_min(arg = VARCHAR, val = INTEGER)
arg_min(arg = VARCHAR, val = BIGINT)
arg_min(arg = VARCHAR, val = HUGEINT)
arg_min(arg = VARCHAR, val = DOUBLE)
arg_min(arg = VARCHAR, val = VARCHAR)
arg_min(arg = VARCHAR, val = DATE)
arg_min(arg = VARCHAR, val = TIMESTAMP)
arg_min(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = VARCHAR, val = BLOB)
arg_min(arg = DATE, val = INTEGER)
arg_min(arg = DATE, val = BIGINT)
arg_min(arg = DATE, val = HUGEINT)
arg_min(arg = DATE, val = DOUBLE)
arg_min(arg = DATE, val = VARCHAR)
arg_min(arg = DATE, val = DATE)
arg_min(arg = DATE, val = TIMESTAMP)
arg_min(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = DATE, val = BLOB)
arg_min(arg = TIMESTAMP, val = INTEGER)
arg_min(arg = TIMESTAMP, val = BIGINT)
arg_min(arg = TIMESTAMP, val = HUGEINT)
arg_min(arg = TIMESTAMP, val = DOUBLE)
arg_min(arg = TIMESTAMP, val = VARCHAR)
arg_min(arg = TIMESTAMP, val = DATE)
arg_min(arg = TIMESTAMP, val = TIMESTAMP)
arg_min(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = TIMESTAMP, val = BLOB)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_min(arg = BLOB, val = INTEGER)
arg_min(arg = BLOB, val = BIGINT)
arg_min(arg = BLOB, val = HUGEINT)
arg_min(arg = BLOB, val = DOUBLE)
arg_min(arg = BLOB, val = VARCHAR)
arg_min(arg = BLOB, val = DATE)
arg_min(arg = BLOB, val = TIMESTAMP)
arg_min(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = BLOB, val = BLOB)
arg_min(arg = DECIMAL, val = INTEGER)
arg_min(arg = DECIMAL, val = BIGINT)
arg_min(arg = DECIMAL, val = HUGEINT)
arg_min(arg = DECIMAL, val = DOUBLE)
arg_min(arg = DECIMAL, val = VARCHAR)
arg_min(arg = DECIMAL, val = DATE)
arg_min(arg = DECIMAL, val = TIMESTAMP)
arg_min(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = DECIMAL, val = BLOB)
arg_min(arg = ANY, val = INTEGER)
arg_min(arg = ANY, val = BIGINT)
arg_min(arg = ANY, val = HUGEINT)
arg_min(arg = ANY, val = DOUBLE)
arg_min(arg = ANY, val = VARCHAR)
arg_min(arg = ANY, val = DATE)
arg_min(arg = ANY, val = TIMESTAMP)
arg_min(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_min(arg = ANY, val = BLOB)
arg_min(arg = ANY, val = ANY)
arg_min(arg = ANY, val = ANY, col2 = BIGINT)
arg_min(A, B)
Finds the row with the minimum val. Calculates the arg expression at that row.
arg |
|
val |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY
arg_min_null(arg = INTEGER, val = INTEGER)
arg_min_null(arg = INTEGER, val = BIGINT)
arg_min_null(arg = INTEGER, val = HUGEINT)
arg_min_null(arg = INTEGER, val = DOUBLE)
arg_min_null(arg = INTEGER, val = VARCHAR)
arg_min_null(arg = INTEGER, val = DATE)
arg_min_null(arg = INTEGER, val = TIMESTAMP)
arg_min_null(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = INTEGER, val = BLOB)
arg_min_null(arg = BIGINT, val = INTEGER)
arg_min_null(arg = BIGINT, val = BIGINT)
arg_min_null(arg = BIGINT, val = HUGEINT)
arg_min_null(arg = BIGINT, val = DOUBLE)
arg_min_null(arg = BIGINT, val = VARCHAR)
arg_min_null(arg = BIGINT, val = DATE)
arg_min_null(arg = BIGINT, val = TIMESTAMP)
arg_min_null(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = BIGINT, val = BLOB)
arg_min_null(arg = DOUBLE, val = INTEGER)
arg_min_null(arg = DOUBLE, val = BIGINT)
arg_min_null(arg = DOUBLE, val = HUGEINT)
arg_min_null(arg = DOUBLE, val = DOUBLE)
arg_min_null(arg = DOUBLE, val = VARCHAR)
arg_min_null(arg = DOUBLE, val = DATE)
arg_min_null(arg = DOUBLE, val = TIMESTAMP)
arg_min_null(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = DOUBLE, val = BLOB)
arg_min_null(arg = VARCHAR, val = INTEGER)
arg_min_null(arg = VARCHAR, val = BIGINT)
arg_min_null(arg = VARCHAR, val = HUGEINT)
arg_min_null(arg = VARCHAR, val = DOUBLE)
arg_min_null(arg = VARCHAR, val = VARCHAR)
arg_min_null(arg = VARCHAR, val = DATE)
arg_min_null(arg = VARCHAR, val = TIMESTAMP)
arg_min_null(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = VARCHAR, val = BLOB)
arg_min_null(arg = DATE, val = INTEGER)
arg_min_null(arg = DATE, val = BIGINT)
arg_min_null(arg = DATE, val = HUGEINT)
arg_min_null(arg = DATE, val = DOUBLE)
arg_min_null(arg = DATE, val = VARCHAR)
arg_min_null(arg = DATE, val = DATE)
arg_min_null(arg = DATE, val = TIMESTAMP)
arg_min_null(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = DATE, val = BLOB)
arg_min_null(arg = TIMESTAMP, val = INTEGER)
arg_min_null(arg = TIMESTAMP, val = BIGINT)
arg_min_null(arg = TIMESTAMP, val = HUGEINT)
arg_min_null(arg = TIMESTAMP, val = DOUBLE)
arg_min_null(arg = TIMESTAMP, val = VARCHAR)
arg_min_null(arg = TIMESTAMP, val = DATE)
arg_min_null(arg = TIMESTAMP, val = TIMESTAMP)
arg_min_null(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = TIMESTAMP, val = BLOB)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_min_null(arg = BLOB, val = INTEGER)
arg_min_null(arg = BLOB, val = BIGINT)
arg_min_null(arg = BLOB, val = HUGEINT)
arg_min_null(arg = BLOB, val = DOUBLE)
arg_min_null(arg = BLOB, val = VARCHAR)
arg_min_null(arg = BLOB, val = DATE)
arg_min_null(arg = BLOB, val = TIMESTAMP)
arg_min_null(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = BLOB, val = BLOB)
arg_min_null(arg = DECIMAL, val = INTEGER)
arg_min_null(arg = DECIMAL, val = BIGINT)
arg_min_null(arg = DECIMAL, val = HUGEINT)
arg_min_null(arg = DECIMAL, val = DOUBLE)
arg_min_null(arg = DECIMAL, val = VARCHAR)
arg_min_null(arg = DECIMAL, val = DATE)
arg_min_null(arg = DECIMAL, val = TIMESTAMP)
arg_min_null(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = DECIMAL, val = BLOB)
arg_min_null(arg = ANY, val = INTEGER)
arg_min_null(arg = ANY, val = BIGINT)
arg_min_null(arg = ANY, val = HUGEINT)
arg_min_null(arg = ANY, val = DOUBLE)
arg_min_null(arg = ANY, val = VARCHAR)
arg_min_null(arg = ANY, val = DATE)
arg_min_null(arg = ANY, val = TIMESTAMP)
arg_min_null(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_null(arg = ANY, val = BLOB)
arg_min_null(arg = ANY, val = ANY)
arg_min_null(A, B)
Finds the rows with N minimum vals, including nulls. Calculates the arg expression at that row.
arg |
|
val |
|
N |
|
INTEGER | BIGINT | DOUBLE | VARCHAR | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | BLOB | DECIMAL | ANY | ANY[]
arg_min_nulls_last(arg = INTEGER, val = INTEGER)
arg_min_nulls_last(arg = INTEGER, val = BIGINT)
arg_min_nulls_last(arg = INTEGER, val = HUGEINT)
arg_min_nulls_last(arg = INTEGER, val = DOUBLE)
arg_min_nulls_last(arg = INTEGER, val = VARCHAR)
arg_min_nulls_last(arg = INTEGER, val = DATE)
arg_min_nulls_last(arg = INTEGER, val = TIMESTAMP)
arg_min_nulls_last(arg = INTEGER, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = INTEGER, val = BLOB)
arg_min_nulls_last(arg = BIGINT, val = INTEGER)
arg_min_nulls_last(arg = BIGINT, val = BIGINT)
arg_min_nulls_last(arg = BIGINT, val = HUGEINT)
arg_min_nulls_last(arg = BIGINT, val = DOUBLE)
arg_min_nulls_last(arg = BIGINT, val = VARCHAR)
arg_min_nulls_last(arg = BIGINT, val = DATE)
arg_min_nulls_last(arg = BIGINT, val = TIMESTAMP)
arg_min_nulls_last(arg = BIGINT, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = BIGINT, val = BLOB)
arg_min_nulls_last(arg = DOUBLE, val = INTEGER)
arg_min_nulls_last(arg = DOUBLE, val = BIGINT)
arg_min_nulls_last(arg = DOUBLE, val = HUGEINT)
arg_min_nulls_last(arg = DOUBLE, val = DOUBLE)
arg_min_nulls_last(arg = DOUBLE, val = VARCHAR)
arg_min_nulls_last(arg = DOUBLE, val = DATE)
arg_min_nulls_last(arg = DOUBLE, val = TIMESTAMP)
arg_min_nulls_last(arg = DOUBLE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = DOUBLE, val = BLOB)
arg_min_nulls_last(arg = VARCHAR, val = INTEGER)
arg_min_nulls_last(arg = VARCHAR, val = BIGINT)
arg_min_nulls_last(arg = VARCHAR, val = HUGEINT)
arg_min_nulls_last(arg = VARCHAR, val = DOUBLE)
arg_min_nulls_last(arg = VARCHAR, val = VARCHAR)
arg_min_nulls_last(arg = VARCHAR, val = DATE)
arg_min_nulls_last(arg = VARCHAR, val = TIMESTAMP)
arg_min_nulls_last(arg = VARCHAR, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = VARCHAR, val = BLOB)
arg_min_nulls_last(arg = DATE, val = INTEGER)
arg_min_nulls_last(arg = DATE, val = BIGINT)
arg_min_nulls_last(arg = DATE, val = HUGEINT)
arg_min_nulls_last(arg = DATE, val = DOUBLE)
arg_min_nulls_last(arg = DATE, val = VARCHAR)
arg_min_nulls_last(arg = DATE, val = DATE)
arg_min_nulls_last(arg = DATE, val = TIMESTAMP)
arg_min_nulls_last(arg = DATE, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = DATE, val = BLOB)
arg_min_nulls_last(arg = TIMESTAMP, val = INTEGER)
arg_min_nulls_last(arg = TIMESTAMP, val = BIGINT)
arg_min_nulls_last(arg = TIMESTAMP, val = HUGEINT)
arg_min_nulls_last(arg = TIMESTAMP, val = DOUBLE)
arg_min_nulls_last(arg = TIMESTAMP, val = VARCHAR)
arg_min_nulls_last(arg = TIMESTAMP, val = DATE)
arg_min_nulls_last(arg = TIMESTAMP, val = TIMESTAMP)
arg_min_nulls_last(arg = TIMESTAMP, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = TIMESTAMP, val = BLOB)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = INTEGER)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = BIGINT)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = HUGEINT)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = DOUBLE)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = VARCHAR)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = DATE)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = TIMESTAMP)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = `TIMESTAMP WITH TIME ZONE`, val = BLOB)
arg_min_nulls_last(arg = BLOB, val = INTEGER)
arg_min_nulls_last(arg = BLOB, val = BIGINT)
arg_min_nulls_last(arg = BLOB, val = HUGEINT)
arg_min_nulls_last(arg = BLOB, val = DOUBLE)
arg_min_nulls_last(arg = BLOB, val = VARCHAR)
arg_min_nulls_last(arg = BLOB, val = DATE)
arg_min_nulls_last(arg = BLOB, val = TIMESTAMP)
arg_min_nulls_last(arg = BLOB, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = BLOB, val = BLOB)
arg_min_nulls_last(arg = DECIMAL, val = INTEGER)
arg_min_nulls_last(arg = DECIMAL, val = BIGINT)
arg_min_nulls_last(arg = DECIMAL, val = HUGEINT)
arg_min_nulls_last(arg = DECIMAL, val = DOUBLE)
arg_min_nulls_last(arg = DECIMAL, val = VARCHAR)
arg_min_nulls_last(arg = DECIMAL, val = DATE)
arg_min_nulls_last(arg = DECIMAL, val = TIMESTAMP)
arg_min_nulls_last(arg = DECIMAL, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = DECIMAL, val = BLOB)
arg_min_nulls_last(arg = ANY, val = INTEGER)
arg_min_nulls_last(arg = ANY, val = BIGINT)
arg_min_nulls_last(arg = ANY, val = HUGEINT)
arg_min_nulls_last(arg = ANY, val = DOUBLE)
arg_min_nulls_last(arg = ANY, val = VARCHAR)
arg_min_nulls_last(arg = ANY, val = DATE)
arg_min_nulls_last(arg = ANY, val = TIMESTAMP)
arg_min_nulls_last(arg = ANY, val = `TIMESTAMP WITH TIME ZONE`)
arg_min_nulls_last(arg = ANY, val = BLOB)
arg_min_nulls_last(arg = ANY, val = ANY)
arg_min_nulls_last(arg = ANY, val = ANY, N = BIGINT)
arg_min_null_val(A, B, N)
DuckDB macro array_append().
array_append(arr, el)array_append(arr, el)
arr |
Unspecified. |
el |
Unspecified. |
Unspecified.
Computes the cosine distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.
array1 |
|
array2 |
|
FLOAT | DOUBLE
array_cosine_distance(array1 = `FLOAT[ANY]`, array2 = `FLOAT[ANY]`)
array_cosine_distance(array1 = `DOUBLE[ANY]`, array2 = `DOUBLE[ANY]`)
array_cosine_distance(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_similarity(),
array_cross_product(),
array_distance(),
array_inner_product(),
array_negative_inner_product(),
array_value()
Computes the cosine similarity between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.
array1 |
|
array2 |
|
FLOAT | DOUBLE
array_cosine_similarity(array1 = `FLOAT[ANY]`, array2 = `FLOAT[ANY]`)
array_cosine_similarity(array1 = `DOUBLE[ANY]`, array2 = `DOUBLE[ANY]`)
array_cosine_similarity(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_distance(),
array_cross_product(),
array_distance(),
array_inner_product(),
array_negative_inner_product(),
array_value()
Computes the cross product of two arrays of size 3. The array elements can not be NULL.
array |
|
FLOAT[3] | DOUBLE[3]
array_cross_product(array = `FLOAT[3]`, array = `FLOAT[3]`)
array_cross_product(array = `DOUBLE[3]`, array = `DOUBLE[3]`)
array_cross_product(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_distance(),
array_cosine_similarity(),
array_distance(),
array_inner_product(),
array_negative_inner_product(),
array_value()
Computes the distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.
array1 |
|
array2 |
|
FLOAT | DOUBLE
array_distance(array1 = `FLOAT[ANY]`, array2 = `FLOAT[ANY]`)
array_distance(array1 = `DOUBLE[ANY]`, array2 = `DOUBLE[ANY]`)
array_distance(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_distance(),
array_cosine_similarity(),
array_cross_product(),
array_inner_product(),
array_negative_inner_product(),
array_value()
Extracts a single character from a string using a (1-based) index.
Extracts the named entry from the STRUCT.
Extracts the entry from an unnamed STRUCT (tuple) using an index (1-based).
Extracts the indexth (1-based) value from the list.
col0 |
|
col1 |
|
string |
|
index |
|
struct |
|
entry |
|
T | VARCHAR | ANY
array_extract(col0 = `T[]`, col1 = BIGINT)
array_extract(string = VARCHAR, index = BIGINT)
array_extract(struct = STRUCT, entry = VARCHAR)
array_extract(struct = STRUCT, index = BIGINT)
array_extract('DuckDB', 2)
array_extract({'i': 3, 'v2': 3, 'v3': 0}, 'i')
array_extract(row(42, 84), 1)
array_extract([4, 5, 6], 3)
Other list:
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Other string:
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Computes the inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.
array1 |
|
array2 |
|
FLOAT | DOUBLE
array_inner_product(array1 = `FLOAT[ANY]`, array2 = `FLOAT[ANY]`)
array_inner_product(array1 = `DOUBLE[ANY]`, array2 = `DOUBLE[ANY]`)
array_inner_product(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_distance(),
array_cosine_similarity(),
array_cross_product(),
array_distance(),
array_negative_inner_product(),
array_value()
Returns the length of the list.
array_length for lists with dimensions other than 1 not implemented.
list |
|
dimension |
|
BIGINT
array_length(list = `ANY[]`)
array_length(list = `ANY[]`, dimension = BIGINT)
array_length([1, 2, 3])
Other list:
array_extract(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Computes the negative inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.
array1 |
|
array2 |
|
FLOAT | DOUBLE
array_negative_inner_product(array1 = `FLOAT[ANY]`, array2 = `FLOAT[ANY]`)
array_negative_inner_product(array1 = `DOUBLE[ANY]`, array2 = `DOUBLE[ANY]`)
array_negative_inner_product(array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT), array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT))
Other array:
array_cosine_distance(),
array_cosine_similarity(),
array_cross_product(),
array_distance(),
array_inner_product(),
array_value()
DuckDB macro array_pop_back().
array_pop_back(arr)array_pop_back(arr)
arr |
Unspecified. |
Unspecified.
DuckDB macro array_pop_front().
array_pop_front(arr)array_pop_front(arr)
arr |
Unspecified. |
Unspecified.
DuckDB macro array_prepend().
array_prepend(el, arr)array_prepend(el, arr)
el |
Unspecified. |
arr |
Unspecified. |
Unspecified.
DuckDB macro array_push_back().
array_push_back(arr, e)array_push_back(arr, e)
arr |
Unspecified. |
e |
Unspecified. |
Unspecified.
DuckDB macro array_push_front().
array_push_front(arr, e)array_push_front(arr, e)
arr |
Unspecified. |
e |
Unspecified. |
Unspecified.
DuckDB macro array_reverse().
array_reverse(l)array_reverse(l)
l |
Unspecified. |
Unspecified.
DuckDB macro array_to_string().
array_to_string(arr, sep)array_to_string(arr, sep)
arr |
Unspecified. |
sep |
Unspecified. |
Unspecified.
DuckDB macro array_to_string_comma_default().
array_to_string_comma_default(arr, sep)array_to_string_comma_default(arr, sep)
arr |
Unspecified. |
sep |
Unspecified. |
Unspecified.
Creates an ARRAY containing the argument values.
array_value()array_value()
ARRAY
array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT)
Other array:
array_cosine_distance(),
array_cosine_similarity(),
array_cross_product(),
array_distance(),
array_inner_product(),
array_negative_inner_product()
DuckDB function arrow_scan().
arrow_scan(col0 = POINTER, col1 = POINTER, col2 = POINTER)arrow_scan(col0 = POINTER, col1 = POINTER, col2 = POINTER)
col0 |
|
col1 |
|
col2 |
|
Unspecified.
DuckDB function arrow_scan_dumb().
arrow_scan_dumb(col0 = POINTER, col1 = POINTER, col2 = POINTER)arrow_scan_dumb(col0 = POINTER, col1 = POINTER, col2 = POINTER)
col0 |
|
col1 |
|
col2 |
|
Unspecified.
Returns an integer that represents the Unicode code point of the first character of the string.
ascii(string = VARCHAR)ascii(string = VARCHAR)
string |
|
INTEGER
ascii('Ω')
Other string:
array_extract(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Computes the arcsine of x.
asin(x = DOUBLE)asin(x = DOUBLE)
x |
|
DOUBLE
asin(0.5)
Computes the inverse hyperbolic sin of x.
asinh(x = DOUBLE)asinh(x = DOUBLE)
x |
|
DOUBLE
asinh(0.5)
Computes the arctangent of x.
atan(x = DOUBLE)atan(x = DOUBLE)
x |
|
DOUBLE
atan(0.5)
Computes the arctangent (y, x).
atan2(y = DOUBLE, x = DOUBLE)atan2(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
atan2(1.0, 0.0)
Computes the inverse hyperbolic tan of x.
atanh(x = DOUBLE)atanh(x = DOUBLE)
x |
|
DOUBLE
atanh(0.5)
Calculates the average value for all tuples in x.
x |
|
DECIMAL | DOUBLE | INTERVAL | TIMESTAMP | TIMESTAMP WITH TIME ZONE | TIME | TIME WITH TIME ZONE
avg(x = DECIMAL)
avg(x = SMALLINT)
avg(x = INTEGER)
avg(x = BIGINT)
avg(x = HUGEINT)
avg(x = INTERVAL)
avg(x = DOUBLE)
avg(x = TIMESTAMP)
avg(x = `TIMESTAMP WITH TIME ZONE`)
avg(x = TIME)
avg(x = `TIME WITH TIME ZONE`)
SUM(x) / COUNT(*)
Draws a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80.
x |
|
min |
|
max |
|
width |
|
VARCHAR
bar(x = DOUBLE, min = DOUBLE, max = DOUBLE, width = DOUBLE)
bar(x = DOUBLE, min = DOUBLE, max = DOUBLE)
bar(5, 0, 20, 10)
Other string:
array_extract(),
ascii(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts the string to binary representation.
Converts the value to binary representation.
string |
|
value |
|
VARCHAR
bin(string = VARCHAR)
bin(value = BIGNUM)
bin(value = UBIGINT)
bin(value = BIGINT)
bin(value = HUGEINT)
bin(value = UHUGEINT)
bin('Aa')
bin(42)
Other numeric:
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns the bitwise AND of all bits in a given expression.
arg |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
bit_and(arg = TINYINT)
bit_and(arg = SMALLINT)
bit_and(arg = INTEGER)
bit_and(arg = BIGINT)
bit_and(arg = HUGEINT)
bit_and(arg = UTINYINT)
bit_and(arg = USMALLINT)
bit_and(arg = UINTEGER)
bit_and(arg = UBIGINT)
bit_and(arg = UHUGEINT)
bit_and(arg = BIT)
bit_and(A)
Returns the number of bits that are set.
x |
|
TINYINT | BIGINT
bit_count(x = TINYINT)
bit_count(x = SMALLINT)
bit_count(x = INTEGER)
bit_count(x = BIGINT)
bit_count(x = HUGEINT)
bit_count(x = BIT)
bit_count(31)
Number of bits in a string.
Returns the bit-length of the bit argument.
string |
|
bit |
|
BIGINT
bit_length(string = VARCHAR)
bit_length(bit = BIT)
bit_length('abc')
bit_length(42::TINYINT::BIT)
Other numeric:
bin(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns the bitwise OR of all bits in a given expression.
arg |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
bit_or(arg = TINYINT)
bit_or(arg = SMALLINT)
bit_or(arg = INTEGER)
bit_or(arg = BIGINT)
bit_or(arg = HUGEINT)
bit_or(arg = UTINYINT)
bit_or(arg = USMALLINT)
bit_or(arg = UINTEGER)
bit_or(arg = UBIGINT)
bit_or(arg = UHUGEINT)
bit_or(arg = BIT)
bit_or(A)
Returns first starting index of the specified substring within bits, or zero if it is not present. The first (leftmost) bit is indexed 1.
bit_position(substring = BIT, bitstring = BIT)bit_position(substring = BIT, bitstring = BIT)
substring |
|
bitstring |
|
INTEGER
bit_position('010'::BIT, '1110101'::BIT)
Returns the bitwise XOR of all bits in a given expression.
arg |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
bit_xor(arg = TINYINT)
bit_xor(arg = SMALLINT)
bit_xor(arg = INTEGER)
bit_xor(arg = BIGINT)
bit_xor(arg = HUGEINT)
bit_xor(arg = UTINYINT)
bit_xor(arg = USMALLINT)
bit_xor(arg = UINTEGER)
bit_xor(arg = UBIGINT)
bit_xor(arg = UHUGEINT)
bit_xor(arg = BIT)
bit_xor(A)
Pads the bitstring until the specified length.
bitstring |
|
length |
|
BIT
bitstring(bitstring = VARCHAR, length = INTEGER)
bitstring(bitstring = BIT, length = INTEGER)
bitstring('1010'::BIT, 7)
Returns a bitstring with bits set for each distinct value.
arg |
|
col1 |
|
col2 |
|
BIT
bitstring_agg(arg = TINYINT)
bitstring_agg(arg = TINYINT, col1 = TINYINT, col2 = TINYINT)
bitstring_agg(arg = SMALLINT)
bitstring_agg(arg = SMALLINT, col1 = SMALLINT, col2 = SMALLINT)
bitstring_agg(arg = INTEGER)
bitstring_agg(arg = INTEGER, col1 = INTEGER, col2 = INTEGER)
bitstring_agg(arg = BIGINT)
bitstring_agg(arg = BIGINT, col1 = BIGINT, col2 = BIGINT)
bitstring_agg(arg = HUGEINT)
bitstring_agg(arg = HUGEINT, col1 = HUGEINT, col2 = HUGEINT)
bitstring_agg(arg = UTINYINT)
bitstring_agg(arg = UTINYINT, col1 = UTINYINT, col2 = UTINYINT)
bitstring_agg(arg = USMALLINT)
bitstring_agg(arg = USMALLINT, col1 = USMALLINT, col2 = USMALLINT)
bitstring_agg(arg = UINTEGER)
bitstring_agg(arg = UINTEGER, col1 = UINTEGER, col2 = UINTEGER)
bitstring_agg(arg = UBIGINT)
bitstring_agg(arg = UBIGINT, col1 = UBIGINT, col2 = UBIGINT)
bitstring_agg(arg = UHUGEINT)
bitstring_agg(arg = UHUGEINT, col1 = UHUGEINT, col2 = UHUGEINT)
bitstring_agg(A)
Returns TRUE if every input value is TRUE, otherwise FALSE.
bool_and(arg = BOOLEAN)bool_and(arg = BOOLEAN)
arg |
|
BOOLEAN
bool_and(A)
Returns TRUE if any input value is TRUE, otherwise FALSE.
bool_or(arg = BOOLEAN)bool_or(arg = BOOLEAN)
arg |
|
BOOLEAN
bool_or(A)
Whether or not we can implicitly cast from the source type to the other type.
can_cast_implicitly(source_type = ANY, target_type = ANY)can_cast_implicitly(source_type = ANY, target_type = ANY)
source_type |
|
target_type |
|
BOOLEAN
can_cast_implicitly(NULL::INTEGER, NULL::BIGINT)
Returns the size of the map (or the number of entries in the map).
cardinality(map = ANY)cardinality(map = ANY)
map |
|
UBIGINT
cardinality( map([4, 2], ['a', 'b']) );
Casts the first argument to the type of the second argument.
cast_to_type(param = ANY, type = ANY)cast_to_type(param = ANY, type = ANY)
param |
|
type |
|
ANY
cast_to_type('42', NULL::INTEGER)
Returns the cube root of x.
cbrt(x = DOUBLE)cbrt(x = DOUBLE)
x |
|
DOUBLE
cbrt(8)
Rounds the number up.
x |
|
FLOAT | DOUBLE | DECIMAL
ceil(x = FLOAT)
ceil(x = DOUBLE)
ceil(x = DECIMAL)
ceil(17.4)
Extract the century component from a date or timestamp.
ts |
|
BIGINT
century(ts = DATE)
century(ts = INTERVAL)
century(ts = TIMESTAMP)
century(ts = `TIMESTAMP WITH TIME ZONE`)
century(timestamp '2021-08-03 11:59:44.123456')
DuckDB function checkpoint().
col0 |
|
Unspecified.
checkpoint()
checkpoint(col0 = VARCHAR)
Returns a character which is corresponding the ASCII code value or Unicode code point.
chr(code_point = INTEGER)chr(code_point = INTEGER)
code_point |
|
VARCHAR
chr(65)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB macro col_description().
col_description(table_oid, column_number)col_description(table_oid, column_number)
table_oid |
Unspecified. |
column_number |
Unspecified. |
Unspecified.
DuckDB function collations().
collations()collations()
Unspecified.
DuckDB function combine().
combine(col0 = `AGGREGATE_STATE<?>`, col1 = ANY)combine(col0 = `AGGREGATE_STATE<?>`, col1 = ANY)
col0 |
|
col1 |
|
AGGREGATE_STATE<?>
Concatenates multiple strings or lists. NULL inputs are skipped. See also operator ||.
concat(value = ANY)concat(value = ANY)
value |
|
ANY
concat('Hello', ' ', 'World')
concat([1, 2, 3], NULL, [4, 5, 6])
Other list:
array_extract(),
array_length(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Concatenates many strings, separated by separator. NULL inputs are skipped.
concat_ws(separator = VARCHAR, string = ANY)concat_ws(separator = VARCHAR, string = ANY)
separator |
|
string |
|
VARCHAR
concat_ws(', ', 'Banana', 'Apple', 'Melon')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
If arg2 is NULL, return NULL. Otherwise, return arg1.
constant_or_null(arg1 = ANY, arg2 = ANY)constant_or_null(arg1 = ANY, arg2 = ANY)
arg1 |
|
arg2 |
|
ANY
constant_or_null(42, NULL)
Returns true if search_string is found within string.
Returns true if the list contains the element.
Checks if a map contains a given key.
string |
|
search_string |
|
col0 |
|
col1 |
|
BOOLEAN
contains(string = VARCHAR, search_string = VARCHAR)
contains(col0 = `T[]`, col1 = T)
contains(col0 = `MAP(K, V)`, col1 = K)
contains(col0 = STRUCT, col1 = ANY)
contains('abc', 'a')
contains([1, 2, NULL], 1)
contains(MAP {'key1': 10, 'key2': 20, 'key3': 30}, 'key2')
Other list:
array_extract(),
array_length(),
concat(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function copy_database().
copy_database(col0 = VARCHAR, col1 = VARCHAR)copy_database(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
Unspecified.
Returns the correlation coefficient for non-NULL pairs in a group.
corr(y = DOUBLE, x = DOUBLE)corr(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y))
Computes the cos of x.
cos(x = DOUBLE)cos(x = DOUBLE)
x |
|
DOUBLE
cos(90)
Computes the hyperbolic cos of x.
cosh(x = DOUBLE)cosh(x = DOUBLE)
x |
|
DOUBLE
cosh(1)
Computes the cotangent of x.
cot(x = DOUBLE)cot(x = DOUBLE)
x |
|
DOUBLE
cot(0.5)
Returns the number of non-NULL values in arg.
arg |
|
BIGINT
count(arg = ANY)
count()
count(A)
Counts the total number of TRUE values for a boolean column.
count_if(arg = BOOLEAN)count_if(arg = BOOLEAN)
arg |
|
HUGEINT
count_if(A)
DuckDB function count_star().
count_star()count_star()
BIGINT
Returns the population covariance of input values.
covar_pop(y = DOUBLE, x = DOUBLE)covar_pop(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
(SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*)
Returns the sample covariance for non-NULL pairs in a group.
covar_samp(y = DOUBLE, x = DOUBLE)covar_samp(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
(SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1)
Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers.
create_sort_key(parameters... = ANY)create_sort_key(parameters... = ANY)
parameters... |
|
BLOB
create_sort_key('A', 'DESC')
DuckDB function cume_dist().
cume_dist()cume_dist()
DOUBLE
DuckDB macro current_catalog().
current_catalog()current_catalog()
Unspecified.
Get the current connection_id.
current_connection_id()current_connection_id()
UBIGINT
current_connection_id()
Returns the name of the currently active database.
current_database()current_database()
VARCHAR
current_database()
Returns the current query as a string.
current_query()current_query()
VARCHAR
current_query()
Get the current query_id.
current_query_id()current_query_id()
UBIGINT
current_query_id()
DuckDB macro current_role().
current_role()current_role()
Unspecified.
Returns the name of the currently active schema. Default is main.
current_schema()current_schema()
VARCHAR
current_schema()
Returns list of schemas. Pass a parameter of True to include implicit schemas.
include_implicit |
|
VARCHAR[]
current_schemas(include_implicit = BOOLEAN)
current_schemas(include_implicit)
current_schemas(true)
Returns the current value of the configuration setting.
current_setting(setting_name = VARCHAR)current_setting(setting_name = VARCHAR)
setting_name |
|
ANY
current_setting('access_mode')
Get the current global transaction_id.
current_transaction_id()current_transaction_id()
UBIGINT
current_transaction_id()
DuckDB macro current_user().
current_user()current_user()
Unspecified.
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
currval(`'sequence_name'` = VARCHAR)currval(`'sequence_name'` = VARCHAR)
'sequence_name' |
|
BIGINT
currval('my_sequence_name')
Extension of Levenshtein distance to also include transposition of adjacent characters as an allowed edit operation. In other words, the minimum number of edit operations (insertions, deletions, substitutions or transpositions) required to change one string to another. Characters of different cases (e.g., a and A) are considered different.
damerau_levenshtein(s1 = VARCHAR, s2 = VARCHAR)damerau_levenshtein(s1 = VARCHAR, s2 = VARCHAR)
s1 |
|
s2 |
|
BIGINT
damerau_levenshtein('duckdb', 'udckbd')
Other text_similarity:
hamming(),
jaccard(),
jaro_similarity(),
jaro_winkler_similarity(),
levenshtein()
DuckDB function database_list().
database_list()database_list()
Unspecified.
DuckDB function database_size().
database_size()database_size()
Unspecified.
DuckDB macro date_add().
date_add(date, interval)date_add(date, interval)
date |
Unspecified. |
interval |
Unspecified. |
Unspecified.
The number of partition boundaries between the timestamps.
part |
|
startdate |
|
enddate |
|
BIGINT
date_diff(part = VARCHAR, startdate = DATE, enddate = DATE)
date_diff(part = VARCHAR, startdate = TIME, enddate = TIME)
date_diff(part = VARCHAR, startdate = TIMESTAMP, enddate = TIMESTAMP)
date_diff(part = VARCHAR, startdate = `TIMESTAMP WITH TIME ZONE`, enddate = `TIMESTAMP WITH TIME ZONE`)
date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Get subfield (equivalent to extract).
ts |
|
col1 |
|
STRUCT() | BIGINT
date_part(ts = `VARCHAR[]`, col1 = DATE)
date_part(ts = `VARCHAR[]`, col1 = INTERVAL)
date_part(ts = `VARCHAR[]`, col1 = TIME)
date_part(ts = `VARCHAR[]`, col1 = TIMESTAMP)
date_part(ts = `VARCHAR[]`, col1 = `TIME WITH TIME ZONE`)
date_part(ts = `VARCHAR[]`, col1 = TIME_NS)
date_part(ts = VARCHAR, col1 = DATE)
date_part(ts = VARCHAR, col1 = INTERVAL)
date_part(ts = VARCHAR, col1 = TIME)
date_part(ts = VARCHAR, col1 = TIMESTAMP)
date_part(ts = VARCHAR, col1 = `TIME WITH TIME ZONE`)
date_part(ts = VARCHAR, col1 = TIME_NS)
date_part(ts = `VARCHAR[]`, col1 = `TIMESTAMP WITH TIME ZONE`)
date_part(ts = VARCHAR, col1 = `TIMESTAMP WITH TIME ZONE`)
date_part('minute', TIMESTAMP '1992-09-20 20:38:40')
The number of complete partitions between the timestamps.
part |
|
startdate |
|
enddate |
|
BIGINT
date_sub(part = VARCHAR, startdate = DATE, enddate = DATE)
date_sub(part = VARCHAR, startdate = TIME, enddate = TIME)
date_sub(part = VARCHAR, startdate = TIMESTAMP, enddate = TIMESTAMP)
date_sub(part = VARCHAR, startdate = `TIMESTAMP WITH TIME ZONE`, enddate = `TIMESTAMP WITH TIME ZONE`)
date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Truncate to specified precision.
part |
|
timestamp |
|
TIMESTAMP | INTERVAL | TIMESTAMP WITH TIME ZONE
date_trunc(part = VARCHAR, timestamp = DATE)
date_trunc(part = VARCHAR, timestamp = INTERVAL)
date_trunc(part = VARCHAR, timestamp = TIMESTAMP)
date_trunc(part = VARCHAR, timestamp = `TIMESTAMP WITH TIME ZONE`)
date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
Extract the day component from a date or timestamp.
ts |
|
BIGINT
day(ts = DATE)
day(ts = INTERVAL)
day(ts = TIMESTAMP)
day(ts = `TIMESTAMP WITH TIME ZONE`)
day(timestamp '2021-08-03 11:59:44.123456')
The (English) name of the weekday.
ts |
|
VARCHAR
dayname(ts = DATE)
dayname(ts = TIMESTAMP)
dayname(ts = `TIMESTAMP WITH TIME ZONE`)
dayname(TIMESTAMP '1992-03-22')
Extract the dayofmonth component from a date or timestamp.
ts |
|
BIGINT
dayofmonth(ts = DATE)
dayofmonth(ts = INTERVAL)
dayofmonth(ts = TIMESTAMP)
dayofmonth(ts = `TIMESTAMP WITH TIME ZONE`)
dayofmonth(timestamp '2021-08-03 11:59:44.123456')
Extract the dayofweek component from a date or timestamp.
ts |
|
BIGINT
dayofweek(ts = DATE)
dayofweek(ts = INTERVAL)
dayofweek(ts = TIMESTAMP)
dayofweek(ts = `TIMESTAMP WITH TIME ZONE`)
dayofweek(timestamp '2021-08-03 11:59:44.123456')
Extract the dayofyear component from a date or timestamp.
ts |
|
BIGINT
dayofyear(ts = DATE)
dayofyear(ts = INTERVAL)
dayofyear(ts = TIMESTAMP)
dayofyear(ts = `TIMESTAMP WITH TIME ZONE`)
dayofyear(timestamp '2021-08-03 11:59:44.123456')
DuckDB macro days_in_month().
days_in_month(date)days_in_month(date)
date |
Unspecified. |
Unspecified.
Extract the decade component from a date or timestamp.
ts |
|
BIGINT
decade(ts = DATE)
decade(ts = INTERVAL)
decade(ts = TIMESTAMP)
decade(ts = `TIMESTAMP WITH TIME ZONE`)
decade(timestamp '2021-08-03 11:59:44.123456')
Converts blob to VARCHAR. Invalid UTF-8 is handled based on the error behavior argument. Can be 'strict' (default, fail), 'replace' to replace invalid characters with '?', or 'ignore' to skip invalid characters.
blob |
|
varchar |
|
VARCHAR
decode(blob = BLOB)
decode(blob = BLOB, varchar = VARCHAR)
decode('\xC3\xBC'::BLOB)
decode('\xA0'::BLOB, 'replace')
decode('\xA0'::BLOB, 'ignore')
Other blob:
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Converts radians to degrees.
degrees(x = DOUBLE)degrees(x = DOUBLE)
x |
|
DOUBLE
degrees(pi())
DuckDB function dense_rank().
dense_rank()dense_rank()
BIGINT
DuckDB function disable_checkpoint_on_shutdown().
disable_checkpoint_on_shutdown()disable_checkpoint_on_shutdown()
Unspecified.
DuckDB function disable_logging().
disable_logging()disable_logging()
Unspecified.
DuckDB function disable_object_cache().
disable_object_cache()disable_object_cache()
Unspecified.
DuckDB function disable_optimizer().
disable_optimizer()disable_optimizer()
Unspecified.
DuckDB function disable_print_progress_bar().
disable_print_progress_bar()disable_print_progress_bar()
Unspecified.
DuckDB function disable_profile().
disable_profile()disable_profile()
Unspecified.
DuckDB function disable_profiling().
disable_profiling()disable_profiling()
Unspecified.
DuckDB function disable_progress_bar().
disable_progress_bar()disable_progress_bar()
Unspecified.
DuckDB function disable_verification().
disable_verification()disable_verification()
Unspecified.
DuckDB function disable_verify_external().
disable_verify_external()disable_verify_external()
Unspecified.
DuckDB function disable_verify_fetch_row().
disable_verify_fetch_row()disable_verify_fetch_row()
Unspecified.
DuckDB function disable_verify_parallelism().
disable_verify_parallelism()disable_verify_parallelism()
Unspecified.
DuckDB function disable_verify_serializer().
disable_verify_serializer()disable_verify_serializer()
Unspecified.
DuckDB function divide().
col0 |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT
divide(col0 = TINYINT, col1 = TINYINT)
divide(col0 = SMALLINT, col1 = SMALLINT)
divide(col0 = INTEGER, col1 = INTEGER)
divide(col0 = BIGINT, col1 = BIGINT)
divide(col0 = HUGEINT, col1 = HUGEINT)
divide(col0 = FLOAT, col1 = FLOAT)
divide(col0 = DOUBLE, col1 = DOUBLE)
divide(col0 = UTINYINT, col1 = UTINYINT)
divide(col0 = USMALLINT, col1 = USMALLINT)
divide(col0 = UINTEGER, col1 = UINTEGER)
divide(col0 = UBIGINT, col1 = UBIGINT)
divide(col0 = UHUGEINT, col1 = UHUGEINT)
DuckDB function duckdb_approx_database_count().
duckdb_approx_database_count()duckdb_approx_database_count()
Unspecified.
DuckDB function duckdb_columns().
duckdb_columns()duckdb_columns()
Unspecified.
DuckDB function duckdb_connection_count().
duckdb_connection_count()duckdb_connection_count()
Unspecified.
DuckDB function duckdb_constraints().
duckdb_constraints()duckdb_constraints()
Unspecified.
DuckDB function duckdb_coordinate_systems().
duckdb_coordinate_systems()duckdb_coordinate_systems()
Unspecified.
DuckDB function duckdb_databases().
duckdb_databases()duckdb_databases()
Unspecified.
DuckDB function duckdb_dependencies().
duckdb_dependencies()duckdb_dependencies()
Unspecified.
DuckDB function duckdb_extensions().
duckdb_extensions()duckdb_extensions()
Unspecified.
DuckDB function duckdb_external_file_cache().
duckdb_external_file_cache()duckdb_external_file_cache()
Unspecified.
DuckDB function duckdb_functions().
duckdb_functions()duckdb_functions()
Unspecified.
DuckDB function duckdb_indexes().
duckdb_indexes()duckdb_indexes()
Unspecified.
DuckDB function duckdb_keywords().
duckdb_keywords()duckdb_keywords()
Unspecified.
DuckDB function duckdb_log_contexts().
duckdb_log_contexts()duckdb_log_contexts()
Unspecified.
DuckDB function duckdb_logs().
duckdb_logs(denormalized_table = BOOLEAN)duckdb_logs(denormalized_table = BOOLEAN)
denormalized_table |
|
Unspecified.
DuckDB macro duckdb_logs_parsed().
duckdb_logs_parsed(log_type)duckdb_logs_parsed(log_type)
log_type |
Unspecified. |
Unspecified.
DuckDB function duckdb_memory().
duckdb_memory()duckdb_memory()
Unspecified.
DuckDB function duckdb_optimizers().
duckdb_optimizers()duckdb_optimizers()
Unspecified.
DuckDB function duckdb_prepared_statements().
duckdb_prepared_statements()duckdb_prepared_statements()
Unspecified.
DuckDB macro duckdb_profiling_settings().
duckdb_profiling_settings()duckdb_profiling_settings()
Unspecified.
DuckDB function duckdb_schemas().
duckdb_schemas()duckdb_schemas()
Unspecified.
DuckDB function duckdb_secret_types().
duckdb_secret_types()duckdb_secret_types()
Unspecified.
DuckDB function duckdb_secrets().
duckdb_secrets(redact = BOOLEAN)duckdb_secrets(redact = BOOLEAN)
redact |
|
Unspecified.
DuckDB function duckdb_sequences().
duckdb_sequences()duckdb_sequences()
Unspecified.
DuckDB function duckdb_settings().
duckdb_settings()duckdb_settings()
Unspecified.
DuckDB function duckdb_table_sample().
duckdb_table_sample(col0 = VARCHAR)duckdb_table_sample(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function duckdb_tables().
duckdb_tables()duckdb_tables()
Unspecified.
DuckDB function duckdb_temporary_files().
duckdb_temporary_files()duckdb_temporary_files()
Unspecified.
DuckDB function duckdb_types().
duckdb_types()duckdb_types()
Unspecified.
DuckDB function duckdb_variables().
duckdb_variables()duckdb_variables()
Unspecified.
DuckDB function duckdb_views().
duckdb_views()duckdb_views()
Unspecified.
DuckDB function enable_checkpoint_on_shutdown().
enable_checkpoint_on_shutdown()enable_checkpoint_on_shutdown()
Unspecified.
DuckDB function enable_object_cache().
enable_object_cache()enable_object_cache()
Unspecified.
DuckDB function enable_optimizer().
enable_optimizer()enable_optimizer()
Unspecified.
DuckDB function enable_print_progress_bar().
enable_print_progress_bar()enable_print_progress_bar()
Unspecified.
DuckDB function enable_profile().
enable_profile()enable_profile()
Unspecified.
DuckDB function enable_profiling().
mode |
|
metrics |
|
save_location |
|
coverage |
|
format |
|
Unspecified.
enable_profiling(mode = VARCHAR, metrics = ANY, save_location = VARCHAR, coverage = VARCHAR, format = VARCHAR)
enable_profiling()
DuckDB function enable_progress_bar().
enable_progress_bar()enable_progress_bar()
Unspecified.
DuckDB function enable_verification().
enable_verification()enable_verification()
Unspecified.
Converts the string to BLOB. Converts UTF-8 characters into literal encoding.
encode(string = VARCHAR)encode(string = VARCHAR)
string |
|
BLOB
encode('my_string_with_ü')
Other blob:
decode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Returns the log-2 entropy of count input-values.
entropy(x = ANY)entropy(x = ANY)
x |
|
DOUBLE
Returns the numeric value backing the given enum value.
enum_code(enum = ANY)enum_code(enum = ANY)
enum |
|
ANY
enum_code('happy'::mood)
Returns the first value of the input enum type.
enum_first(enum = ANY)enum_first(enum = ANY)
enum |
|
VARCHAR
enum_first(NULL::mood)
Returns the last value of the input enum type.
enum_last(enum = ANY)enum_last(enum = ANY)
enum |
|
VARCHAR
enum_last(NULL::mood)
Returns all values of the input enum type as an array.
enum_range(enum = ANY)enum_range(enum = ANY)
enum |
|
VARCHAR[]
enum_range(NULL::mood)
Returns the range between the two given enum values as an array. The values must be of the same enum type. When the first parameter is NULL, the result starts with the first value of the enum type. When the second parameter is NULL, the result ends with the last value of the enum type.
enum_range_boundary(start = ANY, end = ANY)enum_range_boundary(start = ANY, end = ANY)
start |
|
end |
|
VARCHAR[]
enum_range_boundary(NULL, 'happy'::mood)
Extract the epoch component from a temporal type.
temporal |
|
DOUBLE
epoch(temporal = DATE)
epoch(temporal = INTERVAL)
epoch(temporal = TIME)
epoch(temporal = TIMESTAMP)
epoch(temporal = `TIME WITH TIME ZONE`)
epoch(temporal = TIME_NS)
epoch(temporal = `TIMESTAMP WITH TIME ZONE`)
epoch(timestamp '2021-08-03 11:59:44.123456')
Extract the epoch component in milliseconds from a temporal type.
temporal |
|
BIGINT | TIMESTAMP
epoch_ms(temporal = DATE)
epoch_ms(temporal = TIMESTAMP)
epoch_ms(temporal = INTERVAL)
epoch_ms(temporal = TIME)
epoch_ms(temporal = TIME_NS)
epoch_ms(temporal = `TIME WITH TIME ZONE`)
epoch_ms(temporal = `TIMESTAMP WITH TIME ZONE`)
epoch_ms(temporal = BIGINT)
epoch_ms(timestamp '2021-08-03 11:59:44.123456')
Extract the epoch component in nanoseconds from a temporal type.
temporal |
|
BIGINT
epoch_ns(temporal = DATE)
epoch_ns(temporal = TIMESTAMP)
epoch_ns(temporal = INTERVAL)
epoch_ns(temporal = TIME)
epoch_ns(temporal = TIME_NS)
epoch_ns(temporal = `TIME WITH TIME ZONE`)
epoch_ns(temporal = `TIMESTAMP WITH TIME ZONE`)
epoch_ns(temporal = TIMESTAMP_NS)
epoch_ns(timestamp '2021-08-03 11:59:44.123456')
Extract the epoch component in microseconds from a temporal type.
temporal |
|
BIGINT
epoch_us(temporal = DATE)
epoch_us(temporal = TIMESTAMP)
epoch_us(temporal = INTERVAL)
epoch_us(temporal = TIME)
epoch_us(temporal = TIME_NS)
epoch_us(temporal = `TIME WITH TIME ZONE`)
epoch_us(temporal = `TIMESTAMP WITH TIME ZONE`)
epoch_us(timestamp '2021-08-03 11:59:44.123456')
Generates bin_count equi-width bins between the min and max. If enabled nice_rounding makes the numbers more readable/less jagged.
min |
|
max |
|
bin_count |
|
nice_rounding |
|
ANY[]
equi_width_bins(min = BIGINT, max = BIGINT, bin_count = BIGINT, nice_rounding = BOOLEAN)
equi_width_bins(min = DOUBLE, max = DOUBLE, bin_count = BIGINT, nice_rounding = BOOLEAN)
equi_width_bins(min = TIMESTAMP, max = TIMESTAMP, bin_count = BIGINT, nice_rounding = BOOLEAN)
equi_width_bins(min = ANY, max = ANY, bin_count = BIGINT, nice_rounding = BOOLEAN)
equi_width_bins(0, 10, 2, true)
Extract the era component from a date or timestamp.
ts |
|
BIGINT
era(ts = DATE)
era(ts = INTERVAL)
era(ts = TIMESTAMP)
era(ts = `TIMESTAMP WITH TIME ZONE`)
era(timestamp '2021-08-03 11:59:44.123456')
Throws the given error message.
error(message = VARCHAR)error(message = VARCHAR)
message |
|
"NULL"
error('access_mode')
Rounds x to next even number by rounding away from zero.
even(x = DOUBLE)even(x = DOUBLE)
x |
|
DOUBLE
even(2.9)
Computes e to the power of x.
exp(x = DOUBLE)exp(x = DOUBLE)
x |
|
DOUBLE
exp(1)
DuckDB function extension_versions().
extension_versions()extension_versions()
Unspecified.
Factorial of x. Computes the product of the current integer and all integers below it.
factorial(x = INTEGER)factorial(x = INTEGER)
x |
|
HUGEINT
4!
Calculates the average using a more accurate floating point summation (Kahan Sum).
favg(x = DOUBLE)favg(x = DOUBLE)
x |
|
DOUBLE
favg(A)
DuckDB macro fdiv().
fdiv(x, y)fdiv(x, y)
x |
Unspecified. |
y |
Unspecified. |
Unspecified.
DuckDB function fill().
fill(col0 = T)fill(col0 = T)
col0 |
|
T
DuckDB function finalize().
finalize(col0 = `AGGREGATE_STATE<?>`)finalize(col0 = `AGGREGATE_STATE<?>`)
col0 |
|
INVALID
Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
arg |
|
col0 |
|
DECIMAL | ANY | T
first(arg = DECIMAL)
first(arg = ANY)
first(col0 = T)
first(A)
DuckDB function first_value().
first_value(col0 = T)first_value(col0 = T)
col0 |
|
T
Flattens a nested list by one level.
flatten(nested_list = `T[][]`)flatten(nested_list = `T[][]`)
nested_list |
|
T[]
flatten([[1, 2, 3], [4, 5]])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Rounds the number down.
x |
|
FLOAT | DOUBLE | DECIMAL
floor(x = FLOAT)
floor(x = DOUBLE)
floor(x = DECIMAL)
floor(17.4)
DuckDB macro fmod().
fmod(x, y)fmod(x, y)
x |
Unspecified. |
y |
Unspecified. |
Unspecified.
DuckDB function force_checkpoint().
col0 |
|
Unspecified.
force_checkpoint()
force_checkpoint(col0 = VARCHAR)
Converts integer to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).
format_bytes(integer = BIGINT)format_bytes(integer = BIGINT)
integer |
|
VARCHAR
format_bytes(16_000)
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
greatest(),
hex(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB macro format_pg_type().
format_pg_type(logical_type, type_name)format_pg_type(logical_type, type_name)
logical_type |
Unspecified. |
type_name |
Unspecified. |
Unspecified.
DuckDB macro format_type().
format_type(type_oid, typemod)format_type(type_oid, typemod)
type_oid |
Unspecified. |
typemod |
Unspecified. |
Unspecified.
Converts integer to a human-readable representation using units based on powers of 10 (KB, MB, GB, etc.).
formatReadableDecimalSize(integer = BIGINT)formatReadableDecimalSize(integer = BIGINT)
integer |
|
VARCHAR
formatReadableDecimalSize(16_000)
Other numeric:
bin(),
bit_length(),
format_bytes(),
greatest(),
hex(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts a base64 encoded string to a character string (BLOB).
from_base64(string = VARCHAR)from_base64(string = VARCHAR)
string |
|
BLOB
from_base64('QQ==')
Other blob:
decode(),
encode(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function functions().
functions()functions()
Unspecified.
Interpolation of (x-1) factorial (so decimal inputs are allowed).
gamma(x = DOUBLE)gamma(x = DOUBLE)
x |
|
DOUBLE
gamma(5.5)
Creates a list of values between start and stop - the stop parameter is inclusive.
col0 |
|
col1 |
|
col2 |
|
start |
|
stop |
|
step |
|
BIGINT[] | TIMESTAMP[] | TIMESTAMP WITH TIME ZONE[]
generate_series(col0 = BIGINT)
generate_series(col0 = BIGINT, col1 = BIGINT)
generate_series(col0 = BIGINT, col1 = BIGINT, col2 = BIGINT)
generate_series(col0 = TIMESTAMP, col1 = TIMESTAMP, col2 = INTERVAL)
generate_series(start = BIGINT)
generate_series(start = BIGINT, stop = BIGINT)
generate_series(start = BIGINT, stop = BIGINT, step = BIGINT)
generate_series(start = TIMESTAMP, stop = TIMESTAMP, step = INTERVAL)
generate_series(start = `TIMESTAMP WITH TIME ZONE`, stop = `TIMESTAMP WITH TIME ZONE`, step = INTERVAL)
generate_series(2, 5, 3)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro generate_subscripts().
generate_subscripts(arr, dim)generate_subscripts(arr, dim)
arr |
Unspecified. |
dim |
Unspecified. |
Unspecified.
DuckDB macro geomean().
geomean(x)geomean(x)
x |
Unspecified. |
Unspecified.
DuckDB macro geometric_mean().
geometric_mean(x)geometric_mean(x)
x |
Unspecified. |
Unspecified.
Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0.
get_bit(bitstring = BIT, index = INTEGER)get_bit(bitstring = BIT, index = INTEGER)
bitstring |
|
index |
|
INTEGER
get_bit('0110010'::BIT, 2)
DuckDB macro get_block_size().
get_block_size(db_name)get_block_size(db_name)
db_name |
Unspecified. |
Unspecified.
Returns the current timestamp.
get_current_timestamp()get_current_timestamp()
TIMESTAMP WITH TIME ZONE
get_current_timestamp()
Returns the type of the result of the expression.
get_type(expression = ANY)get_type(expression = ANY)
expression |
|
TYPE
get_type('abc')
DuckDB function getvariable().
getvariable(col0 = VARCHAR)getvariable(col0 = VARCHAR)
col0 |
|
ANY
DuckDB function glob().
col0 |
|
Unspecified.
glob(col0 = VARCHAR)
glob(col0 = `VARCHAR[]`)
Returns the largest value. For strings lexicographical ordering is used. Note that lowercase characters are considered “larger” than uppercase characters and collations are not supported.
greatest(arg1 = ANY)greatest(arg1 = ANY)
arg1 |
|
ANY
greatest(42, 84)
greatest('abc', 'bcd', 'cde', 'EFG')
Other aggregate:
least()
Other date:
least()
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
hex(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Other timestamp:
least()
Computes the greatest common divisor of x and y.
x |
|
y |
|
BIGINT | HUGEINT
greatest_common_divisor(x = BIGINT, y = BIGINT)
greatest_common_divisor(x = HUGEINT, y = HUGEINT)
greatest_common_divisor(42, 57)
The Hamming distance between to strings, i.e., the number of positions with different characters for two strings of equal length. Strings must be of equal length. Characters of different cases (e.g., a and A) are considered different.
hamming(s1 = VARCHAR, s2 = VARCHAR)hamming(s1 = VARCHAR, s2 = VARCHAR)
s1 |
|
s2 |
|
BIGINT
hamming('duck', 'luck')
Other text_similarity:
damerau_levenshtein(),
jaccard(),
jaro_similarity(),
jaro_winkler_similarity(),
levenshtein()
DuckDB function has_any_column_privilege().
table |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_any_column_privilege(table, privilege)
has_any_column_privilege(user, table, privilege)
DuckDB function has_column_privilege().
table |
Unspecified. |
column |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_column_privilege(table, column, privilege)
has_column_privilege(user, table, column, privilege)
DuckDB function has_database_privilege().
database |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_database_privilege(database, privilege)
has_database_privilege(user, database, privilege)
DuckDB function has_foreign_data_wrapper_privilege().
fdw |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_foreign_data_wrapper_privilege(fdw, privilege)
has_foreign_data_wrapper_privilege(user, fdw, privilege)
DuckDB function has_function_privilege().
function |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_function_privilege(`function`, privilege)
has_function_privilege(user, `function`, privilege)
DuckDB function has_language_privilege().
language |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_language_privilege(language, privilege)
has_language_privilege(user, language, privilege)
DuckDB function has_schema_privilege().
schema |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_schema_privilege(schema, privilege)
has_schema_privilege(user, schema, privilege)
DuckDB function has_sequence_privilege().
sequence |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_sequence_privilege(sequence, privilege)
has_sequence_privilege(user, sequence, privilege)
DuckDB function has_server_privilege().
server |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_server_privilege(server, privilege)
has_server_privilege(user, server, privilege)
DuckDB function has_table_privilege().
table |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_table_privilege(table, privilege)
has_table_privilege(user, table, privilege)
DuckDB function has_tablespace_privilege().
tablespace |
Unspecified. |
privilege |
Unspecified. |
user |
Unspecified. |
Unspecified.
has_tablespace_privilege(tablespace, privilege)
has_tablespace_privilege(user, tablespace, privilege)
Returns a UBIGINT with the hash of the value. Note that this is not a cryptographic hash.
hash(value = ANY)hash(value = ANY)
value |
|
UBIGINT
hash('🦆')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts the string to hexadecimal representation.
Converts the value to VARCHAR using hexadecimal representation.
Converts blob to VARCHAR using hexadecimal encoding.
string |
|
value |
|
blob |
|
VARCHAR
hex(string = VARCHAR)
hex(value = BIGNUM)
hex(blob = BLOB)
hex(value = BIGINT)
hex(value = UBIGINT)
hex(value = HUGEINT)
hex(value = UHUGEINT)
hex('Hello')
hex(42)
hex('\xAA\xBB'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
least(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns a LIST of STRUCTs with the fields bucket and count.
arg |
|
col1 |
|
source |
Unspecified. |
col_name |
Unspecified. |
bin_count |
Unspecified. |
technique |
Unspecified. |
MAP
histogram(arg = ANY, col1 = `ANY[]`)
histogram(arg = ANY)
histogram(source, col_name, bin_count, technique)
histogram(A)
Returns a LIST of STRUCTs with the fields bucket and count matching the buckets exactly.
histogram_exact(arg = ANY, bins = `ANY[]`)histogram_exact(arg = ANY, bins = `ANY[]`)
arg |
|
bins |
|
MAP
histogram_exact(A, [0, 1, 2])
DuckDB macro histogram_values().
histogram_values(source, col_name, bin_count, technique)histogram_values(source, col_name, bin_count, technique)
source |
Unspecified. |
col_name |
Unspecified. |
bin_count |
Unspecified. |
technique |
Unspecified. |
Unspecified.
Extract the hour component from a date or timestamp.
ts |
|
BIGINT
hour(ts = DATE)
hour(ts = INTERVAL)
hour(ts = TIME)
hour(ts = TIMESTAMP)
hour(ts = `TIME WITH TIME ZONE`)
hour(ts = TIME_NS)
hour(ts = `TIMESTAMP WITH TIME ZONE`)
hour(timestamp '2021-08-03 11:59:44.123456')
Returns true if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.
ilike_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)ilike_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)
string |
|
like_specifier |
|
escape_character |
|
BOOLEAN
ilike_escape('A%c', 'a$%C', '$')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function import_database().
import_database(col0 = VARCHAR)import_database(col0 = VARCHAR)
col0 |
|
Unspecified.
Returns whether or not the database/schema are in the search path.
in_search_path(database_name = VARCHAR, schema_name = VARCHAR)in_search_path(database_name = VARCHAR, schema_name = VARCHAR)
database_name |
|
schema_name |
|
BOOLEAN
in_search_path('memory', 'main')
DuckDB macro inet_client_addr().
inet_client_addr()inet_client_addr()
Unspecified.
DuckDB macro inet_client_port().
inet_client_port()inet_client_port()
Unspecified.
DuckDB macro inet_server_addr().
inet_server_addr()inet_server_addr()
Unspecified.
DuckDB macro inet_server_port().
inet_server_port()inet_server_port()
Unspecified.
Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
instr(string = VARCHAR, search_string = VARCHAR)instr(string = VARCHAR, search_string = VARCHAR)
string |
|
search_string |
|
BIGINT
instr('test test', 'es')
position('b' IN 'abc')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Whether or not the provided value is the histogram "other" bin (used for values not belonging to any provided bin).
is_histogram_other_bin(val = ANY)is_histogram_other_bin(val = ANY)
val |
|
BOOLEAN
is_histogram_other_bin(v)
Returns true if the floating point value is finite, false otherwise.
x |
|
BOOLEAN
isfinite(x = FLOAT)
isfinite(x = DOUBLE)
isfinite(x = DATE)
isfinite(x = TIMESTAMP)
isfinite(x = `TIMESTAMP WITH TIME ZONE`)
isfinite(5.5)
Returns true if the floating point value is infinite, false otherwise.
x |
|
BOOLEAN
isinf(x = FLOAT)
isinf(x = DOUBLE)
isinf(x = DATE)
isinf(x = TIMESTAMP)
isinf(x = `TIMESTAMP WITH TIME ZONE`)
isinf('Infinity'::float)
Returns true if the floating point value is not a number, false otherwise.
x |
|
BOOLEAN
isnan(x = FLOAT)
isnan(x = DOUBLE)
isnan('NaN'::FLOAT)
Extract the isodow component from a date or timestamp.
ts |
|
BIGINT
isodow(ts = DATE)
isodow(ts = INTERVAL)
isodow(ts = TIMESTAMP)
isodow(ts = `TIMESTAMP WITH TIME ZONE`)
isodow(timestamp '2021-08-03 11:59:44.123456')
Extract the isoyear component from a date or timestamp.
ts |
|
BIGINT
isoyear(ts = DATE)
isoyear(ts = INTERVAL)
isoyear(ts = TIMESTAMP)
isoyear(ts = `TIMESTAMP WITH TIME ZONE`)
isoyear(timestamp '2021-08-03 11:59:44.123456')
The Jaccard similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1.
jaccard(s1 = VARCHAR, s2 = VARCHAR)jaccard(s1 = VARCHAR, s2 = VARCHAR)
s1 |
|
s2 |
|
DOUBLE
jaccard('duck', 'luck')
Other text_similarity:
damerau_levenshtein(),
hamming(),
jaro_similarity(),
jaro_winkler_similarity(),
levenshtein()
The Jaro similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.
s1 |
|
s2 |
|
score_cutoff |
|
DOUBLE
jaro_similarity(s1 = VARCHAR, s2 = VARCHAR)
jaro_similarity(s1 = VARCHAR, s2 = VARCHAR, score_cutoff = DOUBLE)
jaro_similarity('duck', 'duckdb')
Other text_similarity:
damerau_levenshtein(),
hamming(),
jaccard(),
jaro_winkler_similarity(),
levenshtein()
The Jaro-Winkler similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.
s1 |
|
s2 |
|
score_cutoff |
|
DOUBLE
jaro_winkler_similarity(s1 = VARCHAR, s2 = VARCHAR)
jaro_winkler_similarity(s1 = VARCHAR, s2 = VARCHAR, score_cutoff = DOUBLE)
jaro_winkler_similarity('duck', 'duckdb')
Other text_similarity:
damerau_levenshtein(),
hamming(),
jaccard(),
jaro_similarity(),
levenshtein()
Extract the Julian Day number from a date or timestamp.
ts |
|
DOUBLE
julian(ts = DATE)
julian(ts = TIMESTAMP)
julian(ts = `TIMESTAMP WITH TIME ZONE`)
julian(timestamp '2006-01-01 12:00')
Calculates the sum using a more accurate floating point summation (Kahan Sum).
kahan_sum(arg = DOUBLE)kahan_sum(arg = DOUBLE)
arg |
|
DOUBLE
kahan_sum(A)
Returns the excess kurtosis (Fisher’s definition) of all input values, with a bias correction according to the sample size.
kurtosis(x = DOUBLE)kurtosis(x = DOUBLE)
x |
|
DOUBLE
Returns the excess kurtosis (Fisher’s definition) of all input values, without bias correction.
kurtosis_pop(x = DOUBLE)kurtosis_pop(x = DOUBLE)
x |
|
DOUBLE
DuckDB function lag().
lag(col0 = T, col1 = BIGINT, col2 = T)lag(col0 = T, col1 = BIGINT, col2 = T)
col0 |
|
col1 |
|
col2 |
|
T
Returns the last value of a column. This function is affected by ordering.
arg |
|
col0 |
|
DECIMAL | ANY | T
last(arg = DECIMAL)
last(arg = ANY)
last(col0 = T)
last(A)
Returns the last day of the month.
ts |
|
DATE
last_day(ts = DATE)
last_day(ts = TIMESTAMP)
last_day(ts = `TIMESTAMP WITH TIME ZONE`)
last_day(TIMESTAMP '1992-03-22 01:02:03.1234')
DuckDB function last_value().
last_value(col0 = T)last_value(col0 = T)
col0 |
|
T
DuckDB function lead().
lead(col0 = T, col1 = BIGINT, col2 = T)lead(col0 = T, col1 = BIGINT, col2 = T)
col0 |
|
col1 |
|
col2 |
|
T
Returns the smallest value. For strings lexicographical ordering is used. Note that uppercase characters are considered “smaller” than lowercase characters, and collations are not supported.
least(arg1 = ANY)least(arg1 = ANY)
arg1 |
|
ANY
least(42, 84)
least('abc', 'bcd', 'cde', 'EFG')
Other aggregate:
greatest()
Other date:
greatest()
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
len(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Other timestamp:
greatest()
Computes the least common multiple of x and y.
x |
|
y |
|
BIGINT | HUGEINT
least_common_multiple(x = BIGINT, y = BIGINT)
least_common_multiple(x = HUGEINT, y = HUGEINT)
least_common_multiple(42, 57)
Extracts the left-most count characters.
left(string = VARCHAR, count = BIGINT)left(string = VARCHAR, count = BIGINT)
string |
|
count |
|
VARCHAR
left('Hello🦆', 2)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Extracts the left-most count grapheme clusters.
left_grapheme(string = VARCHAR, count = BIGINT)left_grapheme(string = VARCHAR, count = BIGINT)
string |
|
count |
|
VARCHAR
left_grapheme('🤦🏼♂️🤦🏽♀️', 1)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Number of characters in string.
Returns the bit-length of the bit argument.
Returns the length of the list.
string |
|
bit |
|
list |
|
BIGINT
len(string = VARCHAR)
len(bit = BIT)
len(list = `ANY[]`)
length('Hello🦆')
length(42::TINYINT::BIT)
length([1,2,3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
least(),
parse_formatted_bytes(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Number of grapheme clusters in string.
length_grapheme(string = VARCHAR)length_grapheme(string = VARCHAR)
string |
|
BIGINT
length_grapheme('🤦🏼♂️🤦🏽♀️')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Characters of different cases (e.g., a and A) are considered different.
levenshtein(s1 = VARCHAR, s2 = VARCHAR)levenshtein(s1 = VARCHAR, s2 = VARCHAR)
s1 |
|
s2 |
|
BIGINT
levenshtein('duck', 'db')
Other text_similarity:
damerau_levenshtein(),
hamming(),
jaccard(),
jaro_similarity(),
jaro_winkler_similarity()
Computes the log of the gamma function.
lgamma(x = DOUBLE)lgamma(x = DOUBLE)
x |
|
DOUBLE
lgamma(2)
Returns true if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.
like_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)like_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)
string |
|
like_specifier |
|
escape_character |
|
BOOLEAN
like_escape('a%c', 'a$%c', '$')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns a LIST containing all the values of a column.
list(arg = T)list(arg = T)
arg |
|
T[]
list(A)
Executes the aggregate function function_name on the elements of list.
list_aggregate(list = `ANY[]`, function_name = VARCHAR)list_aggregate(list = `ANY[]`, function_name = VARCHAR)
list |
|
function_name |
|
ANY
list_aggregate([1, 2, NULL], 'min')
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_any_value().
list_any_value(l)list_any_value(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_append().
list_append(l, e)list_append(l, e)
l |
Unspecified. |
e |
Unspecified. |
Unspecified.
DuckDB macro list_approx_count_distinct().
list_approx_count_distinct(l)list_approx_count_distinct(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_avg().
list_avg(l)list_avg(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_bit_and().
list_bit_and(l)list_bit_and(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_bit_or().
list_bit_or(l)list_bit_or(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_bit_xor().
list_bit_xor(l)list_bit_xor(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_bool_and().
list_bool_and(l)list_bool_and(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_bool_or().
list_bool_or(l)list_bool_or(l)
l |
Unspecified. |
Unspecified.
Concatenates lists. NULL inputs are skipped. See also operator ||.
list_concat()list_concat()
ANY[]
list_concat([2, 3], [4, 5, 6], [7])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns true if the list contains the element.
list_contains(list = `T[]`, element = T)list_contains(list = `T[]`, element = T)
list |
|
element |
|
BOOLEAN
list_contains([1, 2, NULL], 1)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Computes the cosine distance between two same-sized lists.
list1 |
|
list2 |
|
FLOAT | DOUBLE
list_cosine_distance(list1 = `FLOAT[]`, list2 = `FLOAT[]`)
list_cosine_distance(list1 = `DOUBLE[]`, list2 = `DOUBLE[]`)
list_cosine_distance([1, 2, 3], [1, 2, 3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Computes the cosine similarity between two same-sized lists.
list1 |
|
list2 |
|
FLOAT | DOUBLE
list_cosine_similarity(list1 = `FLOAT[]`, list2 = `FLOAT[]`)
list_cosine_similarity(list1 = `DOUBLE[]`, list2 = `DOUBLE[]`)
list_cosine_similarity([1, 2, 3], [1, 2, 3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_count().
list_count(l)list_count(l)
l |
Unspecified. |
Unspecified.
Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
list1 |
|
list2 |
|
FLOAT | DOUBLE
list_distance(list1 = `FLOAT[]`, list2 = `FLOAT[]`)
list_distance(list1 = `DOUBLE[]`, list2 = `DOUBLE[]`)
list_distance([1, 2, 3], [1, 2, 5])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Removes all duplicates and NULL values from a list. Does not preserve the original order.
list_distinct(list = `T[]`)list_distinct(list = `T[]`)
list |
|
T[]
list_distinct([1, 1, NULL, -3, 1, 5])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_entropy().
list_entropy(l)list_entropy(l)
l |
Unspecified. |
Unspecified.
Extract the indexth (1-based) value from the list.
list |
|
index |
|
T | VARCHAR
list_extract(list = `T[]`, index = BIGINT)
list_extract(list = VARCHAR, index = BIGINT)
list_extract([4, 5, 6], 3)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's.
list_filter(list = `ANY[]`, `lambda(x)` = LAMBDA)list_filter(list = `ANY[]`, `lambda(x)` = LAMBDA)
list |
|
lambda(x) |
|
ANY[]
list_filter([3, 4, 5], lambda x : x > 4)
Other lambda:
list_reduce(),
list_transform()
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_first().
list_first(l)list_first(l)
l |
Unspecified. |
Unspecified.
Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
list |
|
col1 |
|
col2 |
|
ANY[]
list_grade_up(list = `ANY[]`)
list_grade_up(list = `ANY[]`, col1 = VARCHAR)
list_grade_up(list = `ANY[]`, col1 = VARCHAR, col2 = VARCHAR)
list_grade_up([3, 6, 1, 2])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns true if all elements of list2 are in list1. NULLs are ignored.
list_has_all(list1 = `T[]`, list2 = `T[]`)list_has_all(list1 = `T[]`, list2 = `T[]`)
list1 |
|
list2 |
|
BOOLEAN
list_has_all([1, 2, 3], [2, 3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns true if the lists have any element in common. NULLs are ignored.
list_has_any(list1 = `T[]`, list2 = `T[]`)list_has_any(list1 = `T[]`, list2 = `T[]`)
list1 |
|
list2 |
|
BOOLEAN
list_has_any([1, 2, 3], [2, 3, 4])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_histogram().
list_histogram(l)list_histogram(l)
l |
Unspecified. |
Unspecified.
Computes the inner product between two same-sized lists.
list1 |
|
list2 |
|
FLOAT | DOUBLE
list_inner_product(list1 = `FLOAT[]`, list2 = `FLOAT[]`)
list_inner_product(list1 = `DOUBLE[]`, list2 = `DOUBLE[]`)
list_inner_product([1, 2, 3], [1, 2, 3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns a list containing the distinct elements that are present in both list1 and list2.
list_intersect(list1 = `T[]`, list2 = `T[]`)list_intersect(list1 = `T[]`, list2 = `T[]`)
list1 |
|
list2 |
|
T[]
list_intersect([1, 2, 3], [2, 3, 4])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_kurtosis().
list_kurtosis(l)list_kurtosis(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_kurtosis_pop().
list_kurtosis_pop(l)list_kurtosis_pop(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_last().
list_last(l)list_last(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_mad().
list_mad(l)list_mad(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_max().
list_max(l)list_max(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_median().
list_median(l)list_median(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_min().
list_min(l)list_min(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_mode().
list_mode(l)list_mode(l)
l |
Unspecified. |
Unspecified.
Computes the negative inner product between two same-sized lists.
list1 |
|
list2 |
|
FLOAT | DOUBLE
list_negative_inner_product(list1 = `FLOAT[]`, list2 = `FLOAT[]`)
list_negative_inner_product(list1 = `DOUBLE[]`, list2 = `DOUBLE[]`)
list_negative_inner_product([1, 2, 3], [1, 2, 3])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.
list_position(list = `T[]`, element = T)list_position(list = `T[]`, element = T)
list |
|
element |
|
INTEGER
list_position([1, 2, NULL], 2)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_prepend().
list_prepend(e, l)list_prepend(e, l)
e |
Unspecified. |
l |
Unspecified. |
Unspecified.
DuckDB macro list_product().
list_product(l)list_product(l)
l |
Unspecified. |
Unspecified.
Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.
list |
|
initial_value |
|
lambda(x, y)
|
|
ANY
list_reduce(list = `ANY[]`, `lambda(x,y)` = LAMBDA)
list_reduce(list = `ANY[]`, `lambda(x,y)` = LAMBDA, initial_value = ANY)
list_reduce([1, 2, 3], lambda x, y : x + y)
Other lambda:
list_filter(),
list_transform()
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Resizes the list to contain size elements. Initializes new elements with value or NULL if value is not set.
list |
|
size[ |
|
value] |
|
ANY[]
list_resize(list = `ANY[]`, `size[` = ANY)
list_resize(list = `ANY[]`, `size[` = ANY, `value]` = ANY)
list_resize([1, 2, 3], 5, 0)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_reverse().
list_reverse(l)list_reverse(l)
l |
Unspecified. |
Unspecified.
Sorts the elements of the list in reverse order.
list |
|
col1 |
|
ANY[]
list_reverse_sort(list = `ANY[]`)
list_reverse_sort(list = `ANY[]`, col1 = VARCHAR)
list_reverse_sort([3, 6, 1, 2])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Returns a list based on the elements selected by the index_list.
list_select(value_list = `T[]`, index_list = `BIGINT[]`)list_select(value_list = `T[]`, index_list = `BIGINT[]`)
value_list |
|
index_list |
|
T[]
list_select([10, 20, 30, 40], [1, 4])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_sem().
list_sem(l)list_sem(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_skewness().
list_skewness(l)list_skewness(l)
l |
Unspecified. |
Unspecified.
Extracts a sublist or substring using slice conventions. Negative values are accepted.
list_slice with added step feature.
list |
|
begin |
|
end |
|
step |
|
ANY
list_slice(list = ANY, begin = ANY, end = ANY)
list_slice(list = ANY, begin = ANY, end = ANY, step = BIGINT)
list_slice([4, 5, 6], 2, 3)
list_slice([4, 5, 6], 1, 3, 2)
array_slice('DuckDB', 3, 4)
array_slice('DuckDB', 3, NULL)
array_slice('DuckDB', 0, -3)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Sorts the elements of the list.
list |
|
col1 |
|
col2 |
|
ANY[]
list_sort(list = `ANY[]`)
list_sort(list = `ANY[]`, col1 = VARCHAR)
list_sort(list = `ANY[]`, col1 = VARCHAR, col2 = VARCHAR)
list_sort([3, 6, 1, 2])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_stddev_pop().
list_stddev_pop(l)list_stddev_pop(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_stddev_samp().
list_stddev_samp(l)list_stddev_samp(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_string_agg().
list_string_agg(l)list_string_agg(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_sum().
list_sum(l)list_sum(l)
l |
Unspecified. |
Unspecified.
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
list_transform(list = `ANY[]`, `lambda(x)` = LAMBDA)list_transform(list = `ANY[]`, `lambda(x)` = LAMBDA)
list |
|
lambda(x) |
|
ANY[]
list_transform([1, 2, 3], lambda x : x + 1)
Other lambda:
list_filter(),
list_reduce()
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Counts the unique elements of a list.
list_unique(list = `ANY[]`)list_unique(list = `ANY[]`)
list |
|
UBIGINT
list_unique([1, 1, NULL, -3, 1, 5])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Creates a LIST containing the argument values.
any |
|
"NULL"[] | T[]
list_value()
list_value(any = T)
list_value(4, 5, 6)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_where(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
DuckDB macro list_var_pop().
list_var_pop(l)list_var_pop(l)
l |
Unspecified. |
Unspecified.
DuckDB macro list_var_samp().
list_var_samp(l)list_var_samp(l)
l |
Unspecified. |
Unspecified.
Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.
list_where(value_list = `T[]`, mask_list = `BOOLEAN[]`)list_where(value_list = `T[]`, mask_list = `BOOLEAN[]`)
value_list |
|
mask_list |
|
T[]
list_where([10, 20, 30, 40], [true, false, false, true])
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_zip(),
or–or,
range(),
repeat(),
unpivot_list()
Zips n LISTs to a new LIST whose length will be that of the longest list. Its elements are structs of n elements from each list list_1, …, list_n, missing elements are replaced with NULL. If truncate is set, all lists are truncated to the smallest list length.
list_zip()list_zip()
STRUCT[]
list_zip([1, 2], [3, 4], [5, 6]) list_zip([1, 2], [3, 4], [5, 6, 7]) list_zip([1, 2], [3, 4], [5, 6, 7], true)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
or–or,
range(),
repeat(),
unpivot_list()
Computes the natural logarithm of x.
ln(x = DOUBLE)ln(x = DOUBLE)
x |
|
DOUBLE
ln(2)
Computes the logarithm of x to base b. b may be omitted, in which case the default 10.
b |
|
x |
|
DOUBLE
log(b = DOUBLE)
log(b = DOUBLE, x = DOUBLE)
log(2, 64)
Computes the 10-log of x.
log10(x = DOUBLE)log10(x = DOUBLE)
x |
|
DOUBLE
log10(1000)
Computes the 2-log of x.
log2(x = DOUBLE)log2(x = DOUBLE)
x |
|
DOUBLE
log2(8)
Converts string to lower case.
lower(string = VARCHAR)lower(string = VARCHAR)
string |
|
VARCHAR
lower('Hello')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Pads the string with the character on the left until it has count characters. Truncates the string on the right if it has more than count characters.
lpad(string = VARCHAR, count = INTEGER, character = VARCHAR)lpad(string = VARCHAR, count = INTEGER, character = VARCHAR)
string |
|
count |
|
character |
|
VARCHAR
lpad('hello', 8, '>')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Removes any occurrences of any of the characters from the left side of the string. characters defaults to space.
string |
|
characters |
|
VARCHAR
ltrim(string = VARCHAR)
ltrim(string = VARCHAR, characters = VARCHAR)
ltrim(' test ')
ltrim('>>>>test<<', '><')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL. .
x |
|
DECIMAL | FLOAT | DOUBLE | INTERVAL
mad(x = DECIMAL)
mad(x = FLOAT)
mad(x = DOUBLE)
mad(x = DATE)
mad(x = TIMESTAMP)
mad(x = TIME)
mad(x = `TIMESTAMP WITH TIME ZONE`)
mad(x = `TIME WITH TIME ZONE`)
mad(x)
The date for the given parts.
The date for the given struct.
col0 |
|
year |
|
month |
|
day |
|
date-struct |
|
DATE
make_date(col0 = INTEGER)
make_date(year = BIGINT, month = BIGINT, day = BIGINT)
make_date(`date-struct` = `STRUCT("year" BIGINT, "month" BIGINT, "day" BIGINT)`)
make_date(1992, 9, 20)
make_date({'year': 2024, 'month': 11, 'day': 14})
The time for the given parts.
make_time(hour = BIGINT, minute = BIGINT, seconds = DOUBLE)make_time(hour = BIGINT, minute = BIGINT, seconds = DOUBLE)
hour |
|
minute |
|
seconds |
|
TIME
make_time(13, 34, 27.123456)
The timestamp for the given parts.
year |
|
month |
|
day |
|
hour |
|
minute |
|
seconds |
|
TIMESTAMP
make_timestamp(year = BIGINT, month = BIGINT, day = BIGINT, hour = BIGINT, minute = BIGINT, seconds = DOUBLE)
make_timestamp(year = BIGINT)
make_timestamp(1992, 9, 20, 13, 34, 27.123456)
The timestamp for the given microseconds since the epoch.
make_timestamp_ms(nanos = BIGINT)make_timestamp_ms(nanos = BIGINT)
nanos |
|
TIMESTAMP
make_timestamp_ms(1732117793000000)
The timestamp for the given nanoseconds since epoch.
make_timestamp_ns(nanos = BIGINT)make_timestamp_ns(nanos = BIGINT)
nanos |
|
TIMESTAMP_NS
make_timestamp_ns(1732117793000000000)
Construct a type from its name and optional parameters.
make_type(name = VARCHAR)make_type(name = VARCHAR)
name |
|
TYPE
make_type('DECIMAL', 10, 2)
Creates a map from a set of keys and values.
keys |
|
values |
|
MAP("NULL", "NULL") | MAP(K, V)
map()
map(keys = `K[]`, values = `V[]`)
map(['key1', 'key2'], ['val1', 'val2'])
Returns a map created from merging the input maps, on key collision the value is taken from the last map with that key.
map_concat()map_concat()
LIST
map_concat(map([1, 2], ['a', 'b']), map([2, 3], ['c', 'd']));
Checks if a map contains a given key.
map_contains(map = `MAP(K, V)`, key = K)map_contains(map = `MAP(K, V)`, key = K)
map |
|
key |
|
BOOLEAN
map_contains(MAP {'key1': 10, 'key2': 20, 'key3': 30}, 'key2')
DuckDB macro map_contains_entry().
map_contains_entry(map, key, value)map_contains_entry(map, key, value)
map |
Unspecified. |
key |
Unspecified. |
value |
Unspecified. |
Unspecified.
DuckDB macro map_contains_value().
map_contains_value(map, value)map_contains_value(map, value)
map |
Unspecified. |
value |
Unspecified. |
Unspecified.
Returns the map entries as a list of keys/values.
map_entries(map = `MAP(K, V)`)map_entries(map = `MAP(K, V)`)
map |
|
STRUCT("key" K, "value" V)[]
map_entries(map(['key'], ['val']))
Returns 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.
map_extract(map = `MAP(K, V)`, key = K)map_extract(map = `MAP(K, V)`, key = K)
map |
|
key |
|
V[]
map_extract(map(['key'], ['val']), 'key')
Returns the value for a given key or NULL 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.
map_extract_value(map = `MAP(K, V)`, key = K)map_extract_value(map = `MAP(K, V)`, key = K)
map |
|
key |
|
V
map_extract_value(map(['key'], ['val']), 'key')
Returns a map created from the entries of the array.
map_from_entries(map = `STRUCT(K, V)[]`)map_from_entries(map = `STRUCT(K, V)[]`)
map |
|
MAP(K, V)
map_from_entries([{k: 5, v: 'val1'}, {k: 3, v: 'val2'}]);
Returns the keys of a map as a list.
map_keys(map = `MAP(K, V)`)map_keys(map = `MAP(K, V)`)
map |
|
K[]
map_keys(map(['key'], ['val']))
DuckDB macro map_to_pg_oid().
map_to_pg_oid(type_name)map_to_pg_oid(type_name)
type_name |
Unspecified. |
Unspecified.
Returns the values of a map as a list.
map_values(map = `MAP(K, V)`)map_values(map = `MAP(K, V)`)
map |
|
V[]
map_values(map(['key'], ['val']))
Returns the maximum value present in arg.
arg |
|
col1 |
|
ANY | ANY[]
max(arg = ANY)
max(arg = ANY, col1 = BIGINT)
max(A)
Returns the MD5 hash of the string as a VARCHAR.
Returns the MD5 hash of the blob as a VARCHAR.
string |
|
blob |
|
VARCHAR
md5(string = VARCHAR)
md5(blob = BLOB)
md5('abc')
md5('\xAA\xBB'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns the MD5 hash of the string as a HUGEINT.
Returns the MD5 hash of the blob as a HUGEINT.
string |
|
blob |
|
UHUGEINT
md5_number(string = VARCHAR)
md5_number(blob = BLOB)
md5_number('abc')
md5_number('\xAA\xBB'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB macro md5_number_lower().
md5_number_lower(param)md5_number_lower(param)
param |
Unspecified. |
Unspecified.
DuckDB macro md5_number_upper().
md5_number_upper(param)md5_number_upper(param)
param |
Unspecified. |
Unspecified.
Returns the middle value of the set. NULL values are ignored. For even value counts, interpolate-able types (numeric, date/time) return the average of the two middle values. Non-interpolate-able types (everything else) return the lower of the two middle values.
median(x = ANY)median(x = ANY)
x |
|
ANY
median(x)
DuckDB function metadata_info().
metadata_info()metadata_info()
Unspecified.
Extract the microsecond component from a date or timestamp.
ts |
|
BIGINT
microsecond(ts = DATE)
microsecond(ts = INTERVAL)
microsecond(ts = TIME)
microsecond(ts = TIMESTAMP)
microsecond(ts = `TIME WITH TIME ZONE`)
microsecond(ts = TIME_NS)
microsecond(ts = `TIMESTAMP WITH TIME ZONE`)
microsecond(timestamp '2021-08-03 11:59:44.123456')
Extract the millennium component from a date or timestamp.
ts |
|
BIGINT
millennium(ts = DATE)
millennium(ts = INTERVAL)
millennium(ts = TIMESTAMP)
millennium(ts = `TIMESTAMP WITH TIME ZONE`)
millennium(timestamp '2021-08-03 11:59:44.123456')
Extract the millisecond component from a date or timestamp.
ts |
|
BIGINT
millisecond(ts = DATE)
millisecond(ts = INTERVAL)
millisecond(ts = TIME)
millisecond(ts = TIMESTAMP)
millisecond(ts = `TIME WITH TIME ZONE`)
millisecond(ts = TIME_NS)
millisecond(ts = `TIMESTAMP WITH TIME ZONE`)
millisecond(timestamp '2021-08-03 11:59:44.123456')
Returns the minimum value present in arg.
arg |
|
col1 |
|
ANY | ANY[]
min(arg = ANY)
min(arg = ANY, col1 = BIGINT)
min(A)
Extract the minute component from a date or timestamp.
ts |
|
BIGINT
minute(ts = DATE)
minute(ts = INTERVAL)
minute(ts = TIME)
minute(ts = TIMESTAMP)
minute(ts = `TIME WITH TIME ZONE`)
minute(ts = TIME_NS)
minute(ts = `TIMESTAMP WITH TIME ZONE`)
minute(timestamp '2021-08-03 11:59:44.123456')
DuckDB function mod().
col0 |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT
mod(col0 = TINYINT, col1 = TINYINT)
mod(col0 = SMALLINT, col1 = SMALLINT)
mod(col0 = INTEGER, col1 = INTEGER)
mod(col0 = BIGINT, col1 = BIGINT)
mod(col0 = HUGEINT, col1 = HUGEINT)
mod(col0 = FLOAT, col1 = FLOAT)
mod(col0 = DOUBLE, col1 = DOUBLE)
mod(col0 = DECIMAL, col1 = DECIMAL)
mod(col0 = UTINYINT, col1 = UTINYINT)
mod(col0 = USMALLINT, col1 = USMALLINT)
mod(col0 = UINTEGER, col1 = UINTEGER)
mod(col0 = UBIGINT, col1 = UBIGINT)
mod(col0 = UHUGEINT, col1 = UHUGEINT)
Returns the most frequent value for the values within x. NULL values are ignored.
mode(x = ANY)mode(x = ANY)
x |
|
ANY
Extract the month component from a date or timestamp.
ts |
|
BIGINT
month(ts = DATE)
month(ts = INTERVAL)
month(ts = TIMESTAMP)
month(ts = `TIMESTAMP WITH TIME ZONE`)
month(timestamp '2021-08-03 11:59:44.123456')
The (English) name of the month.
ts |
|
VARCHAR
monthname(ts = DATE)
monthname(ts = TIMESTAMP)
monthname(ts = `TIMESTAMP WITH TIME ZONE`)
monthname(TIMESTAMP '1992-09-20')
DuckDB function multiply().
col0 |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | INTERVAL
multiply(col0 = TINYINT, col1 = TINYINT)
multiply(col0 = SMALLINT, col1 = SMALLINT)
multiply(col0 = INTEGER, col1 = INTEGER)
multiply(col0 = BIGINT, col1 = BIGINT)
multiply(col0 = HUGEINT, col1 = HUGEINT)
multiply(col0 = FLOAT, col1 = FLOAT)
multiply(col0 = DOUBLE, col1 = DOUBLE)
multiply(col0 = DECIMAL, col1 = DECIMAL)
multiply(col0 = UTINYINT, col1 = UTINYINT)
multiply(col0 = USMALLINT, col1 = USMALLINT)
multiply(col0 = UINTEGER, col1 = UINTEGER)
multiply(col0 = UBIGINT, col1 = UBIGINT)
multiply(col0 = UHUGEINT, col1 = UHUGEINT)
multiply(col0 = INTERVAL, col1 = DOUBLE)
multiply(col0 = DOUBLE, col1 = INTERVAL)
multiply(col0 = BIGINT, col1 = INTERVAL)
multiply(col0 = INTERVAL, col1 = BIGINT)
Extract the nanosecond component from a date or timestamp.
tsns |
|
BIGINT
nanosecond(tsns = DATE)
nanosecond(tsns = TIMESTAMP)
nanosecond(tsns = INTERVAL)
nanosecond(tsns = TIME)
nanosecond(tsns = TIME_NS)
nanosecond(tsns = `TIME WITH TIME ZONE`)
nanosecond(tsns = TIMESTAMP_NS)
nanosecond(tsns = `TIMESTAMP WITH TIME ZONE`)
nanosecond(timestamp_ns '2021-08-03 11:59:44.123456789')
Returns the next floating point value after x in the direction of y.
x |
|
y |
|
DOUBLE | FLOAT
nextafter(x = DOUBLE, y = DOUBLE)
nextafter(x = FLOAT, y = FLOAT)
nextafter(1::float, 2::float)
Return the following value of the sequence.
nextval(`'sequence_name'` = VARCHAR)nextval(`'sequence_name'` = VARCHAR)
'sequence_name' |
|
BIGINT
nextval('my_sequence_name')
Converts string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.
nfc_normalize(string = VARCHAR)nfc_normalize(string = VARCHAR)
string |
|
VARCHAR
nfc_normalize('ardèch')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Normalizes an INTERVAL to an equivalent interval.
normalized_interval(interval = INTERVAL)normalized_interval(interval = INTERVAL)
interval |
|
INTERVAL
normalized_interval(INTERVAL '30 days')
Returns false if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.
not_ilike_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)not_ilike_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)
string |
|
like_specifier |
|
escape_character |
|
BOOLEAN
not_ilike_escape('A%c', 'a$%C', '$')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns false if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.
not_like_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)not_like_escape(string = VARCHAR, like_specifier = VARCHAR, escape_character = VARCHAR)
string |
|
like_specifier |
|
escape_character |
|
BOOLEAN
not_like_escape('a%c', 'a$%c', '$')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function !~~().
`!~~`(col0 = VARCHAR, col1 = VARCHAR)`!~~`(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
BOOLEAN
DuckDB function !~~*().
`!~~*`(col0 = VARCHAR, col1 = VARCHAR)`!~~*`(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
BOOLEAN
DuckDB function nth_value().
nth_value(col0 = T, col1 = BIGINT)nth_value(col0 = T, col1 = BIGINT)
col0 |
|
col1 |
|
T
DuckDB function ntile().
ntile(col0 = BIGINT)ntile(col0 = BIGINT)
col0 |
|
BIGINT
DuckDB macro nullif().
nullif(a, b)nullif(a, b)
a |
Unspecified. |
b |
Unspecified. |
Unspecified.
DuckDB macro obj_description().
obj_description(object_oid, catalog_name)obj_description(object_oid, catalog_name)
object_oid |
Unspecified. |
catalog_name |
Unspecified. |
Unspecified.
Number of bytes in blob.
Returns the number of bytes in the bitstring.
blob |
|
bitstring |
|
BIGINT
octet_length(blob = BLOB)
octet_length(bitstring = BIT)
octet_length('\xAA\xBB'::BLOB)
octet_length('1101011'::BITSTRING)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Bitwise OR.
left |
|
right |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
`|`(left = TINYINT, right = TINYINT)
`|`(left = SMALLINT, right = SMALLINT)
`|`(left = INTEGER, right = INTEGER)
`|`(left = BIGINT, right = BIGINT)
`|`(left = HUGEINT, right = HUGEINT)
`|`(left = UTINYINT, right = UTINYINT)
`|`(left = USMALLINT, right = USMALLINT)
`|`(left = UINTEGER, right = UINTEGER)
`|`(left = UBIGINT, right = UBIGINT)
`|`(left = UHUGEINT, right = UHUGEINT)
`|`(left = BIT, right = BIT)
32 | 3
Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...).
`||`(arg1 = ANY, arg2 = ANY)`||`(arg1 = ANY, arg2 = ANY)
arg1 |
|
arg2 |
|
ANY
'Duck' || 'DB' [1, 2, 3] || [4, 5, 6] '\xAA'::BLOB || '\xBB'::BLOB
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
repeat(),
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
range(),
repeat(),
unpivot_list()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function parquet_bloom_probe().
col0 |
|
col1 |
|
col2 |
|
Unspecified.
parquet_bloom_probe(col0 = VARCHAR, col1 = VARCHAR, col2 = ANY)
parquet_bloom_probe(col0 = `VARCHAR[]`, col1 = VARCHAR, col2 = ANY)
DuckDB function parquet_file_metadata().
col0 |
|
Unspecified.
parquet_file_metadata(col0 = VARCHAR)
parquet_file_metadata(col0 = `VARCHAR[]`)
DuckDB function parquet_full_metadata().
col0 |
|
Unspecified.
parquet_full_metadata(col0 = VARCHAR)
parquet_full_metadata(col0 = `VARCHAR[]`)
DuckDB function parquet_kv_metadata().
col0 |
|
Unspecified.
parquet_kv_metadata(col0 = VARCHAR)
parquet_kv_metadata(col0 = `VARCHAR[]`)
DuckDB function parquet_metadata().
col0 |
|
Unspecified.
parquet_metadata(col0 = VARCHAR)
parquet_metadata(col0 = `VARCHAR[]`)
DuckDB function parquet_scan().
col0 |
|
can_have_nan |
|
filename |
|
union_by_name |
|
debug_use_openssl |
|
hive_partitioning |
|
parquet_version |
|
encryption_config |
|
hive_types_autocast |
|
binary_as_string |
|
explicit_cardinality |
|
compression |
|
file_row_number |
|
hive_types |
|
schema |
|
Unspecified.
parquet_scan(col0 = VARCHAR, can_have_nan = BOOLEAN, filename = ANY, union_by_name = BOOLEAN, debug_use_openssl = BOOLEAN, hive_partitioning = BOOLEAN, parquet_version = VARCHAR, encryption_config = ANY, hive_types_autocast = BOOLEAN, binary_as_string = BOOLEAN, explicit_cardinality = UBIGINT, compression = VARCHAR, file_row_number = BOOLEAN, hive_types = ANY, schema = ANY)
parquet_scan(col0 = `VARCHAR[]`, can_have_nan = BOOLEAN, filename = ANY, union_by_name = BOOLEAN, debug_use_openssl = BOOLEAN, hive_partitioning = BOOLEAN, parquet_version = VARCHAR, encryption_config = ANY, hive_types_autocast = BOOLEAN, binary_as_string = BOOLEAN, explicit_cardinality = UBIGINT, compression = VARCHAR, file_row_number = BOOLEAN, hive_types = ANY, schema = ANY)
DuckDB function parquet_schema().
col0 |
|
Unspecified.
parquet_schema(col0 = VARCHAR)
parquet_schema(col0 = `VARCHAR[]`)
Returns the top-level directory name from the given path. separator options: system, both_slash (default), forward_slash, backslash.
path |
|
separator |
|
VARCHAR
parse_dirname(path = VARCHAR)
parse_dirname(path = VARCHAR, separator = VARCHAR)
parse_dirname('path/to/file.csv', 'system')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns the head of the path (the pathname until the last slash) similarly to Python's os.path.dirname. separator options: system, both_slash (default), forward_slash, backslash.
path |
|
separator |
|
VARCHAR
parse_dirpath(path = VARCHAR)
parse_dirpath(path = VARCHAR, separator = VARCHAR)
parse_dirpath('path/to/file.csv', 'forward_slash')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Parse the message into the expected logical type.
parse_duckdb_log_message(type = VARCHAR, message = VARCHAR)parse_duckdb_log_message(type = VARCHAR, message = VARCHAR)
type |
|
message |
|
ANY
parse_duckdb_log_message('FileSystem', log_message)
Returns the last component of the path similarly to Python's os.path.basename function. If trim_extension is true, the file extension will be removed (defaults to false). separator options: system, both_slash (default), forward_slash, backslash.
string |
|
trim_extension |
|
separator |
|
VARCHAR
parse_filename(string = VARCHAR)
parse_filename(string = VARCHAR, trim_extension = VARCHAR)
parse_filename(string = VARCHAR, trim_extension = BOOLEAN)
parse_filename(string = VARCHAR, trim_extension = BOOLEAN, separator = VARCHAR)
parse_filename('path/to/file.csv', true, 'forward_slash')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Parses a human-readable representation of a size in bytes into an integer.
parse_formatted_bytes(string = VARCHAR)parse_formatted_bytes(string = VARCHAR)
string |
|
UBIGINT
parse_formatted_bytes('16 KiB')
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
least(),
len(),
to_base()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns a list of the components (directories and filename) in the path similarly to Python's pathlib.parts function. separator options: system, both_slash (default), forward_slash, backslash.
path |
|
separator |
|
VARCHAR[]
parse_path(path = VARCHAR)
parse_path(path = VARCHAR, separator = VARCHAR)
parse_path('path/to/file.csv', 'system')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function percent_rank().
percent_rank()percent_rank()
DOUBLE
DuckDB macro pg_collation_is_visible().
pg_collation_is_visible(collation_oid)pg_collation_is_visible(collation_oid)
collation_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_conf_load_time().
pg_conf_load_time()pg_conf_load_time()
Unspecified.
DuckDB macro pg_conversion_is_visible().
pg_conversion_is_visible(conversion_oid)pg_conversion_is_visible(conversion_oid)
conversion_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_function_is_visible().
pg_function_is_visible(function_oid)pg_function_is_visible(function_oid)
function_oid |
Unspecified. |
Unspecified.
DuckDB function pg_get_constraintdef().
constraint_oid |
Unspecified. |
pretty_bool |
Unspecified. |
Unspecified.
pg_get_constraintdef(constraint_oid)
pg_get_constraintdef(constraint_oid, pretty_bool)
DuckDB macro pg_get_expr().
pg_get_expr(pg_node_tree, relation_oid)pg_get_expr(pg_node_tree, relation_oid)
pg_node_tree |
Unspecified. |
relation_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_get_viewdef().
pg_get_viewdef(oid)pg_get_viewdef(oid)
oid |
Unspecified. |
Unspecified.
DuckDB function pg_has_role().
user |
Unspecified. |
role |
Unspecified. |
privilege |
Unspecified. |
Unspecified.
pg_has_role(user, role, privilege)
pg_has_role(role, privilege)
DuckDB macro pg_is_other_temp_schema().
pg_is_other_temp_schema(schema_id)pg_is_other_temp_schema(schema_id)
schema_id |
Unspecified. |
Unspecified.
DuckDB macro pg_my_temp_schema().
pg_my_temp_schema()pg_my_temp_schema()
Unspecified.
DuckDB macro pg_opclass_is_visible().
pg_opclass_is_visible(opclass_oid)pg_opclass_is_visible(opclass_oid)
opclass_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_operator_is_visible().
pg_operator_is_visible(operator_oid)pg_operator_is_visible(operator_oid)
operator_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_opfamily_is_visible().
pg_opfamily_is_visible(opclass_oid)pg_opfamily_is_visible(opclass_oid)
opclass_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_postmaster_start_time().
pg_postmaster_start_time()pg_postmaster_start_time()
Unspecified.
DuckDB macro pg_size_pretty().
pg_size_pretty(bytes)pg_size_pretty(bytes)
bytes |
Unspecified. |
Unspecified.
DuckDB macro pg_sleep().
pg_sleep(seconds)pg_sleep(seconds)
seconds |
Unspecified. |
Unspecified.
DuckDB macro pg_table_is_visible().
pg_table_is_visible(table_oid)pg_table_is_visible(table_oid)
table_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_ts_config_is_visible().
pg_ts_config_is_visible(config_oid)pg_ts_config_is_visible(config_oid)
config_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_ts_dict_is_visible().
pg_ts_dict_is_visible(dict_oid)pg_ts_dict_is_visible(dict_oid)
dict_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_ts_parser_is_visible().
pg_ts_parser_is_visible(parser_oid)pg_ts_parser_is_visible(parser_oid)
parser_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_ts_template_is_visible().
pg_ts_template_is_visible(template_oid)pg_ts_template_is_visible(template_oid)
template_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_type_is_visible().
pg_type_is_visible(type_oid)pg_type_is_visible(type_oid)
type_oid |
Unspecified. |
Unspecified.
DuckDB macro pg_typeof().
pg_typeof(expression)pg_typeof(expression)
expression |
Unspecified. |
Unspecified.
Returns the value of pi.
pi()pi()
DOUBLE
pi()
DuckDB function platform().
platform()platform()
Unspecified.
Computes x to the power of y.
pow(x = DOUBLE, y = DOUBLE)pow(x = DOUBLE, y = DOUBLE)
x |
|
y |
|
DOUBLE
pow(2, 3) power(2, 3) 2 ** 3 2 ^ 3
DuckDB function pragma_collations().
pragma_collations()pragma_collations()
Unspecified.
DuckDB function pragma_database_size().
pragma_database_size()pragma_database_size()
Unspecified.
DuckDB function pragma_metadata_info().
col0 |
|
Unspecified.
pragma_metadata_info()
pragma_metadata_info(col0 = VARCHAR)
DuckDB function pragma_platform().
pragma_platform()pragma_platform()
Unspecified.
DuckDB function pragma_show().
pragma_show(col0 = VARCHAR)pragma_show(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function pragma_storage_info().
pragma_storage_info(col0 = VARCHAR)pragma_storage_info(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function pragma_table_info().
pragma_table_info(col0 = VARCHAR)pragma_table_info(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function pragma_user_agent().
pragma_user_agent()pragma_user_agent()
Unspecified.
DuckDB function pragma_version().
pragma_version()pragma_version()
Unspecified.
Returns true if string starts with search_string.
prefix(string = VARCHAR, search_string = VARCHAR)prefix(string = VARCHAR, search_string = VARCHAR)
string |
|
search_string |
|
BOOLEAN
prefix('abc', 'ab')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Formats a string using printf syntax.
printf(format = VARCHAR)printf(format = VARCHAR)
format |
|
VARCHAR
printf('Benchmark "%s" took %d seconds', 'CSV', 42)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Calculates the product of all tuples in arg.
product(arg = DOUBLE)product(arg = DOUBLE)
arg |
|
DOUBLE
product(A)
Returns the interpolated quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles. .
x |
|
pos |
|
DECIMAL | TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DATE | TIMESTAMP | TIME | TIMESTAMP WITH TIME ZONE | TIME WITH TIME ZONE
quantile_cont(x = DECIMAL, pos = DOUBLE)
quantile_cont(x = DECIMAL, pos = `DOUBLE[]`)
quantile_cont(x = TINYINT, pos = DOUBLE)
quantile_cont(x = TINYINT, pos = `DOUBLE[]`)
quantile_cont(x = SMALLINT, pos = DOUBLE)
quantile_cont(x = SMALLINT, pos = `DOUBLE[]`)
quantile_cont(x = INTEGER, pos = DOUBLE)
quantile_cont(x = INTEGER, pos = `DOUBLE[]`)
quantile_cont(x = BIGINT, pos = DOUBLE)
quantile_cont(x = BIGINT, pos = `DOUBLE[]`)
quantile_cont(x = HUGEINT, pos = DOUBLE)
quantile_cont(x = HUGEINT, pos = `DOUBLE[]`)
quantile_cont(x = FLOAT, pos = DOUBLE)
quantile_cont(x = FLOAT, pos = `DOUBLE[]`)
quantile_cont(x = DOUBLE, pos = DOUBLE)
quantile_cont(x = DOUBLE, pos = `DOUBLE[]`)
quantile_cont(x = DATE, pos = DOUBLE)
quantile_cont(x = DATE, pos = `DOUBLE[]`)
quantile_cont(x = TIMESTAMP, pos = DOUBLE)
quantile_cont(x = TIMESTAMP, pos = `DOUBLE[]`)
quantile_cont(x = TIME, pos = DOUBLE)
quantile_cont(x = TIME, pos = `DOUBLE[]`)
quantile_cont(x = `TIMESTAMP WITH TIME ZONE`, pos = DOUBLE)
quantile_cont(x = `TIMESTAMP WITH TIME ZONE`, pos = `DOUBLE[]`)
quantile_cont(x = `TIME WITH TIME ZONE`, pos = DOUBLE)
quantile_cont(x = `TIME WITH TIME ZONE`, pos = `DOUBLE[]`)
quantile_cont(x, 0.5)
Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact quantiles.
x |
|
pos |
|
ANY
quantile_disc(x = ANY, pos = DOUBLE)
quantile_disc(x = ANY, pos = `DOUBLE[]`)
quantile_disc(x = ANY)
quantile_disc(x, 0.5)
Extract the quarter component from a date or timestamp.
ts |
|
BIGINT
quarter(ts = DATE)
quarter(ts = INTERVAL)
quarter(ts = TIMESTAMP)
quarter(ts = `TIMESTAMP WITH TIME ZONE`)
quarter(timestamp '2021-08-03 11:59:44.123456')
DuckDB function query().
query(col0 = VARCHAR)query(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function query_table().
col0 |
|
col1 |
|
Unspecified.
query_table(col0 = VARCHAR)
query_table(col0 = `VARCHAR[]`)
query_table(col0 = `VARCHAR[]`, col1 = BOOLEAN)
DuckDB function r_dataframe_scan().
r_dataframe_scan( col0 = POINTER, map_list_of = BOOLEAN, experimental = BOOLEAN, integer64 = BOOLEAN )r_dataframe_scan( col0 = POINTER, map_list_of = BOOLEAN, experimental = BOOLEAN, integer64 = BOOLEAN )
col0 |
|
map_list_of |
|
experimental |
|
integer64 |
|
Unspecified.
Converts degrees to radians.
radians(x = DOUBLE)radians(x = DOUBLE)
x |
|
DOUBLE
radians(90)
Returns a random number between 0 and 1.
random()random()
DOUBLE
random()
Creates a list of values between start and stop - the stop parameter is exclusive.
col0 |
|
col1 |
|
col2 |
|
start |
|
stop |
|
step |
|
BIGINT[] | TIMESTAMP[] | TIMESTAMP WITH TIME ZONE[]
range(col0 = BIGINT)
range(col0 = BIGINT, col1 = BIGINT)
range(col0 = BIGINT, col1 = BIGINT, col2 = BIGINT)
range(col0 = TIMESTAMP, col1 = TIMESTAMP, col2 = INTERVAL)
range(start = BIGINT)
range(start = BIGINT, stop = BIGINT)
range(start = BIGINT, stop = BIGINT, step = BIGINT)
range(start = TIMESTAMP, stop = TIMESTAMP, step = INTERVAL)
range(start = `TIMESTAMP WITH TIME ZONE`, stop = `TIMESTAMP WITH TIME ZONE`, step = INTERVAL)
range(2, 5, 3)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
repeat(),
unpivot_list()
DuckDB function rank_dense().
rank_dense()rank_dense()
BIGINT
DuckDB function read_blob().
col0 |
|
Unspecified.
read_blob(col0 = VARCHAR)
read_blob(col0 = `VARCHAR[]`)
DuckDB function read_csv().
col0 |
|
thousands |
|
strict_mode |
|
dtypes |
|
column_types |
|
null_padding |
|
column_names |
|
buffer_size |
|
parallel |
|
force_not_null |
|
hive_types |
|
new_line |
|
files_to_sniff |
|
dateformat |
|
delim |
|
sep |
|
decimal_separator |
|
nullstr |
|
escape |
|
compression |
|
encoding |
|
hive_types_autocast |
|
all_varchar |
|
columns |
|
hive_partitioning |
|
auto_detect |
|
comment |
|
quote |
|
max_line_size |
|
store_rejects |
|
union_by_name |
|
header |
|
types |
|
skip |
|
filename |
|
sample_size |
|
timestampformat |
|
normalize_names |
|
ignore_errors |
|
names |
|
allow_quoted_nulls |
|
maximum_line_size |
|
rejects_table |
|
auto_type_candidates |
|
rejects_scan |
|
rejects_limit |
|
Unspecified.
read_csv(col0 = VARCHAR, thousands = VARCHAR, strict_mode = BOOLEAN, dtypes = ANY, column_types = ANY, null_padding = BOOLEAN, column_names = `VARCHAR[]`, buffer_size = UBIGINT, parallel = BOOLEAN, force_not_null = `VARCHAR[]`, hive_types = ANY, new_line = VARCHAR, files_to_sniff = BIGINT, dateformat = VARCHAR, delim = VARCHAR, sep = VARCHAR, decimal_separator = VARCHAR, nullstr = ANY, escape = VARCHAR, compression = VARCHAR, encoding = VARCHAR, hive_types_autocast = BOOLEAN, all_varchar = BOOLEAN, columns = ANY, hive_partitioning = BOOLEAN, auto_detect = BOOLEAN, comment = VARCHAR, quote = VARCHAR, max_line_size = VARCHAR, store_rejects = BOOLEAN, union_by_name = BOOLEAN, header = BOOLEAN, types = ANY, skip = BIGINT, filename = ANY, sample_size = BIGINT, timestampformat = VARCHAR, normalize_names = BOOLEAN, ignore_errors = BOOLEAN, names = `VARCHAR[]`, allow_quoted_nulls = BOOLEAN, maximum_line_size = VARCHAR, rejects_table = VARCHAR, auto_type_candidates = ANY, rejects_scan = VARCHAR, rejects_limit = BIGINT)
read_csv(col0 = `VARCHAR[]`, thousands = VARCHAR, strict_mode = BOOLEAN, dtypes = ANY, column_types = ANY, null_padding = BOOLEAN, column_names = `VARCHAR[]`, buffer_size = UBIGINT, parallel = BOOLEAN, force_not_null = `VARCHAR[]`, hive_types = ANY, new_line = VARCHAR, files_to_sniff = BIGINT, dateformat = VARCHAR, delim = VARCHAR, sep = VARCHAR, decimal_separator = VARCHAR, nullstr = ANY, escape = VARCHAR, compression = VARCHAR, encoding = VARCHAR, hive_types_autocast = BOOLEAN, all_varchar = BOOLEAN, columns = ANY, hive_partitioning = BOOLEAN, auto_detect = BOOLEAN, comment = VARCHAR, quote = VARCHAR, max_line_size = VARCHAR, store_rejects = BOOLEAN, union_by_name = BOOLEAN, header = BOOLEAN, types = ANY, skip = BIGINT, filename = ANY, sample_size = BIGINT, timestampformat = VARCHAR, normalize_names = BOOLEAN, ignore_errors = BOOLEAN, names = `VARCHAR[]`, allow_quoted_nulls = BOOLEAN, maximum_line_size = VARCHAR, rejects_table = VARCHAR, auto_type_candidates = ANY, rejects_scan = VARCHAR, rejects_limit = BIGINT)
DuckDB function read_csv_auto().
col0 |
|
thousands |
|
strict_mode |
|
dtypes |
|
column_types |
|
null_padding |
|
column_names |
|
buffer_size |
|
parallel |
|
force_not_null |
|
hive_types |
|
new_line |
|
files_to_sniff |
|
dateformat |
|
delim |
|
sep |
|
decimal_separator |
|
nullstr |
|
escape |
|
compression |
|
encoding |
|
hive_types_autocast |
|
all_varchar |
|
columns |
|
hive_partitioning |
|
auto_detect |
|
comment |
|
quote |
|
max_line_size |
|
store_rejects |
|
union_by_name |
|
header |
|
types |
|
skip |
|
filename |
|
sample_size |
|
timestampformat |
|
normalize_names |
|
ignore_errors |
|
names |
|
allow_quoted_nulls |
|
maximum_line_size |
|
rejects_table |
|
auto_type_candidates |
|
rejects_scan |
|
rejects_limit |
|
Unspecified.
read_csv_auto(col0 = VARCHAR, thousands = VARCHAR, strict_mode = BOOLEAN, dtypes = ANY, column_types = ANY, null_padding = BOOLEAN, column_names = `VARCHAR[]`, buffer_size = UBIGINT, parallel = BOOLEAN, force_not_null = `VARCHAR[]`, hive_types = ANY, new_line = VARCHAR, files_to_sniff = BIGINT, dateformat = VARCHAR, delim = VARCHAR, sep = VARCHAR, decimal_separator = VARCHAR, nullstr = ANY, escape = VARCHAR, compression = VARCHAR, encoding = VARCHAR, hive_types_autocast = BOOLEAN, all_varchar = BOOLEAN, columns = ANY, hive_partitioning = BOOLEAN, auto_detect = BOOLEAN, comment = VARCHAR, quote = VARCHAR, max_line_size = VARCHAR, store_rejects = BOOLEAN, union_by_name = BOOLEAN, header = BOOLEAN, types = ANY, skip = BIGINT, filename = ANY, sample_size = BIGINT, timestampformat = VARCHAR, normalize_names = BOOLEAN, ignore_errors = BOOLEAN, names = `VARCHAR[]`, allow_quoted_nulls = BOOLEAN, maximum_line_size = VARCHAR, rejects_table = VARCHAR, auto_type_candidates = ANY, rejects_scan = VARCHAR, rejects_limit = BIGINT)
read_csv_auto(col0 = `VARCHAR[]`, thousands = VARCHAR, strict_mode = BOOLEAN, dtypes = ANY, column_types = ANY, null_padding = BOOLEAN, column_names = `VARCHAR[]`, buffer_size = UBIGINT, parallel = BOOLEAN, force_not_null = `VARCHAR[]`, hive_types = ANY, new_line = VARCHAR, files_to_sniff = BIGINT, dateformat = VARCHAR, delim = VARCHAR, sep = VARCHAR, decimal_separator = VARCHAR, nullstr = ANY, escape = VARCHAR, compression = VARCHAR, encoding = VARCHAR, hive_types_autocast = BOOLEAN, all_varchar = BOOLEAN, columns = ANY, hive_partitioning = BOOLEAN, auto_detect = BOOLEAN, comment = VARCHAR, quote = VARCHAR, max_line_size = VARCHAR, store_rejects = BOOLEAN, union_by_name = BOOLEAN, header = BOOLEAN, types = ANY, skip = BIGINT, filename = ANY, sample_size = BIGINT, timestampformat = VARCHAR, normalize_names = BOOLEAN, ignore_errors = BOOLEAN, names = `VARCHAR[]`, allow_quoted_nulls = BOOLEAN, maximum_line_size = VARCHAR, rejects_table = VARCHAR, auto_type_candidates = ANY, rejects_scan = VARCHAR, rejects_limit = BIGINT)
DuckDB function read_duckdb().
col0 |
|
table_name |
|
schema_name |
|
hive_types_autocast |
|
hive_types |
|
hive_partitioning |
|
union_by_name |
|
filename |
|
Unspecified.
read_duckdb(col0 = VARCHAR, table_name = VARCHAR, schema_name = VARCHAR, hive_types_autocast = BOOLEAN, hive_types = ANY, hive_partitioning = BOOLEAN, union_by_name = BOOLEAN, filename = ANY)
read_duckdb(col0 = `VARCHAR[]`, table_name = VARCHAR, schema_name = VARCHAR, hive_types_autocast = BOOLEAN, hive_types = ANY, hive_partitioning = BOOLEAN, union_by_name = BOOLEAN, filename = ANY)
DuckDB function read_parquet().
col0 |
|
can_have_nan |
|
filename |
|
union_by_name |
|
debug_use_openssl |
|
hive_partitioning |
|
parquet_version |
|
encryption_config |
|
hive_types_autocast |
|
binary_as_string |
|
explicit_cardinality |
|
compression |
|
file_row_number |
|
hive_types |
|
schema |
|
Unspecified.
read_parquet(col0 = VARCHAR, can_have_nan = BOOLEAN, filename = ANY, union_by_name = BOOLEAN, debug_use_openssl = BOOLEAN, hive_partitioning = BOOLEAN, parquet_version = VARCHAR, encryption_config = ANY, hive_types_autocast = BOOLEAN, binary_as_string = BOOLEAN, explicit_cardinality = UBIGINT, compression = VARCHAR, file_row_number = BOOLEAN, hive_types = ANY, schema = ANY)
read_parquet(col0 = `VARCHAR[]`, can_have_nan = BOOLEAN, filename = ANY, union_by_name = BOOLEAN, debug_use_openssl = BOOLEAN, hive_partitioning = BOOLEAN, parquet_version = VARCHAR, encryption_config = ANY, hive_types_autocast = BOOLEAN, binary_as_string = BOOLEAN, explicit_cardinality = UBIGINT, compression = VARCHAR, file_row_number = BOOLEAN, hive_types = ANY, schema = ANY)
DuckDB function read_text().
col0 |
|
Unspecified.
read_text(col0 = VARCHAR)
read_text(col0 = `VARCHAR[]`)
Escapes special patterns to turn string into a regular expression similarly to Python's re.escape function.
regexp_escape(string = VARCHAR)regexp_escape(string = VARCHAR)
string |
|
VARCHAR
regexp_escape('https://duckdb.org')
Other regex:
regexp_extract(),
regexp_extract_all(),
regexp_full_match(),
regexp_matches(),
regexp_replace(),
string_split_regex()
If string contains the regex pattern, returns the capturing group specified by optional parameter group; otherwise, returns the empty string. The group must be a constant value. If no group is given, it defaults to 0. A set of optional regex options can be set.
If string contains the regex pattern, returns the capturing groups as a struct with corresponding names from name_list; otherwise, returns a struct with the same keys and empty strings as values. A set of optional regex options can be set.
string |
|
regex |
|
group |
|
options |
|
name_list |
|
VARCHAR
regexp_extract(string = VARCHAR, regex = VARCHAR)
regexp_extract(string = VARCHAR, regex = VARCHAR, group = INTEGER)
regexp_extract(string = VARCHAR, regex = VARCHAR, group = INTEGER, options = VARCHAR)
regexp_extract(string = VARCHAR, regex = VARCHAR, name_list = `VARCHAR[]`)
regexp_extract(string = VARCHAR, regex = VARCHAR, name_list = `VARCHAR[]`, options = VARCHAR)
regexp_extract('abcde', '[a-z]{3}')
regexp_extract('abc', '([a-z])(b)', 1)
regexp_extract('ABC', '([a-z])(b)', 1, 'i')
regexp_extract('2023-04-15', '(\d+)-(\d+)-(\d+)', ['y', 'm', 'd'])
regexp_extract('John Doe', '([a-z]+) ([a-z]+)', ['first_name', 'last_name'], 'i')
Other regex:
regexp_escape(),
regexp_extract_all(),
regexp_full_match(),
regexp_matches(),
regexp_replace(),
string_split_regex()
Finds non-overlapping occurrences of the regex in the string and returns the corresponding values of the capturing group. A set of optional regex options can be set.
string |
|
regex |
|
group |
|
options |
|
VARCHAR[]
regexp_extract_all(string = VARCHAR, regex = VARCHAR)
regexp_extract_all(string = VARCHAR, regex = VARCHAR, group = INTEGER)
regexp_extract_all(string = VARCHAR, regex = VARCHAR, group = INTEGER, options = VARCHAR)
regexp_extract_all(string = VARCHAR, regex = VARCHAR, group = `VARCHAR[]`)
regexp_extract_all(string = VARCHAR, regex = VARCHAR, group = `VARCHAR[]`, options = VARCHAR)
regexp_extract_all('Peter: 33, Paul:14', '(\w+):\s*(\d+)', 2)
Other regex:
regexp_escape(),
regexp_extract(),
regexp_full_match(),
regexp_matches(),
regexp_replace(),
string_split_regex()
Returns true if the entire string matches the regex. A set of optional regex options can be set.
string |
|
regex |
|
col2 |
|
BOOLEAN
regexp_full_match(string = VARCHAR, regex = VARCHAR)
regexp_full_match(string = VARCHAR, regex = VARCHAR, col2 = VARCHAR)
regexp_full_match('anabanana', '(an)*')
Other regex:
regexp_escape(),
regexp_extract(),
regexp_extract_all(),
regexp_matches(),
regexp_replace(),
string_split_regex()
Returns true if string contains the regex, false otherwise. A set of optional regex options can be set.
string |
|
regex |
|
options |
|
BOOLEAN
regexp_matches(string = VARCHAR, regex = VARCHAR)
regexp_matches(string = VARCHAR, regex = VARCHAR, options = VARCHAR)
regexp_matches('anabanana', '(an)*')
Other regex:
regexp_escape(),
regexp_extract(),
regexp_extract_all(),
regexp_full_match(),
regexp_replace(),
string_split_regex()
If string contains the regex, replaces the matching part with replacement. A set of optional regex options can be set.
string |
|
regex |
|
replacement |
|
options |
|
VARCHAR
regexp_replace(string = VARCHAR, regex = VARCHAR, replacement = VARCHAR)
regexp_replace(string = VARCHAR, regex = VARCHAR, replacement = VARCHAR, options = VARCHAR)
regexp_replace('hello', '[lo]', '-')
Other regex:
regexp_escape(),
regexp_extract(),
regexp_extract_all(),
regexp_full_match(),
regexp_matches(),
string_split_regex()
DuckDB macro regexp_split_to_table().
regexp_split_to_table(text, pattern)regexp_split_to_table(text, pattern)
text |
Unspecified. |
pattern |
Unspecified. |
Unspecified.
Returns the average of the independent variable for non-NULL pairs in a group, where x is the independent variable and y is the dependent variable.
regr_avgx(y = DOUBLE, x = DOUBLE)regr_avgx(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
Returns the average of the dependent variable for non-NULL pairs in a group, where x is the independent variable and y is the dependent variable.
regr_avgy(y = DOUBLE, x = DOUBLE)regr_avgy(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
Returns the number of non-NULL number pairs in a group.
regr_count(y = DOUBLE, x = DOUBLE)regr_count(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
UINTEGER
(SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*)
Returns the intercept of the univariate linear regression line for non-NULL pairs in a group.
regr_intercept(y = DOUBLE, x = DOUBLE)regr_intercept(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
AVG(y)-REGR_SLOPE(y, x)*AVG(x)
Returns the coefficient of determination for non-NULL pairs in a group.
regr_r2(y = DOUBLE, x = DOUBLE)regr_r2(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
Returns the slope of the linear regression line for non-NULL pairs in a group.
regr_slope(y = DOUBLE, x = DOUBLE)regr_slope(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
COVAR_POP(x, y) / VAR_POP(x)
DuckDB function regr_sxx().
regr_sxx(y = DOUBLE, x = DOUBLE)regr_sxx(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
REGR_COUNT(y, x) * VAR_POP(x)
Returns the population covariance of input values.
regr_sxy(y = DOUBLE, x = DOUBLE)regr_sxy(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
REGR_COUNT(y, x) * COVAR_POP(y, x)
DuckDB function regr_syy().
regr_syy(y = DOUBLE, x = DOUBLE)regr_syy(y = DOUBLE, x = DOUBLE)
y |
|
x |
|
DOUBLE
REGR_COUNT(y, x) * VAR_POP(y)
Repeats the string count number of times.
Repeats the blob count number of times.
Repeats the list count number of times.
col0 |
|
col1 |
|
string |
|
count |
|
blob |
|
VARCHAR | BLOB | T[]
`repeat`(col0 = ANY, col1 = BIGINT)
`repeat`(string = VARCHAR, count = BIGINT)
`repeat`(blob = BLOB, count = BIGINT)
`repeat`(col0 = `T[]`, col1 = BIGINT)
repeat('A', 5)
repeat('\xAA\xBB'::BLOB, 5)
repeat([1, 2, 3], 5)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
sha1(),
sha256(),
to_base64(),
unbin(),
unhex()
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
unpivot_list()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function repeat_row().
repeat_row(num_rows = BIGINT)repeat_row(num_rows = BIGINT)
num_rows |
|
Unspecified.
Replaces any occurrences of the source with target in string.
replace(string = VARCHAR, source = VARCHAR, target = VARCHAR)replace(string = VARCHAR, source = VARCHAR, target = VARCHAR)
string |
|
source |
|
target |
|
VARCHAR
replace('hello', 'l', '-')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Casts all fields of type1 to type2.
replace_type(param = ANY, type1 = ANY, type2 = ANY)replace_type(param = ANY, type1 = ANY, type2 = ANY)
param |
|
type1 |
|
type2 |
|
ANY
replace_type({duck: 3.141592653589793::DOUBLE}, NULL::DOUBLE, NULL::DECIMAL(15,2))
Gives the approximate quantile using reservoir sampling, the sample size is optional and uses 8192 as a default size.
x |
|
quantile |
|
sample_size |
|
DECIMAL | DECIMAL[] | TINYINT | TINYINT[] | SMALLINT | SMALLINT[] | INTEGER | INTEGER[] | BIGINT | BIGINT[] | HUGEINT | HUGEINT[] | FLOAT | FLOAT[] | DOUBLE | DOUBLE[]
reservoir_quantile(x = DECIMAL, quantile = DOUBLE)
reservoir_quantile(x = DECIMAL, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = DECIMAL, quantile = `DOUBLE[]`)
reservoir_quantile(x = DECIMAL, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = TINYINT, quantile = DOUBLE)
reservoir_quantile(x = TINYINT, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = TINYINT, quantile = `DOUBLE[]`)
reservoir_quantile(x = TINYINT, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = SMALLINT, quantile = DOUBLE)
reservoir_quantile(x = SMALLINT, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = SMALLINT, quantile = `DOUBLE[]`)
reservoir_quantile(x = SMALLINT, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = INTEGER, quantile = DOUBLE)
reservoir_quantile(x = INTEGER, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = INTEGER, quantile = `DOUBLE[]`)
reservoir_quantile(x = INTEGER, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = BIGINT, quantile = DOUBLE)
reservoir_quantile(x = BIGINT, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = BIGINT, quantile = `DOUBLE[]`)
reservoir_quantile(x = BIGINT, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = HUGEINT, quantile = DOUBLE)
reservoir_quantile(x = HUGEINT, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = HUGEINT, quantile = `DOUBLE[]`)
reservoir_quantile(x = HUGEINT, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = FLOAT, quantile = DOUBLE)
reservoir_quantile(x = FLOAT, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = FLOAT, quantile = `DOUBLE[]`)
reservoir_quantile(x = FLOAT, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(x = DOUBLE, quantile = DOUBLE)
reservoir_quantile(x = DOUBLE, quantile = DOUBLE, sample_size = INTEGER)
reservoir_quantile(x = DOUBLE, quantile = `DOUBLE[]`)
reservoir_quantile(x = DOUBLE, quantile = `DOUBLE[]`, sample_size = INTEGER)
reservoir_quantile(A, 0.5, 1024)
Reverses the string.
reverse(string = VARCHAR)reverse(string = VARCHAR)
string |
|
VARCHAR
reverse('hello')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Extract the right-most count characters.
right(string = VARCHAR, count = BIGINT)right(string = VARCHAR, count = BIGINT)
string |
|
count |
|
VARCHAR
right('Hello🦆', 3)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Extracts the right-most count grapheme clusters.
right_grapheme(string = VARCHAR, count = BIGINT)right_grapheme(string = VARCHAR, count = BIGINT)
string |
|
count |
|
VARCHAR
right_grapheme('🤦🏼♂️🤦🏽♀️', 1)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Rounds x to s decimal places.
x |
|
precision |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL
round(x = TINYINT)
round(x = TINYINT, precision = INTEGER)
round(x = SMALLINT)
round(x = SMALLINT, precision = INTEGER)
round(x = INTEGER)
round(x = INTEGER, precision = INTEGER)
round(x = BIGINT)
round(x = BIGINT, precision = INTEGER)
round(x = HUGEINT)
round(x = HUGEINT, precision = INTEGER)
round(x = FLOAT)
round(x = FLOAT, precision = INTEGER)
round(x = DOUBLE)
round(x = DOUBLE, precision = INTEGER)
round(x = DECIMAL)
round(x = DECIMAL, precision = INTEGER)
round(42.4332, 2)
DuckDB macro round_even().
round_even(x, n)round_even(x, n)
x |
Unspecified. |
n |
Unspecified. |
Unspecified.
DuckDB macro roundbankers().
roundbankers(x, n)roundbankers(x, n)
x |
Unspecified. |
n |
Unspecified. |
Unspecified.
Create an unnamed STRUCT (tuple) containing the argument values.
row()row()
STRUCT
row(i, i % 4, i / 4)
DuckDB function row_number().
row_number()row_number()
BIGINT
Pads the string with the character on the right until it has count characters. Truncates the string on the right if it has more than count characters.
rpad(string = VARCHAR, count = INTEGER, character = VARCHAR)rpad(string = VARCHAR, count = INTEGER, character = VARCHAR)
string |
|
count |
|
character |
|
VARCHAR
rpad('hello', 10, '<')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Removes any occurrences of any of the characters from the right side of the string. characters defaults to space.
string |
|
characters |
|
VARCHAR
rtrim(string = VARCHAR)
rtrim(string = VARCHAR, characters = VARCHAR)
rtrim(' test ')
rtrim('>>>>test<<', '><')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Extract the second component from a date or timestamp.
ts |
|
BIGINT
second(ts = DATE)
second(ts = INTERVAL)
second(ts = TIME)
second(ts = TIMESTAMP)
second(ts = `TIME WITH TIME ZONE`)
second(ts = TIME_NS)
second(ts = `TIMESTAMP WITH TIME ZONE`)
second(timestamp '2021-08-03 11:59:44.123456')
Returns the standard error of the mean.
sem(x = DOUBLE)sem(x = DOUBLE)
x |
|
DOUBLE
DuckDB function seq_scan().
seq_scan()seq_scan()
Unspecified.
DuckDB macro session_user().
session_user()session_user()
Unspecified.
Sets the nth bit in bitstring to newvalue; the first (leftmost) bit is indexed 0. Returns a new bitstring.
set_bit(bitstring = BIT, index = INTEGER, new_value = INTEGER)set_bit(bitstring = BIT, index = INTEGER, new_value = INTEGER)
bitstring |
|
index |
|
new_value |
|
BIT
set_bit('0110010'::BIT, 2, 0)
Sets the seed to be used for the random function.
setseed(col0 = DOUBLE)setseed(col0 = DOUBLE)
col0 |
|
"NULL"
setseed(0.42)
Returns a VARCHAR with the SHA-1 hash of the value.
Returns a VARCHAR with the SHA-1 hash of the blob.
value |
|
blob |
|
VARCHAR
sha1(value = VARCHAR)
sha1(blob = BLOB)
sha1('🦆')
sha1('\xAA\xBB'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha256(),
to_base64(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns a VARCHAR with the SHA-256 hash of the value.
Returns a VARCHAR with the SHA-256 hash of the blob.
value |
|
blob |
|
VARCHAR
sha256(value = VARCHAR)
sha256(blob = BLOB)
sha256('🦆')
sha256('\xAA\xBB'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
to_base64(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB macro shobj_description().
shobj_description(object_oid, catalog_name)shobj_description(object_oid, catalog_name)
object_oid |
Unspecified. |
catalog_name |
Unspecified. |
Unspecified.
DuckDB function show().
show(col0 = VARCHAR)show(col0 = VARCHAR)
col0 |
|
Unspecified.
DuckDB function show_databases().
show_databases()show_databases()
Unspecified.
DuckDB function show_tables().
show_tables()show_tables()
Unspecified.
DuckDB function show_tables_expanded().
show_tables_expanded()show_tables_expanded()
Unspecified.
Returns the sign of x as -1, 0 or 1.
x |
|
TINYINT
sign(x = TINYINT)
sign(x = SMALLINT)
sign(x = INTEGER)
sign(x = BIGINT)
sign(x = HUGEINT)
sign(x = FLOAT)
sign(x = DOUBLE)
sign(x = UTINYINT)
sign(x = USMALLINT)
sign(x = UINTEGER)
sign(x = UBIGINT)
sign(x = UHUGEINT)
sign(-349)
Returns whether the signbit is set or not.
x |
|
BOOLEAN
signbit(x = FLOAT)
signbit(x = DOUBLE)
signbit(-0.0)
Computes the sin of x.
sin(x = DOUBLE)sin(x = DOUBLE)
x |
|
DOUBLE
sin(90)
Computes the hyperbolic sin of x.
sinh(x = DOUBLE)sinh(x = DOUBLE)
x |
|
DOUBLE
sinh(1)
Returns the skewness of all input values.
skewness(x = DOUBLE)skewness(x = DOUBLE)
x |
|
DOUBLE
skewness(A)
Sleeps for the specified number of milliseconds and returns NULL.
sleep_ms(milliseconds = BIGINT)sleep_ms(milliseconds = BIGINT)
milliseconds |
|
"NULL"
sleep_ms(100)
DuckDB macro split_part().
split_part(string, delimiter, position)split_part(string, delimiter, position)
string |
Unspecified. |
delimiter |
Unspecified. |
position |
Unspecified. |
Unspecified.
Returns the square root of x.
sqrt(x = DOUBLE)sqrt(x = DOUBLE)
x |
|
DOUBLE
sqrt(4)
Returns the Well-Known Text (WKT) representation of the geometry.
st_astext(geom = GEOMETRY)st_astext(geom = GEOMETRY)
geom |
|
VARCHAR
ST_AsText(ST_GeomFromWKB(X'01010000000000000000000000000000000000000000000000'))
Other geometry:
st_aswkb(),
st_crs(),
st_geomfromwkb()
Returns the Well-Known Binary (WKB) representation of the geometry.
st_aswkb(geom = GEOMETRY)st_aswkb(geom = GEOMETRY)
geom |
|
BLOB
st_aswkb(ST_GeomFromWKB(X'01010000000000000000000000000000000000000000000000000'))
Other geometry:
st_astext(),
st_crs(),
st_geomfromwkb()
Returns the Coordinate Reference System (CRS) identifier of the geometry.
st_crs(geom = GEOMETRY)st_crs(geom = GEOMETRY)
geom |
|
VARCHAR
Other geometry:
st_astext(),
st_aswkb(),
st_geomfromwkb()
Creates a geometry from Well-Known Binary (WKB) representation.
st_geomfromwkb(wkb = BLOB)st_geomfromwkb(wkb = BLOB)
wkb |
|
GEOMETRY
ST_GeomFromWKB(X'01010000000000000000000000000000000000000000000000')
Other geometry:
st_astext(),
st_aswkb(),
st_crs()
Sets the Coordinate Reference System (CRS) identifier of the geometry.
st_setcrs(geom = GEOMETRY, crs = VARCHAR)st_setcrs(geom = GEOMETRY, crs = VARCHAR)
geom |
|
crs |
|
GEOMETRY
Returns true if string begins with search_string.
starts_with(string = VARCHAR, search_string = VARCHAR)starts_with(string = VARCHAR, search_string = VARCHAR)
string |
|
search_string |
|
BOOLEAN
starts_with('abc', 'a')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
stats(expression = ANY)stats(expression = ANY)
expression |
|
VARCHAR
stats(5)
Returns the population standard deviation.
stddev_pop(x = DOUBLE)stddev_pop(x = DOUBLE)
x |
|
DOUBLE
sqrt(var_pop(x))
Returns the sample standard deviation.
stddev_samp(x = DOUBLE)stddev_samp(x = DOUBLE)
x |
|
DOUBLE
sqrt(var_samp(x))
DuckDB function storage_info().
storage_info(col0 = VARCHAR)storage_info(col0 = VARCHAR)
col0 |
|
Unspecified.
Converts a date to a string according to the format string.
data |
|
format |
|
VARCHAR
strftime(data = DATE, format = VARCHAR)
strftime(data = TIMESTAMP, format = VARCHAR)
strftime(data = TIMESTAMP_NS, format = VARCHAR)
strftime(data = VARCHAR, format = DATE)
strftime(data = VARCHAR, format = TIMESTAMP)
strftime(data = VARCHAR, format = TIMESTAMP_NS)
strftime(data = `TIMESTAMP WITH TIME ZONE`, format = VARCHAR)
strftime(data = VARCHAR, format = `TIMESTAMP WITH TIME ZONE`)
strftime(date '1992-01-01', '%a, %-d %B %Y')
Concatenates the column string values with an optional separator.
str |
|
arg |
|
VARCHAR
string_agg(str = ANY)
string_agg(str = ANY, arg = VARCHAR)
string_agg(A, '-')
Splits the string along the separator.
string_split(string = VARCHAR, separator = VARCHAR)string_split(string = VARCHAR, separator = VARCHAR)
string |
|
separator |
|
VARCHAR[]
string_split('hello-world', '-')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Splits the string along the regex. A set of optional regex options can be set.
string |
|
regex |
|
options |
|
VARCHAR[]
string_split_regex(string = VARCHAR, regex = VARCHAR)
string_split_regex(string = VARCHAR, regex = VARCHAR, options = VARCHAR)
string_split_regex('hello world; 42', ';? ')
Other regex:
regexp_escape(),
regexp_extract(),
regexp_extract_all(),
regexp_full_match(),
regexp_matches(),
regexp_replace()
Strips accents from string.
strip_accents(string = VARCHAR)strip_accents(string = VARCHAR)
string |
|
VARCHAR
strip_accents('mühleisen')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Number of bytes in string.
strlen(string = VARCHAR)strlen(string = VARCHAR)
string |
|
BIGINT
strlen('🦆')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts the string text to timestamp according to the format string. Throws an error on failure. To return NULL on failure, use try_strptime.
Converts the string text to timestamp applying the format strings in the list until one succeeds. Throws an error on failure. To return NULL on failure, use try_strptime.
text |
|
format |
|
format-list |
|
TIMESTAMP
strptime(text = VARCHAR, format = VARCHAR)
strptime(text = VARCHAR, `format-list` = `VARCHAR[]`)
strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')
strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S'])
Merge the multiple STRUCTs into a single STRUCT.
struct_concat()struct_concat()
STRUCT
struct_concat(struct_pack(i := 4), struct_pack(s := 'string'))
Check if an unnamed STRUCT contains the value.
struct_contains(struct = STRUCT, `'entry'` = ANY)struct_contains(struct = STRUCT, `'entry'` = ANY)
struct |
|
'entry' |
|
BOOLEAN
struct_contains(ROW(3, 3, 0), 3)
Extract the named entry from the STRUCT.
struct |
|
'entry' |
|
ANY
struct_extract(struct = STRUCT, `'entry'` = VARCHAR)
struct_extract(struct = STRUCT, `'entry'` = BIGINT)
struct_extract({'i': 3, 'v2': 3, 'v3': 0}, 'i')
Adds field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s).
struct_insert()struct_insert()
STRUCT
struct_insert({'a': 1}, b := 2)
Returns the field names of a STRUCT as a list.
struct_keys(struct = ANY)struct_keys(struct = ANY)
struct |
|
VARCHAR[]
struct_keys({'a': 1, 'b': 2})
Create a STRUCT containing the argument values. The entry name will be the bound variable name.
struct_pack()struct_pack()
STRUCT
struct_pack(i := 4, s := 'string')
Get the position of the entry in an unnamed STRUCT, starting at 1.
struct_position(struct = STRUCT, `'entry'` = ANY)struct_position(struct = STRUCT, `'entry'` = ANY)
struct |
|
'entry' |
|
INTEGER
struct_position(ROW(3, 3, 0), 3)
Changes field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s).
struct_update()struct_update()
STRUCT
struct_update({'a': 1}, a := 2)
Returns the field values of a STRUCT as an UnnamedStruct.
struct_values(struct = STRUCT)struct_values(struct = STRUCT)
struct |
|
STRUCT
struct_values({'a': 1, 'b': 'world'})
Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.
string |
|
start |
|
length |
|
VARCHAR
substring(string = VARCHAR, start = BIGINT, length = BIGINT)
substring(string = VARCHAR, start = BIGINT)
substring('Hello', 2)
substring('Hello', 2, 2)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Extracts substring starting from grapheme clusters start up to the end of the string. If optional argument length is set, extracts a substring of length grapheme clusters instead. Note that a start value of 1 refers to the first character of the string.
string |
|
start |
|
length |
|
VARCHAR
substring_grapheme(string = VARCHAR, start = BIGINT, length = BIGINT)
substring_grapheme(string = VARCHAR, start = BIGINT)
substring_grapheme('🦆🤦🏼♂️🤦🏽♀️🦆', 3)
substring_grapheme('🦆🤦🏼♂️🤦🏽♀️🦆', 3, 2)
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
DuckDB function subtract().
col0 |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIGNUM | DATE | INTERVAL | TIMESTAMP | TIME | TIME WITH TIME ZONE
subtract(col0 = TINYINT)
subtract(col0 = TINYINT, col1 = TINYINT)
subtract(col0 = SMALLINT)
subtract(col0 = SMALLINT, col1 = SMALLINT)
subtract(col0 = INTEGER)
subtract(col0 = INTEGER, col1 = INTEGER)
subtract(col0 = BIGINT)
subtract(col0 = BIGINT, col1 = BIGINT)
subtract(col0 = HUGEINT)
subtract(col0 = HUGEINT, col1 = HUGEINT)
subtract(col0 = FLOAT)
subtract(col0 = FLOAT, col1 = FLOAT)
subtract(col0 = DOUBLE)
subtract(col0 = DOUBLE, col1 = DOUBLE)
subtract(col0 = DECIMAL)
subtract(col0 = DECIMAL, col1 = DECIMAL)
subtract(col0 = UTINYINT)
subtract(col0 = UTINYINT, col1 = UTINYINT)
subtract(col0 = USMALLINT)
subtract(col0 = USMALLINT, col1 = USMALLINT)
subtract(col0 = UINTEGER)
subtract(col0 = UINTEGER, col1 = UINTEGER)
subtract(col0 = UBIGINT)
subtract(col0 = UBIGINT, col1 = UBIGINT)
subtract(col0 = UHUGEINT)
subtract(col0 = UHUGEINT, col1 = UHUGEINT)
subtract(col0 = BIGNUM)
subtract(col0 = BIGNUM, col1 = BIGNUM)
subtract(col0 = DATE, col1 = DATE)
subtract(col0 = DATE, col1 = INTEGER)
subtract(col0 = TIMESTAMP, col1 = TIMESTAMP)
subtract(col0 = INTERVAL, col1 = INTERVAL)
subtract(col0 = DATE, col1 = INTERVAL)
subtract(col0 = TIME, col1 = INTERVAL)
subtract(col0 = TIMESTAMP, col1 = INTERVAL)
subtract(col0 = `TIME WITH TIME ZONE`, col1 = INTERVAL)
subtract(col0 = INTERVAL)
Returns true if string ends with search_string.
suffix(string = VARCHAR, search_string = VARCHAR)suffix(string = VARCHAR, search_string = VARCHAR)
string |
|
search_string |
|
BOOLEAN
suffix('abc', 'bc')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Calculates the sum value for all tuples in arg.
arg |
|
DECIMAL | HUGEINT | DOUBLE | BIGNUM
sum(arg = DECIMAL)
sum(arg = BOOLEAN)
sum(arg = SMALLINT)
sum(arg = INTEGER)
sum(arg = BIGINT)
sum(arg = HUGEINT)
sum(arg = DOUBLE)
sum(arg = BIGNUM)
sum(A)
Internal only. Calculates the sum value for all tuples in arg without overflow checks.
arg |
|
HUGEINT | DECIMAL
sum_no_overflow(arg = INTEGER)
sum_no_overflow(arg = BIGINT)
sum_no_overflow(arg = DECIMAL)
sum_no_overflow(A)
DuckDB function summary().
summary(col0 = TABLE)summary(col0 = TABLE)
col0 |
|
Unspecified.
Creates a switch statement similar to CASE WHEN/THEN.
key |
|
map |
|
value |
|
V
switch(key = K, map = `MAP(K, V)`)
switch(key = K, map = `MAP(K, V)`, value = V)
switch(key = `MAP(K, V)`, map = V)
switch(key = `MAP(K, V)`)
switch(x, map({1 : 1}, default)
DuckDB function table_info().
table_info(col0 = VARCHAR)table_info(col0 = VARCHAR)
col0 |
|
Unspecified.
Computes the tan of x.
tan(x = DOUBLE)tan(x = DOUBLE)
x |
|
DOUBLE
tan(90)
Computes the hyperbolic tan of x.
tanh(x = DOUBLE)tanh(x = DOUBLE)
x |
|
DOUBLE
tanh(1)
DuckDB function test_all_types().
test_all_types(use_large_bignum = BOOLEAN, use_large_enum = BOOLEAN)test_all_types(use_large_bignum = BOOLEAN, use_large_enum = BOOLEAN)
use_large_bignum |
|
use_large_enum |
|
Unspecified.
DuckDB function test_vector_types().
test_vector_types(col0 = ANY, all_flat = BOOLEAN)test_vector_types(col0 = ANY, all_flat = BOOLEAN)
col0 |
|
all_flat |
|
Unspecified.
Truncate TIMESTAMPTZ by the specified interval bucket_width. Buckets are aligned relative to origin TIMESTAMPTZ. The origin defaults to 2000-01-03 00:00:00+00 for buckets that do not include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets.
bucket_width |
|
timestamp |
|
origin |
|
DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE
time_bucket(bucket_width = INTERVAL, timestamp = DATE)
time_bucket(bucket_width = INTERVAL, timestamp = DATE, origin = DATE)
time_bucket(bucket_width = INTERVAL, timestamp = DATE, origin = INTERVAL)
time_bucket(bucket_width = INTERVAL, timestamp = TIMESTAMP)
time_bucket(bucket_width = INTERVAL, timestamp = TIMESTAMP, origin = INTERVAL)
time_bucket(bucket_width = INTERVAL, timestamp = TIMESTAMP, origin = TIMESTAMP)
time_bucket(bucket_width = INTERVAL, timestamp = `TIMESTAMP WITH TIME ZONE`)
time_bucket(bucket_width = INTERVAL, timestamp = `TIMESTAMP WITH TIME ZONE`, origin = INTERVAL)
time_bucket(bucket_width = INTERVAL, timestamp = `TIMESTAMP WITH TIME ZONE`, origin = `TIMESTAMP WITH TIME ZONE`)
time_bucket(bucket_width = INTERVAL, timestamp = `TIMESTAMP WITH TIME ZONE`, origin = VARCHAR)
time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00-07', TIMESTAMP '1992-04-01 00:00:00-07')
Converts a TIME WITH TIME ZONE to an integer sort key.
timetz_byte_comparable(time_tz = `TIME WITH TIME ZONE`)timetz_byte_comparable(time_tz = `TIME WITH TIME ZONE`)
time_tz |
|
UBIGINT
timetz_byte_comparable('18:18:16.21-07:00'::TIMETZ)
Extract the timezone component from a date or timestamp.
ts |
|
col1 |
|
BIGINT | TIME WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | TIMESTAMP
timezone(ts = DATE)
timezone(ts = INTERVAL)
timezone(ts = INTERVAL, col1 = `TIME WITH TIME ZONE`)
timezone(ts = TIMESTAMP)
timezone(ts = `TIMESTAMP WITH TIME ZONE`)
timezone(ts = VARCHAR, col1 = TIMESTAMP)
timezone(ts = VARCHAR, col1 = `TIMESTAMP WITH TIME ZONE`)
timezone(ts = VARCHAR, col1 = `TIME WITH TIME ZONE`)
timezone(timestamp '2021-08-03 11:59:44.123456')
Extract the timezone_hour component from a date or timestamp.
ts |
|
BIGINT
timezone_hour(ts = DATE)
timezone_hour(ts = INTERVAL)
timezone_hour(ts = TIMESTAMP)
timezone_hour(ts = `TIMESTAMP WITH TIME ZONE`)
timezone_hour(timestamp '2021-08-03 11:59:44.123456')
Extract the timezone_minute component from a date or timestamp.
ts |
|
BIGINT
timezone_minute(ts = DATE)
timezone_minute(ts = INTERVAL)
timezone_minute(ts = TIMESTAMP)
timezone_minute(ts = `TIMESTAMP WITH TIME ZONE`)
timezone_minute(timestamp '2021-08-03 11:59:44.123456')
Converts number to a string in the given base radix, optionally padding with leading zeros to min_length.
number |
|
radix |
|
min_length |
|
VARCHAR
to_base(number = BIGINT, radix = INTEGER)
to_base(number = BIGINT, radix = INTEGER, min_length = INTEGER)
to_base(42, 16, 5)
Other numeric:
bin(),
bit_length(),
formatReadableDecimalSize(),
format_bytes(),
greatest(),
hex(),
least(),
len(),
parse_formatted_bytes()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts a blob to a base64 encoded string.
to_base64(blob = BLOB)to_base64(blob = BLOB)
blob |
|
VARCHAR
to_base64('A'::BLOB)
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
unbin(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Construct a century interval.
integer |
|
INTERVAL
to_centuries(integer = INTEGER)
to_centuries(integer = BIGINT)
to_centuries(5)
Construct a day interval.
integer |
|
INTERVAL
to_days(integer = INTEGER)
to_days(integer = BIGINT)
to_days(5)
Construct a decade interval.
integer |
|
INTERVAL
to_decades(integer = INTEGER)
to_decades(integer = BIGINT)
to_decades(5)
Construct a hour interval.
to_hours(integer = BIGINT)to_hours(integer = BIGINT)
integer |
|
INTERVAL
to_hours(5)
Construct a microsecond interval.
to_microseconds(integer = BIGINT)to_microseconds(integer = BIGINT)
integer |
|
INTERVAL
to_microseconds(5)
Construct a millenium interval.
integer |
|
INTERVAL
to_millennia(integer = INTEGER)
to_millennia(integer = BIGINT)
to_millennia(1)
Construct a millisecond interval.
to_milliseconds(double = DOUBLE)to_milliseconds(double = DOUBLE)
double |
|
INTERVAL
to_milliseconds(5.5)
Construct a minute interval.
to_minutes(integer = BIGINT)to_minutes(integer = BIGINT)
integer |
|
INTERVAL
to_minutes(5)
Construct a month interval.
integer |
|
INTERVAL
to_months(integer = INTEGER)
to_months(integer = BIGINT)
to_months(5)
Construct a quarter interval.
integer |
|
INTERVAL
to_quarters(integer = INTEGER)
to_quarters(integer = BIGINT)
to_quarters(5)
Construct a second interval.
to_seconds(double = DOUBLE)to_seconds(double = DOUBLE)
double |
|
INTERVAL
to_seconds(5.5)
Converts secs since epoch to a timestamp with time zone.
to_timestamp(sec = DOUBLE)to_timestamp(sec = DOUBLE)
sec |
|
TIMESTAMP WITH TIME ZONE
to_timestamp(1284352323.5)
Construct a week interval.
integer |
|
INTERVAL
to_weeks(integer = INTEGER)
to_weeks(integer = BIGINT)
to_weeks(5)
Construct a year interval.
integer |
|
INTERVAL
to_years(integer = INTEGER)
to_years(integer = BIGINT)
to_years(5)
Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.
translate(string = VARCHAR, from = VARCHAR, to = VARCHAR)translate(string = VARCHAR, from = VARCHAR, to = VARCHAR)
string |
|
from |
|
to |
|
VARCHAR
translate('12345', '143', 'ax')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Removes any occurrences of any of the characters from either side of the string. characters defaults to space.
string |
|
characters |
|
VARCHAR
trim(string = VARCHAR)
trim(string = VARCHAR, characters = VARCHAR)
trim(' test ')
trim('>>>>test<<', '><')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Truncates the number.
x |
|
col1 |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | FLOAT | DOUBLE | DECIMAL | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT
trunc(x = TINYINT)
trunc(x = TINYINT, col1 = INTEGER)
trunc(x = SMALLINT)
trunc(x = SMALLINT, col1 = INTEGER)
trunc(x = INTEGER)
trunc(x = INTEGER, col1 = INTEGER)
trunc(x = BIGINT)
trunc(x = BIGINT, col1 = INTEGER)
trunc(x = HUGEINT)
trunc(x = HUGEINT, col1 = INTEGER)
trunc(x = FLOAT)
trunc(x = FLOAT, col1 = INTEGER)
trunc(x = DOUBLE)
trunc(x = DOUBLE, col1 = INTEGER)
trunc(x = DECIMAL)
trunc(x = DECIMAL, col1 = INTEGER)
trunc(x = UTINYINT)
trunc(x = UTINYINT, col1 = INTEGER)
trunc(x = USMALLINT)
trunc(x = USMALLINT, col1 = INTEGER)
trunc(x = UINTEGER)
trunc(x = UINTEGER, col1 = INTEGER)
trunc(x = UBIGINT)
trunc(x = UBIGINT, col1 = INTEGER)
trunc(x = UHUGEINT)
trunc(x = UHUGEINT, col1 = INTEGER)
trunc(17.4)
DuckDB function truncate_duckdb_logs().
truncate_duckdb_logs()truncate_duckdb_logs()
Unspecified.
Converts the string text to timestamp according to the format string. Returns NULL on failure.
text |
|
format |
|
TIMESTAMP
try_strptime(text = VARCHAR, format = VARCHAR)
try_strptime(text = VARCHAR, format = `VARCHAR[]`)
try_strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')
Returns the current transaction’s ID (a BIGINT). It will assign a new one if the current transaction does not have one already.
txid_current()txid_current()
UBIGINT
txid_current()
Returns the name of the data type of the result of the expression.
typeof(expression = ANY)typeof(expression = ANY)
expression |
|
VARCHAR
typeof('abc')
Converts a value from binary representation to a blob.
unbin(value = VARCHAR)unbin(value = VARCHAR)
value |
|
BLOB
unbin('0110')
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unhex()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unhex(),
unicode(),
upper(),
url_decode(),
url_encode()
Converts a value from hexadecimal representation to a blob.
unhex(value = VARCHAR)unhex(value = VARCHAR)
value |
|
BLOB
unhex('2A')
Other blob:
decode(),
encode(),
from_base64(),
hex(),
md5(),
md5_number(),
octet_length(),
or–or,
repeat(),
sha1(),
sha256(),
to_base64(),
unbin()
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unicode(),
upper(),
url_decode(),
url_encode()
Returns an INTEGER representing the unicode codepoint of the first character in the string.
unicode(string = VARCHAR)unicode(string = VARCHAR)
string |
|
INTEGER
[unicode('âbcd'), unicode('â'), unicode(''), unicode(NULL)]
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
upper(),
url_decode(),
url_encode()
Extract the value with the named tags from the union. NULL if the tag is not currently selected.
union_extract(union = UNION, tag = VARCHAR)union_extract(union = UNION, tag = VARCHAR)
union |
|
tag |
|
ANY
union_extract(s, 'k')
Retrieve the currently selected tag of the union as an ENUM.
union_tag(union = UNION)union_tag(union = UNION)
union |
|
ANY
union_tag(union_value(k := 'foo'))
Create a single member UNION containing the argument value. The tag of the value will be the bound variable name.
union_value()union_value()
UNION
union_value(k := 'hello')
DuckDB function unnest().
unnest(col0 = ANY)unnest(col0 = ANY)
col0 |
|
Unspecified.
Identical to list_value, but generated as part of unpivot for better error messages.
unpivot_list()unpivot_list()
LIST
unpivot_list(4, 5, 6)
Other list:
array_extract(),
array_length(),
concat(),
contains(),
flatten(),
generate_series(),
len(),
list_aggregate(),
list_concat(),
list_contains(),
list_cosine_distance(),
list_cosine_similarity(),
list_distance(),
list_distinct(),
list_extract(),
list_filter(),
list_grade_up(),
list_has_all(),
list_has_any(),
list_inner_product(),
list_intersect(),
list_negative_inner_product(),
list_position(),
list_reduce(),
list_resize(),
list_reverse_sort(),
list_select(),
list_slice(),
list_sort(),
list_transform(),
list_unique(),
list_value(),
list_where(),
list_zip(),
or–or,
range(),
repeat()
Converts string to upper case.
upper(string = VARCHAR)upper(string = VARCHAR)
string |
|
VARCHAR
upper('Hello')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
url_decode(),
url_encode()
Decodes a URL from a representation using Percent-Encoding.
url_decode(string = VARCHAR)url_decode(string = VARCHAR)
string |
|
VARCHAR
url_decode('https%3A%2F%2Fduckdb.org%2Fwhy_duckdb%23portable')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_encode()
Encodes a URL to a representation using Percent-Encoding.
url_encode(string = VARCHAR)url_encode(string = VARCHAR)
string |
|
VARCHAR
url_encode('this string has/ special+ characters>')
Other string:
array_extract(),
ascii(),
bar(),
bin(),
bit_length(),
chr(),
concat(),
concat_ws(),
contains(),
formatReadableDecimalSize(),
format_bytes(),
from_base64(),
greatest(),
hash(),
hex(),
ilike_escape(),
instr(),
least(),
left(),
left_grapheme(),
len(),
length_grapheme(),
like_escape(),
list_slice(),
lower(),
lpad(),
ltrim(),
md5(),
md5_number(),
nfc_normalize(),
not_ilike_escape(),
not_like_escape(),
or–or,
parse_dirname(),
parse_dirpath(),
parse_filename(),
parse_formatted_bytes(),
parse_path(),
prefix(),
printf(),
repeat(),
replace(),
reverse(),
right(),
right_grapheme(),
rpad(),
rtrim(),
sha1(),
sha256(),
starts_with(),
string_split(),
strip_accents(),
strlen(),
substring(),
substring_grapheme(),
suffix(),
to_base(),
to_base64(),
translate(),
trim(),
unbin(),
unhex(),
unicode(),
upper(),
url_decode()
DuckDB function user_agent().
user_agent()user_agent()
Unspecified.
Returns a random UUID v4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
uuid()uuid()
UUID
uuid()
Extract the timestamp for the given UUID v7.
uuid_extract_timestamp(uuid = UUID)uuid_extract_timestamp(uuid = UUID)
uuid |
|
TIMESTAMP WITH TIME ZONE
uuid_extract_timestamp('019482e4-1441-7aad-8127-eec99573b0a0')
Extract a version for the given UUID.
uuid_extract_version(uuid = UUID)uuid_extract_version(uuid = UUID)
uuid |
|
UINTEGER
uuid_extract_version('019482e4-1441-7aad-8127-eec99573b0a0')
Returns a random UUIDv4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
uuidv4()uuidv4()
UUID
uuidv4()
Returns a random UUID v7 similar to this: 019482e4-1441-7aad-8127-eec99573b0a0.
uuidv7()uuidv7()
UUID
uuidv7()
Returns the population variance.
var_pop(x = DOUBLE)var_pop(x = DOUBLE)
x |
|
DOUBLE
Returns the sample variance of all input values.
var_samp(x = DOUBLE)var_samp(x = DOUBLE)
x |
|
DOUBLE
(SUM(x^2) - SUM(x)^2 / COUNT(x)) / (COUNT(x) - 1)
DuckDB function variant_bytes_to_variant().
variant_bytes_to_variant(col0 = BLOB)variant_bytes_to_variant(col0 = BLOB)
col0 |
|
VARIANT
Returns the field from the input_variant if it's an OBJECT.
Returns the entry at index from the input_variant if it's an ARRAY.
col0 |
|
col1 |
|
VARIANT
variant_extract(col0 = VARIANT, col1 = VARCHAR)
variant_extract(col0 = VARIANT, col1 = UINTEGER)
variant_extract({'a': 42, 'b': [1,2,3])::VARIANT, 'b')
variant_extract([1,2,3])::VARIANT, 0)
Other variant:
variant_normalize(),
variant_typeof()
Normalizes the input_variant to a canonical representation.
variant_normalize(input_variant = VARIANT)variant_normalize(input_variant = VARIANT)
input_variant |
|
VARIANT
variant_normalize({'b': [1,2,3], 'a': 42})::VARIANT)
Other variant:
variant_extract(),
variant_typeof()
DuckDB function variant_to_parquet_variant().
variant_to_parquet_variant(col0 = VARIANT)variant_to_parquet_variant(col0 = VARIANT)
col0 |
|
ANY
Returns the internal type of the input_variant.
variant_typeof(input_variant = VARIANT)variant_typeof(input_variant = VARIANT)
input_variant |
|
VARCHAR
variant_typeof({'a': 42, 'b': [1,2,3]})::VARIANT)
Other variant:
variant_extract(),
variant_normalize()
Returns the VectorType of a given column.
vector_type(col = ANY)vector_type(col = ANY)
col |
|
VARCHAR
vector_type(col)
DuckDB function verify_external().
verify_external()verify_external()
Unspecified.
DuckDB function verify_fetch_row().
verify_fetch_row()verify_fetch_row()
Unspecified.
DuckDB function verify_parallelism().
verify_parallelism()verify_parallelism()
Unspecified.
DuckDB function verify_serializer().
verify_serializer()verify_serializer()
Unspecified.
Returns the currently active version of DuckDB in this format: v0.3.2 .
version()version()
VARCHAR
version()
DuckDB macro wavg().
wavg(value, weight)wavg(value, weight)
value |
Unspecified. |
weight |
Unspecified. |
Unspecified.
Extract the week component from a date or timestamp.
ts |
|
BIGINT
week(ts = DATE)
week(ts = INTERVAL)
week(ts = TIMESTAMP)
week(ts = `TIMESTAMP WITH TIME ZONE`)
week(timestamp '2021-08-03 11:59:44.123456')
Extract the weekday component from a date or timestamp.
ts |
|
BIGINT
weekday(ts = DATE)
weekday(ts = INTERVAL)
weekday(ts = TIMESTAMP)
weekday(ts = `TIMESTAMP WITH TIME ZONE`)
weekday(timestamp '2021-08-03 11:59:44.123456')
Extract the weekofyear component from a date or timestamp.
ts |
|
BIGINT
weekofyear(ts = DATE)
weekofyear(ts = INTERVAL)
weekofyear(ts = TIMESTAMP)
weekofyear(ts = `TIMESTAMP WITH TIME ZONE`)
weekofyear(timestamp '2021-08-03 11:59:44.123456')
DuckDB macro weighted_avg().
weighted_avg(value, weight)weighted_avg(value, weight)
value |
Unspecified. |
weight |
Unspecified. |
Unspecified.
DuckDB function which_secret().
which_secret(col0 = VARCHAR, col1 = VARCHAR)which_secret(col0 = VARCHAR, col1 = VARCHAR)
col0 |
|
col1 |
|
Unspecified.
Writes to the logger.
write_log(string = VARCHAR)write_log(string = VARCHAR)
string |
|
ANY
write_log('Hello')
Bitwise XOR.
left |
|
right |
|
TINYINT | SMALLINT | INTEGER | BIGINT | HUGEINT | UTINYINT | USMALLINT | UINTEGER | UBIGINT | UHUGEINT | BIT
xor(left = TINYINT, right = TINYINT)
xor(left = SMALLINT, right = SMALLINT)
xor(left = INTEGER, right = INTEGER)
xor(left = BIGINT, right = BIGINT)
xor(left = HUGEINT, right = HUGEINT)
xor(left = UTINYINT, right = UTINYINT)
xor(left = USMALLINT, right = USMALLINT)
xor(left = UINTEGER, right = UINTEGER)
xor(left = UBIGINT, right = UBIGINT)
xor(left = UHUGEINT, right = UHUGEINT)
xor(left = BIT, right = BIT)
xor(17, 5)
Extract the year component from a date or timestamp.
ts |
|
BIGINT
year(ts = DATE)
year(ts = INTERVAL)
year(ts = TIMESTAMP)
year(ts = `TIMESTAMP WITH TIME ZONE`)
year(timestamp '2021-08-03 11:59:44.123456')
Extract the yearweek component from a date or timestamp.
ts |
|
BIGINT
yearweek(ts = DATE)
yearweek(ts = INTERVAL)
yearweek(ts = TIMESTAMP)
yearweek(ts = `TIMESTAMP WITH TIME ZONE`)
yearweek(timestamp '2021-08-03 11:59:44.123456')