SQL Functions
Propel​
Aggregate​
| Function | Supported |
|---|---|
AVG(numeric) → numeric | ✅ |
AVG_IF(numeric, boolean) → numeric | ✅ |
COUNT(*) → numeric COUNT(any) → numeric COUNT() → numeric | ✅ |
COUNT_IF(boolean) → numeric | ✅ |
COUNT_DISTINCT(any) → integer | ✅ |
FIRST(any) → any FIRST(any, any) → any | ✅ |
LAST(any) → any LAST(any, any) → any | ✅ |
SUM(numeric) → numeric | ✅ |
SUM_IF(numeric, boolean) → numeric | ✅ |
MIN(any) → any | ✅ |
MAX(any) → any | ✅ |
ANY(any) → any | ✅ |
PERCENTILE(numeric, numeric) → numeric | ✅ |
TOP_K(N integer, column any) → anyarray | ✅ |
General​
| Function | Supported |
|---|---|
IS_NULL(any) → boolean | ✅ |
TO_START_OF_MINUTE(date or timestamp) → timestamp | ✅ |
TO_START_OF_FIVE_MINUTES(date or timestamp) → timestamp | ✅ |
TO_START_OF_TEN_MINUTES(date or timestamp) → timestamp | ✅ |
TO_START_OF_FIFTEEN_MINUTES(date or timestamp) → timestamp | ✅ |
TO_START_OF_HOUR(date or timestamp) → timestamp | ✅ |
TO_START_OF_DAY(date or timestamp) → timestamp | ✅ |
TO_START_OF_WEEK(date or timestamp) → timestamp | ✅ |
TO_START_OF_MONTH(date or timestamp) → timestamp | ✅ |
TO_START_OF_YEAR(date or timestamp) → timestamp | ✅ |
TO_INTERVAL_SECOND(integer) → interval | ✅ |
TO_INTERVAL_MINUTE(integer) → interval | ✅ |
TO_INTERVAL_HOUR(integer) → interval | ✅ |
TO_INTERVAL_DAY(integer) → interval | ✅ |
TO_INTERVAL_WEEK(integer) → interval | ✅ |
TO_INTERVAL_MONTH(integer) → interval | ✅ |
TO_INTERVAL_QUARTER(integer) → interval | ✅ |
TO_INTERVAL_YEAR(integer) → interval | ✅ |
TO_SECOND(date or timestamp) → integer | ✅ |
TO_MINUTE(date or timestamp) → integer | ✅ |
TO_HOUR(date or timestamp) → integer | ✅ |
TO_DAY_OF_WEEK(date or timestamp) → integer | ✅ |
TO_DAY_OF_MONTH(date or timestamp) → integer | ✅ |
TO_DAY_OF_YEAR(date or timestamp) → integer | ✅ |
TO_ISO_WEEK(date or timestamp) → integer | ✅ |
TO_MONTH(date or timestamp) → integer | ✅ |
TO_QUARTER(date or timestamp) → integer | ✅ |
TO_YEAR(date or timestamp) → integer | ✅ |
ADD(numeric, numeric [, ...]) → numeric | ✅ |
SUBTRACT(numeric, numeric [, ...]) → numeric | ✅ |
MULTIPLY(numeric, numeric [, ...]) → numeric | ✅ |
DIVIDE(numeric, numeric [, ...]) → numeric | ✅ |
TIMESTAMP_DIFF(unit text, startdate any, enddate any [, timezone text]) → integer | ✅ |
DATE_DIFF(unit text, startdate any, enddate any [, timezone text]) → integer | ✅ |
DATE_TRUNC(unit text, value any [, timezone text]) → timestamp | ✅ |
TO_UNIX_TIMESTAMP(date or timestamp [, timezone text]) → integer | ✅ |
CAST(any, text) → any | ✅ |
CONCAT(s1 text, s2 text [, ...]) → text | ✅ |
CONCAT_WITH_SEPARATOR(separator text, s1 text, s2 text [, ...]) → text | ✅ |
TO_TIMESTAMP(str text, format text [, timezone text]) → timestamp | ✅ |
NOW() → timestamp | ✅ |
CURRENT_TIMESTAMP() → timestamp | ✅ |
FLOOR(numeric) → numeric | ✅ |
CEIL(numeric) → numeric | ✅ |
SUBSTRING(s text, offset integer [, length integer]) → text | ✅ |
PostgreSQL​
Mathematical Functions​
| Function | Supported |
|---|---|
abs(numeric_type) → numeric_type | ✅ |
cbrt(double precision) → double precision | ✅ |
ceil(numeric) → numeric ceil(double precision) → double precision | ✅ |
ceiling(numeric) → numeric ceiling(double precision) → double precision | ✅ |
degrees(double precision) → double precision | ✅ |
div(y numeric, x numeric) → numeric | ✅ |
erf(double precision) → double precision | ✅ |
erfc(double precision) → double precision | ✅ |
exp(numeric) → numeric exp(double precision) → double precision | ✅ |
factorial(big int) → numeric | ✅ |
floor(numeric) → numeric floor(double precision) → double precision | ✅ |
gcd(numeric_type, numeric_type) → numeric_type | ✅ |
lcm(numeric_type, numeric_type) → numeric_type | ✅ |
ln(numeric) → numeric ln(double precision) → double precision | ✅ |
log(numeric) → numeric log(double precision) → double precision log(b numeric, x numeric) → numeric | ✅ |
log10(numeric) → numeric log10(double precision) → double precision | ✅ |
mod(y numeric_type, x numeric_type) → numeric_type | ✅ |
pi() → double precision | ✅ |
power(a numeric, b numeric) → numeric power(a double precision, b double precision) → double precision | ✅ |
radians(double precision) → double precision | ✅ |
round(numeric) → numeric round(double precision) → double precision round(v numeric, s integer) → numeric | ✅ |
sign(numeric) → numeric sign(double precision) → double precision | ✅ |
sqrt(numeric) → numeric sqrt(double precision) → double precision | ✅ |
trunc(numeric) → numeric trunc(double precision) → double precision trunc(v numeric, s integer) → numeric | ✅ |
width_bucket(operand numeric, low numeric, high numeric, count integer) → integer width_bucket(operand double precision, low double precision, high double precision, count integer) → integer width_bucket(operand anycompatible, thresholds anycompatiblearray) → integer | ✅ |
random() → double precision | ✅ |
random_normal(mean double precision [, stddev double precision]) → double precision | ✅ |
acos(double precision) → double precision | ✅ |
asin(double precision) → double precision | ✅ |
atan(double precision) → double precision | ✅ |
atan2(y double precision, x double precision) → double precision | ✅ |
cos(double precision) → double precision | ✅ |
sin(double precision) → double precision | ✅ |
tan(double precision) → double precision | ✅ |
sinh(double precision) → double precision | ✅ |
cosh(double precision) → double precision | ✅ |
asinh(double precision) → double precision | ✅ |
acosh(double precision) → double precision | ✅ |
atanh(double precision) → double precision | ✅ |
String Functions​
| Function | Supported |
|---|---|
btrim(string text [, characters text]) → text | ✅ |
bit_length(text) → integer | ✅ |
char_length(text) → integer | ✅ |
character_length(text) → integer | ✅ |
lower(text) → text | ✅ |
lpad(string text, length integer [, fill text]) → text | ✅ |
ltrim(string text [, characters text]) → text | ✅ |
normalize(text [, form]) → text | ✅ |
octet_length(text) → integer octet_length(character) → integer | ✅ |
rpad(string text, length integer [, fill text]) → text | ✅ |
rtrim(string text [, characters text]) → text | ✅ |
upper(text) → text | ✅ |
ascii(text) → integer | ✅ |
chr(integer) → text | ✅ |
concat(val1 "any" [, val2 "any" [, ...]]) → text | ✅ |
concat_ws(sep text, val1 "any" [, val2 "any" [, ...]]) → text | ✅ |
format(formatstr text [, formatarg "any" [, ...]]) → text | ✅ |
initcap(text) → text | ✅ |
length(text) → integer | ✅ |
md5(text) → text | ✅ |
repeat(string text, number integer) → text | ✅ |
replace(string text, from text, to text) → text | ✅ |
reverse(text) → text | ✅ |
substr(string text, start integer [, count integer]) → text | ✅ |
to_hex(integer) → text to_hex(big int) → text | ✅ |
translate(string text, from text, to text) → text | ✅ |
Binary String Functions​
| Function | Supported |
|---|---|
bit_length(bytea) → integer | ✅ |
btrim(bytes bytea, bytesremoved bytea) → bytea | ✅ |
ltrim(bytes bytea, bytesremoved bytea) → bytea | ✅ |
octet_length(bytea) → integer | ✅ |
rtrim(bytes bytea, bytesremoved bytea) → bytea | ✅ |
bit_count(bytes bytea) → bigint | ✅ |
get_byte(bytes bytea, n integer) → integer | ✅ |
length(bytea) → integer length(bytes bytea, encoding name) → integer | ✅ |
md5(bytea) → text | ✅ |
sha224(bytea) → bytea | ✅ |
sha256(bytea) → bytea | ✅ |
sha512(bytea) → bytea | ✅ |
substr(bytes bytea, start integer [, count integer]) → bytea | ✅ |
convert(bytes bytea, src_encoding name, dest_encoding name) → bytea | ✅ |
convert_from(bytes bytea, src_encoding name) → text | ✅ |
convert_to(string text, dest_encoding name) → bytea | ✅ |
encode(bytes bytea, format text) → text | ✅ |
decode(string text, format text) → bytea | ✅ |
Bit String Functions​
| Function | Supported |
|---|---|
bit_count(bit) → bigint | ✅ |
bit_length(bit) → integer | ✅ |
length(bit) → integer | ✅ |
octet_length(bit) → integer | ✅ |
Date/Time Functions​
| Function | Supported |
|---|---|
age(timestamp, timestamp) → interval age(timestamp) → interval | ✅ |
clock_timestamp() → timestamp with time zone | ✅ |
current_timestamp(integer) → timestamp with time zone | ✅ |
date_add(timestamp with time zone, interval [, text]) → timestamp with time zone | ✅ |
date_trunc(text, timestamp) → timestamp date_trunc(text, timestamp with time zone, text) → timestamp with time zone date_trunc(text, interval) → interval | ✅ |
isfinite(date) → boolean isfinite(timestamp) → boolean isfinite(interval) → boolean | ✅ |
localtime(integer) → time | ✅ |
localtimestamp(integer) → timestamp | ✅ |
make_date(year int, month int, day int) → date | ✅ |
now() → timestamp with time zone | ✅ |
statement_timestamp() → timestamp with time zone | ✅ |
transaction_timestamp() → timestamp with time zone | ✅ |
Geometric Functions​
| Function | Supported |
|---|---|
length(geometric_type) → double precision | ✅ |
Network Address Functions​
| Function | Supported |
|---|---|
trunc(macaddr) → macaddr trunc(macaddr8) → macaddr8 | ✅ |
Text Search Functions​
| Function | Supported |
|---|---|
length(tsvector) → integer | ✅ |
UUID Functions​
| Function | Supported |
|---|---|
gen_random_uuid() → uuid | ✅ |
XML Functions​
| Function | Supported |
|---|---|
xmlconcat(xml [, ...]) → xml | ✅ |
Conditional Expressions​
| Function | Supported |
|---|---|
COALESCE(any [, ...]) → any | ✅ |
NULLIF(value1 any, value2 any) → any | ✅ |
GREATEST(value any [, ...]) → any | ✅ |
LEAST(value any [, ...]) → any | ✅ |
Range/Multirange Functions​
| Function | Supported |
|---|---|
lower(anyrange) → anyelement lower(anymultirange) → anyelement | ✅ |
upper(anyrange) → anyelement upper(anymultirange) → anyelement | ✅ |
Aggregate Functions​
| Function | Supported |
|---|---|
any_value(anyelement) → same as input type | ✅ |
avg(small int) → numeric avg(integer) → numeric avg(big int) → numeric avg(numeric) → numeric avg(real) → double precision avg(double precision) → double precision avg(interval) → interval | ✅ |
bit_and(small int) → smallint bit_and(integer) → integer bit_and(big int) → bigint bit_and(bit) → bit | ✅ |
bit_or(small int) → smallint bit_or(integer) → integer bit_or(big int) → bigint bit_or(bit) → bit | ✅ |
bit_xor(small int) → smallint bit_xor(integer) → integer bit_xor(big int) → bigint bit_xor(bit) → bit | ✅ |
bool_and(boolean) → boolean | ✅ |
bool_or(boolean) → boolean | ✅ |
count(*) → bigint count("any") → bigint | ✅ |
every(boolean) → boolean | ✅ |
max(see text) → same as input type | ✅ |
min(see text) → same as input type | ✅ |
sum(small int) → bigint sum(integer) → bigint sum(big int) → numeric sum(numeric) → numeric sum(real) → real sum(double precision) → double precision sum(interval) → interval sum(money) → money | ✅ |
corr(Y double precision, X double precision) → double precision | ✅ |
covar_pop(Y double precision, X double precision) → double precision | ✅ |
covar_samp(Y double precision, X double precision) → double precision | ✅ |
Window Functions​
| Function | Supported |
|---|---|
first_value(value anyelement) → anyelement | ✅ |
last_value(value anyelement) → anyelement | ✅ |
System Information Functions​
| Function | Supported |
|---|---|
current_database() → name | ✅ |
version() → text | ✅ |
age(xid) → integer | ✅ |
ClickHouse​
Arithmetic Functions​
| Function | Supported |
|---|---|
plus(a numeric, b numeric) → numeric plus(a integer, b date) → date plus(a date, b integer) → date plus(a integer, b timestamp) → timestamp plus(a timestamp, b integer) → timestamp | ✅ |
minus(a numeric, b numeric) → numeric minus(a integer, b date) → date minus(a date, b integer) → date minus(a integer, b timestamp) → timestamp minus(a timestamp, b integer) → timestamp | ✅ |
multiply(a numeric, b numeric) → numeric | ✅ |
divide(a numeric, b numeric) → float64 | ✅ |
intDiv(a numeric, b numeric) → integer | ✅ |
intDivOrZero(a numeric, b numeric) → integer | ✅ |
modulo(a integer, b integer) → integer modulo(a numeric, b numeric) → float64 | ✅ |
moduloOrZero(a integer, b integer) → integer moduloOrZero(a numeric, b numeric) → float64 | ✅ |
positiveModulo(a integer, b integer) → integer positiveModulo(a numeric, b numeric) → float64 | ✅ |
negate(a numeric) → numeric | ✅ |
abs(a numeric) → numeric | ✅ |
gcd(a numeric, b numeric) → numeric | ✅ |
lcm(a numeric, b numeric) → numeric | ✅ |
max2(a numeric, b numeric) → float64 | ✅ |
min2(a numeric, b numeric) → float64 | ✅ |
multiplyDecimal(a decimal, b decimal [, result_scale integer]) → decimal256 | ✅ |
divideDecimal(a decimal, b decimal [, result_scale integer]) → decimal256 | ✅ |
byteSwap(a integer) → integer | ✅ |
Array Functions​
| Function | Supported |
|---|---|
empty(anyarray) → boolean | ✅ |
notEmpty(anyarray) → boolean | ✅ |
length(anyarray) → uint64 | ✅ |
reverse(arr anyarray) → anyarray | ✅ |
Bit Functions​
| Function | Supported |
|---|---|
bitAnd(numeric, numeric) → integer | ✅ |
bitOr(numeric, numeric) → integer | ✅ |
bitXor(numeric, numeric) → integer | ✅ |
bitNot(numeric) → integer | ✅ |
bitShiftLeft(numeric, integer) → integer bitShiftLeft(text, integer) → text | ✅ |
bitShiftRight(numeric, integer) → integer bitShiftRight(text, integer) → text | ✅ |
bitRotateLeft(numeric, integer) → integer | ✅ |
bitRotateRight(numeric, integer) → integer | ✅ |
bitSlice(s text, offset integer [, length integer]) → text | ✅ |
byteSlice(s text, offset integer [, length integer]) → text | ✅ |
bitTest(numeric, integer) → boolean | ✅ |
bitTestAll(numeric, integer [, ...]) → boolean | ✅ |
bitTestAny(numeric, integer [, ...]) → boolean | ✅ |
bitCount(numeric) → uint8 | ✅ |
bitHammingDistance(int64, int64) → uint8 | ✅ |
Comparison Functions​
| Function | Supported |
|---|---|
equals(any, any) → boolean | ✅ |
notEquals(any, any) → boolean | ✅ |
less(any, any) → boolean | ✅ |
greater(any, any) → boolean | ✅ |
lessOrEquals(any, any) → boolean | ✅ |
greaterOrEquals(any, any) → boolean | ✅ |
Conditional Functions​
| Function | Supported |
|---|---|
if(cond boolean, then any, else any) → any | ✅ |
multiIf(cond_1 boolean, then_1 any, cond_2 boolean, then_2 any [, ...], else any) → any | ✅ |
greatest(any [, ...]) → any | ✅ |
least(any [, ...]) → any | ✅ |
Date and Time Functions​
| Function | Supported |
|---|---|
makeDate(year numeric, month numeric, day numeric) → date makeDate(year numeric, day_of_year numeric) → date | ✅ |
makeDate32(year numeric, month numeric, day numeric) → date32 makeDate32(year numeric, day_of_year numeric) → date32 | ✅ |
makeDateTime(year numeric, month numeric, day numeric, hour numeric, minute numeric, second numeric [, timezone text]) → date32 | ✅ |
makeDateTime64(year numeric, month numeric, day numeric, hour numeric, minute numeric, second numeric [, fraction numeric] [, precision numeric] [, timezone text]) → date32 | ✅ |
timestamp(exp text [, expr_time text]) → datetime64 | ✅ |
timeZone() → text | ✅ |
timezone() → text | ✅ |
serverTimeZone() → text | ✅ |
serverTimezone() → text | ✅ |
toTimeZone(datetime64, timezone text) → datetime | ✅ |
timeZoneOf(date time or datetime64) → text | ✅ |
timeZoneOffset(date time or datetime64) → int32 | ✅ |
toYear(date or date32 or datetime or datetime64) → uint16 | ✅ |
toQuarter(date or date32 or datetime or datetime64) → uint8 | ✅ |
toMonth(date or date32 or datetime or datetime64) → uint8 | ✅ |
toDayOfYear(date or date32 or datetime or datetime64) → uint16 | ✅ |
DAYOFYEAR(date or date32 or datetime or datetime64) → uint16 | ✅ |
toDayOfMonth(date or date32 or datetime or datetime64) → uint8 | ✅ |
toDayOfWeek(date or date32 or datetime or datetime64 [, mode integer] [, timezone text]) → uint8 | ✅ |
toHour(date time or datetime64) → uint8 | ✅ |
HOUR(date time or datetime64) → uint8 | ✅ |
toMinute(date time or datetime64) → uint8 | ✅ |
MINUTE(date time or datetime64) → uint8 | ✅ |
toSecond(date time or datetime64) → uint8 | ✅ |
SECOND(date time or datetime64) → uint8 | ✅ |
toMillisecond(date time or datetime64) → uint8 | ✅ |
MILLISECOND(date time or datetime64) → uint8 | ✅ |
toUnixTimestamp(date or timestamp) → uint32 toUnixTimestamp(str text [, timezone text]) → uint32 | ✅ |
toStartOfYear(date or date32 or datetime or datetime64) → date | ✅ |
toStartOfISOYear(date or date32 or datetime or datetime64) → date | ✅ |
toStartOfQuarter(date or date32 or datetime or datetime64) → date | ✅ |
toStartOfMonth(date or date32 or datetime or datetime64) → date | ✅ |
toLastDayOfMonth(date or date32 or datetime or datetime64) → date | ✅ |
toMonday(date or date32 or datetime or datetime64) → date | ✅ |
toStartOfWeek(date or date32 or datetime or datetime64 [, mode integer] [, timezone text]) → date | ✅ |
toLastDayOfWeek(date or date32 or datetime or datetime64 [, mode integer] [, timezone text]) → date | ✅ |
toStartOfDay(date or date32 or datetime or datetime64) → datetime | ✅ |
toStartOfHour(date time or datetime64) → datetime | ✅ |
toStartOfMinute(date time or datetime64) → datetime | ✅ |
toStartOfSecond(datetime64 [, timezone text]) → datetime64 | ✅ |
toStartOfFiveMinutes(date time or datetime64) → datetime | ✅ |
toStartOfTenMinutes(date time or datetime64) → datetime | ✅ |
toStartOfFifteenMinutes(date time or datetime64) → datetime | ✅ |
toTime(date time or datetime64) → same as input type | ✅ |
toRelativeYearNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeQuarterNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeMonthNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeWeekNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeDayNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeHourNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeMinuteNum(date or date32 or datetime or datetime64) → integer | ✅ |
toRelativeSecondNum(date or date32 or datetime or datetime64) → integer | ✅ |
toISOYear(date or date32 or datetime or datetime64) → uint16 | ✅ |
toISOWeek(date or date32 or datetime or datetime64) → uint8 | ✅ |
toWeek(text or date or date32 or datetime or datetime64 [, mode integer]) → uint8 | ✅ |
WEEK(text or date or date32 or datetime or datetime64 [, mode integer]) → uint8 | ✅ |
toYearWeek(text or date or date32 or datetime or datetime64 [, mode integer]) → uint8 | ✅ |
YEARWEEK(text or date or date32 or datetime or datetime64 [, mode integer]) → uint8 | ✅ |
toDaysSinceYearZero(date or date32 or datetime or datetime64 [, timezone text]) → uint32 | ✅ |
TO_DAYS(date or date32 or datetime or datetime64 [, timezone text]) → uint32 | ✅ |
fromDaysSinceYearZero(days integer) → date | ✅ |
FROM_DAYS(days integer) → date | ✅ |
fromDaysSinceYearZero32(days integer) → date32 | ✅ |
age(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
date_diff(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
dateDiff(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
DATE_DIFF(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
timestampDiff(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
timestamp_diff(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
TIMESTAMP_DIFF(unit text, startdate date or date32 or datetime or datetime64, enddate date or date32 or datetime or datetime64 [, timezone text]) → integer | ✅ |
date_trunc(unit text, value date or date32 or datetime or datetime64 [, timezone text]) → timestamp | ✅ |
dateTrunc(unit text, value date or date32 or datetime or datetime64 [, timezone text]) → timestamp | ✅ |
date_add(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
dateAdd(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
DATE_ADD(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
date_sub(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
dateSub(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
DATE_SUB(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
timestamp_sub(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
timestampSub(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
TIMESTAMP_SUB(unit text, value integer, date date or date32 or datetime or datetime64) → date or date32 or datetime or datetime64 | ✅ |
addDate(date date or date32 or datetime or datetime64, interval interval) → date or date32 or datetime or datetime64 | ✅ |
subDate(date date or date32 or datetime or datetime64, interval interval) → date or date32 or datetime or datetime64 | ✅ |
now(timezone text) → datetime | ✅ |
current_timestamp(timezone text) → timestamp | ✅ |
now64(timezone text) → datetime64 | ✅ |
nowInBlock(timezone text) → datetime | ✅ |
today() → datetime | ✅ |
yesterday() → datetime | ✅ |
timeSlot(date time or datetime64) → same as input type | ✅ |
toYYYYMM(date or date32 or datetime or datetime64 [, timezone text]) → text | ✅ |
toYYYYMMDD(date or date32 or datetime or datetime64 [, timezone text]) → text | ✅ |
toYYYYMMDDhhmmss(date or date32 or datetime or datetime64 [, timezone text]) → text | ✅ |
YYYYMMDDToDate(yyyymmdd text) → date | ✅ |
YYYYMMDDToDate32(yyyymmdd text) → date32 | ✅ |
YYYYMMDDhhmmssToDateTime(yyyymmddhhmmss text [, timezone text]) → datetime | ✅ |
YYYYMMDDhhmmssToDateTime64(yyyymmddhhmmss text [, timezone text]) → datetime64 | ✅ |
addYears(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
addQuarters(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
addMonths(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
addWeeks(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
addDays(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
addHours(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
addMinutes(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
addSeconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
addMilliseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
addMicroseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
addNanoseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractYears(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
subtractQuarters(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
subtractMonths(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
subtractWeeks(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
subtractDays(text or date or date32 or datetime or datetime64, integer) → date or date32 or datetime or datetime64 | ✅ |
subtractHours(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractMinutes(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractSeconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractMilliseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractMicroseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
subtractNanoseconds(text or date or date32 or datetime or datetime64, integer) → datetime or datetime64 | ✅ |
timeSlots(starttime datetime or datetime64, duration uint32 [, size uint32]) → anyarray | ✅ |
formatDateTime(date or date32 or datetime or datetime64, format text [, timezone text]) → text | ✅ |
DATE_FORMAT(date or date32 or datetime or datetime64, format text [, timezone text]) → text | ✅ |
formatDateTimeInJodaSyntax(date or date32 or datetime or datetime64, format text [, timezone text]) → text | ✅ |
dateName(date_part text, date date or date32 or datetime or datetime64 [, timezone text]) → text | ✅ |
monthName(date or date32 or datetime or datetime64) → text | ✅ |
fromUnixTimestamp(integer) → datetime fromUnixTimestamp(integer or date or date32 or datetime or datetime64, format text [, timezone text]) → text | ✅ |
FROM_UNIXTIME(integer) → datetime | ✅ |
fromUnixTimestampInJodaSyntax(integer or date or date32 or datetime or datetime64, format text [, timezone text]) → text | ✅ |
toModifiedJulianDay(text) → int32 | ✅ |
toModifiedJulianDayOrNull(text) → int32 | ✅ |
fromModifiedJulianDay(integer) → text | ✅ |
fromModifiedJulianDayOrNull(integer) → text | ✅ |
toUTCTimestamp(time_val datetime or datetime64, timezone text) → text | ✅ |
fromUTCTimestamp(time_val datetime or datetime64, timezone text) → text | ✅ |
timeDiff(first_datetime datetime or datetime64, second_datetime datetime or datetime64) → numeric | ✅ |
Encoding Functions​
| Function | Supported |
|---|---|
char(numeric [, ...]) → text | ✅ |
hex(any) → text | ✅ |
unhex(arg text) → text | ✅ |
bin(any) → text | ✅ |
unbin(text) → text | ✅ |
bitmaskToList(integer) → text | ✅ |
mortonEncode(integer [, ...]) → uint64 | ✅ |
Hash Functions​
| Function | Supported |
|---|---|
halfMD5(any [, ...]) → uint64 | ✅ |
MD4(text) → text | ✅ |
MD5(text) → text | ✅ |
sipHash64(any [, ...]) → uint64 | ✅ |
sipHash128(any [, ...]) → text | ✅ |
sipHash128Reference(any [, ...]) → text | ✅ |
cityHash(any [, ...]) → uint64 | ✅ |
intHash32(integer) → uint32 | ✅ |
intHash64(integer) → uint64 | ✅ |
SHA1(text) → text | ✅ |
SHA224(text) → text | ✅ |
SHA256(text) → text | ✅ |
SHA512(text) → text | ✅ |
SHA512_256(text) → text | ✅ |
BLAKE3(text) → text | ✅ |
URLHash(url text [, N integer]) → text | ✅ |
farmFingerprint64(any [, ...]) → uint64 | ✅ |
farmHash64(any [, ...]) → uint64 | ✅ |
javaHash(any) → int32 | ✅ |
javaHashUTF16LE(text) → int32 | ✅ |
hiveHash(text) → int32 | ✅ |
metroHash64(any [, ...]) → uint64 | ✅ |
jumpConsistentHash(u int64, buckets integer) → int32 | ✅ |
murmurHash2_32(any [, ...]) → uint32 | ✅ |
murmurHash2_64(any [, ...]) → uint64 | ✅ |
gccMurmurHash(any [, ...]) → uint64 | ✅ |
kafkaMurmurHash(any [, ...]) → uint32 | ✅ |
murmurHash3_32(any [, ...]) → uint32 | ✅ |
murmurHash3_64(any [, ...]) → uint64 | ✅ |
murmurHash3_128(any [, ...]) → text | ✅ |
xxh3(any [, ...]) → uint64 | ✅ |
xxHash32(text) → uint32 | ✅ |
xxHash64(text) → uint64 | ✅ |
ngramSimHash(text [, ngramsize uint8]) → uint64 | ✅ |
ngramSimHashCaseInsensitive(text [, ngramsize uint8]) → uint64 | ✅ |
ngramSimHashUTF8(text [, ngramsize uint8]) → uint64 | ✅ |
ngramSimHashCaseInsensitiveUTF8(text [, ngramsize uint8]) → uint64 | ✅ |
wordShingleSimHash(text [, shinglesize uint8]) → uint64 | ✅ |
wordShingleSimHashCaseInsensitive(text [, shinglesize uint8]) → uint64 | ✅ |
wordShingleSimHashUTF8(text [, shinglesize uint8]) → uint64 | ✅ |
wordShingleSimHashCaseInsensitiveUTF8(text [, shinglesize uint8]) → uint64 | ✅ |
sqidEncode(integer [, ...]) → text | ✅ |
sqidDecode(text) → anyarray | ✅ |
IP Address Functions​
| Function | Supported |
|---|---|
IPv4NumToString(uint32) → text | ✅ |
IPv4StringToNum(text) → uint32 | ✅ |
INET_ATON(text) → uint32 | ✅ |
IPv4StringToNumOrDefault(text) → uint32 | ✅ |
IPv4StringToNumOrNull(text) → uint32 | ✅ |
IPv4NumToStringClassC(uint32) → text | ✅ |
IPv6NumToString(text) → text | ✅ |
INET6_NTOA(text) → text | ✅ |
IPv6StringToNum(text) → text | ✅ |
INET6_ATON(text) → text | ✅ |
IPv6StringToNumOrDefault(text) → text | ✅ |
IPv6StringToNumOrNull(text) → text | ✅ |
IPv4ToIPv6(uint32) → text | ✅ |
cutIPv6(text, bytesToCutForIPv6 integer, bytesToCutForIPv4 integer) → text | ✅ |
isIPv4String(text) → boolean | ✅ |
isIPv6String(text) → boolean | ✅ |
isIPAddressInRange(address text, prefix text) → boolean | ✅ |
JSON Functions​
| Function | Supported |
|---|---|
JSONExtractRaw(json text [, indices_or_keys text or integer [, ...]]) → text | ✅ |
JSONExtract(json text [, indices_or_keys text or integer [, ...]], return_type text) → any | ✅ |
JSONType(json text [, indices_or_keys text or integer [, ...]]) → text | ✅ |
Logical Functions​
| Function | Supported |
|---|---|
and(any, any [, ...]) → boolean | ✅ |
or(any, any [, ...]) → boolean | ✅ |
not(any) → boolean | ✅ |
xor(any, any [, ...]) → boolean | ✅ |
Mathematical Functions​
| Function | Supported |
|---|---|
e() → float64 | ✅ |
pi() → float64 | ✅ |
exp(numeric) → float32 or float64 | ✅ |
log(numeric) → float32 or float64 | ✅ |
ln(numeric) → float32 or float64 | ✅ |
exp2(numeric) → float32 or float64 | ✅ |
intExp2(numeric) → uint64 | ✅ |
log2(numeric) → float32 or float64 | ✅ |
exp10(numeric) → float32 or float64 | ✅ |
intExp10(numeric) → uint64 | ✅ |
log10(numeric) → float32 or float64 | ✅ |
sqrt(numeric) → float32 or float64 | ✅ |
cbrt(numeric) → float32 or float64 | ✅ |
erf(numeric) → float32 or float64 | ✅ |
erfc(numeric) → float32 or float64 | ✅ |
lgamma(numeric) → float32 or float64 | ✅ |
tgamma(numeric) → float32 or float64 | ✅ |
sin(numeric) → float32 or float64 | ✅ |
cos(numeric) → float32 or float64 | ✅ |
tan(numeric) → float32 or float64 | ✅ |
asin(numeric) → float32 or float64 | ✅ |
acos(numeric) → float32 or float64 | ✅ |
atan(numeric) → float32 or float64 | ✅ |
pow(numeric, numeric) → float64 | ✅ |
cosh(float64) → float64 | ✅ |
acosh(float64) → float64 | ✅ |
sinh(float64) → float64 | ✅ |
asinh(float64) → float64 | ✅ |
atanh(float64) → float64 | ✅ |
atan2(float64, float64) → float64 | ✅ |
hypot(float64, float64) → float64 | ✅ |
log1p(float64) → float64 | ✅ |
sign(numeric) → int8 | ✅ |
degrees(float64) → float64 | ✅ |
radians(float64) → float64 | ✅ |
factorial(integer) → uint64 | ✅ |
width_bucket(operand numeric, low numeric, high numeric, count uint8 or uint16 or uint32 or uint64) → numeric | ✅ |
Nullable Value Functions​
| Function | Supported |
|---|---|
isNull(any) → boolean | ✅ |
isNotNull(any) → boolean | ✅ |
isZeroOrNull(any) → boolean | ✅ |
coalesce(any [, ...]) → any | ✅ |
ifNull(x any, alt any) → any | ✅ |
nullIf(x any, y any) → any | ✅ |
assumeNotNull(x any) → any | ✅ |
toNullable(x any) → any | ✅ |
Other Functions​
| Function | Supported |
|---|---|
basename(text) → text | ✅ |
visibleWidth(text) → text | ✅ |
toTypeName(text) → text | ✅ |
blockSize() → integer | ✅ |
byteSize(any [, ...]) → uint64 | ✅ |
currentDatabase() → text | ✅ |
isConstant(any) → boolean | ✅ |
isFinite(float32 or float64) → boolean | ✅ |
isInfinite(float32 or float64) → boolean | ✅ |
ifNotFinite(x float32 or float64, y float32 or float64) → float32 or float64 | ✅ |
isNaN(float32 or float64) → boolean | ✅ |
bar(x integer, min int64, max int64, width numeric) → text | ✅ |
formatReadableDecimalSize(bytes integer) → text | ✅ |
formatReadableSize(bytes integer) → text | ✅ |
formatReadableQuantity(quantity integer) → text | ✅ |
formatReadableTimeDelta(column any [, maximum_unit text] [, minimum_unit text]) → text | ✅ |
parseTimeDelta(text) → float64 | ✅ |
least(any, any) → any | ✅ |
greatest(any, any) → any | ✅ |
version() → text | ✅ |
blockNumber() → text | ✅ |
rowNumberInBlock() → integer | ✅ |
rowNumberInAllBlocks() → integer | ✅ |
runningDifference(numeric) → numeric | ✅ |
runningDifferenceWithFirstValue(numeric) → numeric | ✅ |
identity(any) → any | ✅ |
countDigits(x integer) → uint8 | ✅ |
formatQuery(query text) → text | ✅ |
formatQueryOrNull(query text) → text | ✅ |
formatQuerySingleLine(query text) → text | ✅ |
formatQuerySingleLineOrNull(query text) → text | ✅ |
Random Value Functions​
| Function | Supported |
|---|---|
rand() → uint32 | ✅ |
rand64() → uint64 | ✅ |
randCanonical() → float64 | ✅ |
randConstant(any) → uint32 | ✅ |
randUniform(min float64, max float64) → float64 | ✅ |
randNormal(mean float64, variance float64) → float64 | ✅ |
randLogNormal(mean float64, variance float64) → float64 | ✅ |
randBinomial(experiments uint64, probability float64) → uint64 | ✅ |
randNegativeBinomial(experiments uint64, probability float64) → uint64 | ✅ |
randPoisson(n uint64) → uint64 | ✅ |
randBernoulli(probability float64) → uint64 | ✅ |
randExponential(lambda float64) → float64 | ✅ |
randChiSquared(degree_of_freedom float64) → float64 | ✅ |
randStudentT(degree_of_freedom float64) → float64 | ✅ |
randFisherF(d1 float64, d2 float64) → float64 | ✅ |
randomString(length integer) → text | ✅ |
randomFixedString(length uint64) → text | ✅ |
randomPrintableASCII(length integer) → text | ✅ |
randomStringUTF8(length uint64) → text | ✅ |
fuzzBits(s text, prob float32 or float64) → text | ✅ |
Rounding Functions​
| Function | Supported |
|---|---|
floor(x numeric [, N integer]) → numeric | ✅ |
ceil(x numeric [, N integer]) → numeric | ✅ |
ceiling(x numeric [, N integer]) → numeric | ✅ |
trunc(x numeric [, N integer]) → numeric | ✅ |
truncate(x numeric [, N integer]) → numeric | ✅ |
round(x numeric [, N integer]) → numeric | ✅ |
roundBankers(x numeric [, N integer]) → numeric | ✅ |
roundToExp2(numeric) → numeric | ✅ |
roundDuration(numeric) → numeric | ✅ |
roundAge(numeric) → numeric | ✅ |
roundDown(numeric, anyarray) → any | ✅ |
String Functions​
| Function | Supported |
|---|---|
empty(text) → boolean empty(anyarray) → boolean empty(uuid) → boolean | ✅ |
notEmpty(text) → boolean notEmpty(anyarray) → boolean notEmpty(uuid) → boolean | ✅ |
length(text) → integer length(anyarray) → integer | ✅ |
OCTET_LENGTH(text) → integer | ✅ |
lengthUTF8(text) → integer | ✅ |
CHAR_LENGTH(text) → integer | ✅ |
CHARACTER_LENGTH(text) → integer | ✅ |
leftPad(text, length integer [, pad_string text]) → text | ✅ |
LPAD(text, length integer [, pad_string text]) → text | ✅ |
leftPadUTF8(text, length integer [, pad_string text]) → text | ✅ |
rightPad(text, length integer [, pad_string text]) → text | ✅ |
RPAD(text, length integer [, pad_string text]) → text | ✅ |
rightPadUTF8(text, length integer [, pad_string text]) → text | ✅ |
lower(text) → text | ✅ |
lcase(text) → text | ✅ |
upper(text) → text | ✅ |
ucase(text) → text | ✅ |
lowerUTF8(text) → text | ✅ |
upperUTF8(text) → text | ✅ |
isValidUTF8(text) → boolean | ✅ |
toValidUTF8(text) → text | ✅ |
repeat(text, integer) → text | ✅ |
space(integer) → text | ✅ |
SPACE(integer) → text | ✅ |
reverse(text) → text | ✅ |
reverseUTF8(text) → text | ✅ |
concat(s1 text, s2 text [, ...]) → text | ✅ |
concatAssumeInjective(s1 text, s2 text [, ...]) → text | ✅ |
concatWithSeparator(sep text, expr1 text, expr2 text [, ...]) → text | ✅ |
concat_ws(sep text, expr1 text, expr2 text [, ...]) → text | ✅ |
concatWithSeparatorAssumeInjective(sep text, expr1 text, expr2 text [, ...]) → text | ✅ |
substring(s text, offset integer [, length integer]) → text | ✅ |
substr(s text, offset integer [, length integer]) → text | ✅ |
mid(s text, offset integer [, length integer]) → text | ✅ |
byteSlice(s text, offset integer [, length integer]) → text | ✅ |
substringUTF8(s text, offset integer [, length integer]) → text | ✅ |
substringIndex(s text, delim text, count integer) → text | ✅ |
SUBSTRING_INDEX(s text, delim text, count integer) → text | ✅ |
substringIndexUTF8(s text, delim text, count integer) → text | ✅ |
appendTrailingCharIfAbsent(s text, c text) → text | ✅ |
convertCharset(s text, from text, to text) → text | ✅ |
base58Encode(text) → text | ✅ |
base58Decode(text) → text | ✅ |
tryBase58Decode(text) → text | ✅ |
base64Encode(text) → text | ✅ |
TO_BASE64(text) → text | ✅ |
base64Decode(text) → text | ✅ |
FROM_BASE64(text) → text | ✅ |
tryBase64Decode(text) → text | ✅ |
endsWith(str text, suffix text) → boolean | ✅ |
endsWithUTF8(str text, suffix text) → boolean | ✅ |
startsWith(str text, prefix text) → boolean | ✅ |
startsWithUTF8(str text, prefix text) → boolean | ✅ |
trimLeft(text) → text | ✅ |
ltrim(text) → text | ✅ |
trimRight(text) → text | ✅ |
rtrim(text) → text | ✅ |
trimBoth(text) → text | ✅ |
trim(text) → text | ✅ |
CRC32(text) → uint32 | ✅ |
CRC32IEEE(text) → uint32 | ✅ |
CRC64(text) → uint64 | ✅ |
normalizeQuery(text) → text | ✅ |
normalizeQueryHash(text) → uint64 | ✅ |
normalizeUTF8NFC(text) → text | ✅ |
normalizeUTF8NFD(text) → text | ✅ |
normalizeUTF8NFKC(text) → text | ✅ |
normalizeUTF8NFKD(text) → text | ✅ |
encodeXMLComponent(text) → text | ✅ |
decodeXMLComponent(text) → text | ✅ |
decodeHTMLComponent(text) → text | ✅ |
extractTextFromHTML(text) → text | ✅ |
ascii(text) → int32 | ✅ |
soundex(text) → text | ✅ |
punycodeEncode(text) → text | ✅ |
punycodDecode(text) → text | ✅ |
tryPunycodDecode(text) → text | ✅ |
idnaEncode(text) → text | ✅ |
tryIdnaEncode(text) → text | ✅ |
idnaDecode(text) → text | ✅ |
byteHammingDistance(string1 text, string2 text) → integer | ✅ |
mismatches(string1 text, string2 text) → integer | ✅ |
stringJaccardIndex(string1 text, string2 text) → float64 | ✅ |
stringJaccardIndexUTF8(string1 text, string2 text) → float64 | ✅ |
editDistance(string1 text, string2 text) → integer | ✅ |
levenshteinDistance(string1 text, string2 text) → integer | ✅ |
damerauLevenshteinDistance(string1 text, string2 text) → integer | ✅ |
jaroSimilarity(string1 text, string2 text) → float64 | ✅ |
jaroWinklerSimilarity(string1 text, string2 text) → float64 | ✅ |
initcap(text) → text | ✅ |
initcapUTF8(text) → text | ✅ |
firstLine(text) → text | ✅ |
String Replacement Functions​
| Function | Supported |
|---|---|
replaceOne(haystack text, pattern text, replacement text) → text | ✅ |
replaceAll(haystack text, pattern text, replacement text) → text | ✅ |
replace(haystack text, pattern text, replacement text) → text | ✅ |
replaceRegexpOne(haystack text, pattern text, replacement text) → text | ✅ |
replaceRegexpAll(haystack text, pattern text, replacement text) → text | ✅ |
REGEXP_REPLACE(haystack text, pattern text, replacement text) → text | ✅ |
regexpQuoteMeta(text) → text | ✅ |
format(pattern text, s text [, ...]) → text | ✅ |
translate(s text, from text, to text) → text | ✅ |
translateUTF8(s text, from text, to text) → text | ✅ |
Type Conversion Functions​
| Function | Supported |
|---|---|
toInt8(numeric or String) → Int8 | ✅ |
toInt16(numeric or String) → Int16 | ✅ |
toInt32(numeric or String) → Int32 | ✅ |
toInt64(numeric or String) → Int64 | ✅ |
toInt128(numeric or String) → Int128 | ✅ |
toInt256(numeric or String) → Int256 | ✅ |
toInt8OrZero(numeric or String) → Int8 | ✅ |
toInt16OrZero(numeric or String) → Int16 | ✅ |
toInt32OrZero(numeric or String) → Int32 | ✅ |
toInt64OrZero(numeric or String) → Int64 | ✅ |
toInt128OrZero(numeric or String) → Int128 | ✅ |
toInt256OrZero(numeric or String) → Int256 | ✅ |
toInt8OrNull(numeric or String) → Int8 | ✅ |
toInt16OrNull(numeric or String) → Int16 | ✅ |
toInt32OrNull(numeric or String) → Int32 | ✅ |
toInt64OrNull(numeric or String) → Int64 | ✅ |
toInt128OrNull(numeric or String) → Int128 | ✅ |
toInt256OrNull(numeric or String) → Int256 | ✅ |
toInt8OrDefault(numeric or String, Int8) → Int8 | ✅ |
toInt16OrDefault(numeric or String, Int16) → Int16 | ✅ |
toInt32OrDefault(numeric or String, Int32) → Int32 | ✅ |
toInt64OrDefault(numeric or String, Int64) → Int64 | ✅ |
toInt128OrDefault(numeric or String, Int128) → Int128 | ✅ |
toInt256OrDefault(numeric or String, Int256) → Int256 | ✅ |
toUInt8(numeric or String) → UInt8 | ✅ |
toUInt16(numeric or String) → UInt16 | ✅ |
toUInt32(numeric or String) → UInt32 | ✅ |
toUInt64(numeric or String) → UInt64 | ✅ |
toUInt128(numeric or String) → UInt128 | ✅ |
toUInt256(numeric or String) → UInt256 | ✅ |
toUInt8OrZero(numeric or String) → UInt8 | ✅ |
toUInt16OrZero(numeric or String) → UInt16 | ✅ |
toUInt32OrZero(numeric or String) → UInt32 | ✅ |
toUInt64OrZero(numeric or String) → UInt64 | ✅ |
toUInt128OrZero(numeric or String) → UInt128 | ✅ |
toUInt256OrZero(numeric or String) → UInt256 | ✅ |
toUInt8OrNull(numeric or String) → UInt8 | ✅ |
toUInt16OrNull(numeric or String) → UInt16 | ✅ |
toUInt32OrNull(numeric or String) → UInt32 | ✅ |
toUInt64OrNull(numeric or String) → UInt64 | ✅ |
toUInt128OrNull(numeric or String) → UInt128 | ✅ |
toUInt256OrNull(numeric or String) → UInt256 | ✅ |
toUInt8OrDefault(numeric or String, U Int8) → UInt8 | ✅ |
toUInt16OrDefault(numeric or String, UInt16) → UInt16 | ✅ |
toUInt32OrDefault(numeric or String, UInt32) → UInt32 | ✅ |
toUInt64OrDefault(numeric or String, UInt64) → UInt64 | ✅ |
toUInt128OrDefault(numeric or String, U Int128) → UInt128 | ✅ |
toUInt256OrDefault(numeric or String, U Int256) → UInt256 | ✅ |
toFloat32(numeric or String) → Float32 | ✅ |
toFloat64(numeric or String) → Float64 | ✅ |
toFloat32OrZero(numeric or String) → Float32 | ✅ |
toFloat64OrZero(numeric or String) → Float64 | ✅ |
toFloat32OrNull(numeric or String) → Float32 | ✅ |
toFloat64OrNull(numeric or String) → Float64 | ✅ |
toFloat32OrDefault(numeric or String, Float32) → Float32 | ✅ |
toFloat64OrDefault(numeric or String, Float64) → Float64 | ✅ |
toDate(any) → Date | ✅ |
toDateOrZero(any) → Date | ✅ |
toDateOrNull(any) → Date | ✅ |
toDateOrDefault(any [, Date]) → Date | ✅ |
toDateTime(any [, timezone text]) → DateTime | ✅ |
toDateTimeOrZero(any [, timezone text]) → DateTime | ✅ |
toDateTimeOrNull(any [, timezone text]) → DateTime | ✅ |
toDateTimeOrDefault(any [, timezone text] [, DateTime]) → DateTime | ✅ |
toDate32(any) → Date32 | ✅ |
toDate32OrZero(any) → Date32 | ✅ |
toDate32OrNull(any) → Date32 | ✅ |
toDate32OrDefault(any [, Date32]) → Date32 | ✅ |
toDateTime64(expr any, scale integer [, timezone text]) → DateTime64 | ✅ |
toDateTime64OrZero(expr any, scale integer [, timezone text]) → DateTime64 | ✅ |
toDateTime64OrNull(expr any, scale integer [, timezone text]) → DateTime64 | ✅ |
toDateTime64OrDefault(expr any, scale integer [, timezone text] [, DateTime64]) → DateTime64 | ✅ |
toDecimal32(value numeric or text, S numeric) → Decimal32 | ✅ |
toDecimal64(value numeric or text, S numeric) → Decimal64 | ✅ |
toDecimal128(value numeric or text, S numeric) → Decimal128 | ✅ |
toDecimal256(value numeric or text, S numeric) → Decimal256 | ✅ |
toDecimal32OrNull(expr text, S numeric) → Decimal32 | ✅ |
toDecimal64OrNull(expr text, S numeric) → Decimal64 | ✅ |
toDecimal128OrNull(expr text, S numeric) → Decimal128 | ✅ |
toDecimal256OrNull(expr text, S numeric) → Decimal256 | ✅ |
toDecimal32OrDefault(expr text, S numeric, Decimal32) → Decimal32 | ✅ |
toDecimal64OrDefault(expr text, S numeric, Decimal64) → Decimal64 | ✅ |
toDecimal128OrDefault(expr text, S numeric, Decimal128) → Decimal128 | ✅ |
toDecimal256OrDefault(expr text, S numeric, Decimal256) → Decimal256 | ✅ |
toDecimal32OrZero(expr text, S numeric) → Decimal32 | ✅ |
toDecimal64OrZero(expr text, S numeric) → Decimal64 | ✅ |
toDecimal128OrZero(expr text, S numeric) → Decimal128 | ✅ |
toDecimal256OrZero(expr text, S numeric) → Decimal256 | ✅ |
toString(any) → String | ✅ |
toFixedString(s String, N numeric) → FixedString | ✅ |
toStringCutToZero(s String or FixedString) → String | ✅ |
toDecimalString(number numeric, scale UInt8) → String | ✅ |
reinterpretAsUInt8(any) → UInt8 | ✅ |
reinterpretAsUInt16(any) → UInt16 | ✅ |
reinterpretAsUInt32(any) → UInt32 | ✅ |
reinterpretAsUInt64(any) → UInt64 | ✅ |
reinterpretAsInt8(any) → UInt8 | ✅ |
reinterpretAsInt16(any) → UInt16 | ✅ |
reinterpretAsInt32(any) → UInt32 | ✅ |
reinterpretAsInt64(any) → UInt64 | ✅ |
reinterpretAsFloat32(any) → Float32 | ✅ |
reinterpretAsFloat64(any) → Float64 | ✅ |
reinterpretAsDate(any) → Date | ✅ |
reinterpretAsDateTime(any) → DateTime | ✅ |
reinterpretAsString(any) → String | ✅ |
reinterpretAsFixedString(any) → FixedString | ✅ |
reinterpretAsUUID(Fixed String) → UUID | ✅ |
reinterpret(x any, type String) → any | ✅ |
cast(x any, T String) → String | ✅ |
CAST(x any, T String) → String | ✅ |
accurateCast(x any, T String) → any | ✅ |
accurateCastOrNull(x any, T String) → any | ✅ |
accurateCastOrDefault(x any, T String [, default_value any]) → any | ✅ |
toIntervalSecond(integer) → interval | ✅ |
toIntervalMinute(integer) → interval | ✅ |
toIntervalHour(integer) → interval | ✅ |
toIntervalDay(integer) → interval | ✅ |
toIntervalWeek(integer) → interval | ✅ |
toIntervalMonth(integer) → interval | ✅ |
toIntervalQuarter(integer) → interval | ✅ |
toIntervalYear(integer) → interval | ✅ |
parseDateTime(str text, format text [, timezone text]) → timestamp | ✅ |
TO_TIMESTAMP(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeOrZero(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeOrNull(str text, format text [, timezone text]) → timestamp | ✅ |
str_to_date(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeInJodaSyntax(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeInJodaSyntaxOrZero(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeInJodaSyntaxOrNull(str text, format text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffort(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffortUS(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffortOrNull(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTime32BestEffortOrNull(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffortOrZero(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTime32BestEffortOrZero(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffortUSOrNull(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTimeBestEffortUSOrZero(time_string text [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffort(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffortUS(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffortOrNull(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffortOrZero(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffortUSOrNull(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
parseDateTime64BestEffortUSOrZero(time_string text [, precision uint8] [, timezone text]) → timestamp | ✅ |
toLowCardinality(expr any) → any | ✅ |
toUnixTimestamp64Milli(value DateTime64) → Int64 | ✅ |
toUnixTimestamp64Micro(value DateTime64) → Int64 | ✅ |
toUnixTimestamp64Nano(value DateTime64) → Int64 | ✅ |
fromUnixTimestamp64Milli(value Int64) → DateTime64 | ✅ |
fromUnixTimestamp64Micro(value Int64) → DateTime64 | ✅ |
fromUnixTimestamp64Nano(value Int64) → DateTime64 | ✅ |
formatRow(format String, x any, y any [, ...]) → String | ✅ |
formatRowNoNewline(format String, x any, y any [, ...]) → String | ✅ |
snowflakeToDateTime(value Int64 [, time_zone text]) → DateTime snowflakeToDateTime(value Int64 [, time_zone text]) → DateTime64 | ✅ |
dateTimeToSnowflake(value DateTime) → Int64 dateTimeToSnowflake(value DateTime64) → Int64 | ✅ |
Aggregate Functions​
| Function | Supported |
|---|---|
count(expr any) → integer count() → integer count(*) → integer | ✅ |
countIf(expr any, cond boolean) → integer countIf(cond boolean) → integer countIf(*, cond boolean) → integer | ✅ |
boundingRatio(numeric, numeric) → numeric | ✅ |
min(any) → any | ✅ |
minSimpleState(any) → any | ✅ |
minState(any) → any | ✅ |
minMerge(any) → any | ✅ |
max(any) → any | ✅ |
maxSimpleState(any) → any | ✅ |
maxState(any) → any | ✅ |
maxMerge(any) → any | ✅ |
sum(numeric) → numeric | ✅ |
sumIf(numeric, cond boolean) → numeric | ✅ |
sumSimpleState(numeric) → numeric | ✅ |
sumState(numeric) → numeric | ✅ |
sumMerge(numeric) → numeric | ✅ |
avg(x numeric) → float64 | ✅ |
avgIf(x numeric, cond boolean) → float64 | ✅ |
avgSimpleState(numeric) → numeric | ✅ |
avgState(numeric) → numeric | ✅ |
avgMerge(numeric) → numeric | ✅ |
any(any) → any | ✅ |
anySimpleState(any) → any | ✅ |
anyState(any) → any | ✅ |
anyMerge(any) → any | ✅ |
first_value(any) → any | ✅ |
last_value(any) → any | ✅ |
stddevPop(x numeric, y numeric) → float64 | ✅ |
stddevPropStable(x numeric, y numeric) → float64 | ✅ |
STD(x numeric, y numeric) → float64 | ✅ |
STDDEV_POP(x numeric, y numeric) → float64 | ✅ |
stddevSamp(expr numeric) → float64 | ✅ |
stddevSampStable(expr numeric) → float64 | ✅ |
varPop(x numeric, y numeric) → float64 | ✅ |
varPopStable(x numeric, y numeric) → float64 | ✅ |
varSamp(expr numeric) → float64 | ✅ |
varSampStable(expr numeric) → float64 | ✅ |
covarPop(x numeric, y numeric) → float64 | ✅ |
covarPopStable(x numeric, y numeric) → float64 | ✅ |
covarSamp(x numeric, y numeric) → float64 | ✅ |
covarSampStable(x numeric, y numeric) → float64 | ✅ |
anyHeavy(any) → any | ✅ |
anyLast(any) → any | ✅ |
argMax(arg any, val any) → any | ✅ |
argMin(arg any, val any) → any | ✅ |
avgWeighted(x numeric, weight numeric) → numeric | ✅ |
corr(x numeric, y numeric) → numeric | ✅ |
corrStable(x numeric, y numeric) → numeric | ✅ |
groupArrayIntersect(x any) → anyarray | ✅ |
groupBitAnd(expr numeric) → same as input type | ✅ |
groupBitOr(expr numeric) → same as input type | ✅ |
groupBitXor(expr numeric) → same as input type | ✅ |
groupBitmap(expr numeric) → same as input type | ✅ |
groupBitmapAnd(expr numeric) → same as input type | ✅ |
groupBitmapXor(expr numeric) → same as input type | ✅ |
sumWithOverflow(expr numeric) → same as input type | ✅ |
deltaSum(value numeric) → same as input type | ✅ |
deltaSumTimestamp(value numeric, timestamp numeric or date or timestamp) → numeric | ✅ |
rankCorr(x numeric, y numeric) → numeric | ✅ |
sumKahan(numeric) → same as input type | ✅ |
intervalLengthSum(start numeric, end numeric) → numeric | ✅ |
skewPop(expr numeric) → float64 | ✅ |
skewSamp(expr numeric) → float64 | ✅ |
kurtPop(expr numeric) → float64 | ✅ |
kurtSamp(expr numeric) → float64 | ✅ |
uniq(x any [, ...]) → uint64 | ✅ |
uniqSimpleState(x any [, ...]) → uint64 | ✅ |
uniqState(x any [, ...]) → uint64 | ✅ |
uniqMerge(x any [, ...]) → uint64 | ✅ |
uniqExact(x any [, ...]) → uint64 | ✅ |
uniqExactSimpleState(x any [, ...]) → uint64 | ✅ |
uniqExactState(x any [, ...]) → uint64 | ✅ |
uniqExactMerge(x any [, ...]) → uint64 | ✅ |
uniqCombinedSimpleState(x any [, ...]) → uint64 | ✅ |
uniqCombinedState(HLL_precision integer, expr any [, ...]) → uint64 | ✅ |
uniqCombinedMerge(HLL_precision integer, expr any [, ...]) → uint64 | ✅ |
uniqCombined64SimpleState(x any [, ...]) → uint64 | ✅ |
uniqCombined64State(HLL_precision integer, expr any [, ...]) → uint64 | ✅ |
uniqCombined64Merge(HLL_precision integer, expr any [, ...]) → uint64 | ✅ |
uniqHLL12(x any [, ...]) → uint64 | ✅ |
uniqHLL12SimpleState(x any [, ...]) → uint64 | ✅ |
uniqHLL12State(x any [, ...]) → uint64 | ✅ |
uniqHLL12Merge(x any [, ...]) → uint64 | ✅ |
uniqTheta(x any [, ...]) → uint64 | ✅ |
uniqThetaSimpleState(x any [, ...]) → uint64 | ✅ |
uniqThetaState(x any [, ...]) → uint64 | ✅ |
uniqThetaMerge(x any [, ...]) → uint64 | ✅ |
quantileSimpleState(level numeric, expr numeric or date or timestamp) → any | ✅ |
quantileState(level numeric, expr numeric or date or timestamp) → any | ✅ |
quantileMerge(level numeric, expr numeric or date or timestamp) → any | ✅ |
UUID Functions​
| Function | Supported |
|---|---|
generateUUIDv4(x any) → uuid | ✅ |
empty(uuid uuid) → boolean | ✅ |
notEmpty(uuid uuid) → boolean | ✅ |
toUUID(x text) → uuid | ✅ |
toUUIDOrDefault(x text, default uuid) → uuid | ✅ |
toUUIDOrNull(x text) → uuid | ✅ |
toUUIDOrZero(x text) → uuid | ✅ |
UUIDStringToNum(text [, variant integer]) → text | ✅ |
UUIDNumToString(text [, variant integer]) → text | ✅ |