# SQL functions

This page lists every function that GizDB runs by name. A function that has the name of a PostgreSQL 16 function works
as that function does. A label after a description names the database whose function it follows otherwise: MySQL,
SQLite, Oracle, SQL Server, StarRocks, pgvector, TimescaleDB, or GizDB for its own functions. Brackets show optional
arguments, and `...` shows that more arguments of the same kind can follow. "Rows" marks a function that returns rows
(use it in `FROM`).

The SQL tests (`tests/db/functions`) fail when GizDB runs a function that this page does not list, or when this page
lists a function that GizDB does not run. A type name also casts as a function (`int4('7')`, `date('2026-10-22')`); the
types are in [Dialects](../guides/data.md#dialects).

## Conditions and comparisons

| Function | Result |
| --- | --- |
| `coalesce(value, ...)` | The first argument that is not NULL. |
| `nullif(a, b)` | NULL when `a` equals `b`, else `a`. |
| `greatest(value, ...)`, `least(value, ...)` | The largest or the smallest argument. NULL arguments are left out. |
| `ifnull(value, other)` | `other` when `value` is NULL. MySQL, SQLite. |
| `nvl(value, other)` | `other` when `value` is NULL. Oracle. |
| `nvl2(value, if_not_null, if_null)` | The second argument when `value` is not NULL, else the third. Oracle. |
| `isnull(value)`, `isnull(value, other)` | With one argument, whether `value` is NULL (MySQL). With two, `ifnull` (SQL Server). |
| `if(condition, then, otherwise)`, `iif(condition, then, otherwise)` | `then` when `condition` is true, else `otherwise`. MySQL; SQL Server, SQLite. |
| `decode(value, search, result [, search, result ...] [, default])` | The result of the first `search` that equals `value`; NULL equals NULL. Oracle. With two text arguments it is PostgreSQL's `decode(text, format)` (Binary data and hashes). |
| `lnnvl(condition)` | True when `condition` is false or unknown. Oracle. |
| `nanvl(x, other)` | `other` when `x` is NaN. Oracle. |
| `zeroifnull(x)` | 0 when `x` is NULL. Snowflake. |
| `num_nulls(value, ...)`, `num_nonnulls(value, ...)` | How many arguments are NULL, or are not NULL. |
| `field(value, first, ...)` | The position of `value` in the list from 1, or 0. MySQL. |
| `row(value, ...)` | A row of the values. |

## Numbers

| Function | Result |
| --- | --- |
| `abs(x)`, `sign(x)` | The absolute value; -1, 0 or 1. |
| `ceil(x)`, `ceiling(x)`, `floor(x)` | The nearest integer above or below. |
| `round(x [, digits])`, `trunc(x [, digits])` | `x` rounded, or cut, to `digits` decimal places (0 when left out). |
| `truncate(x, digits)` | `trunc`. MySQL. |
| `div(a, b)`, `mod(a, b)` | The integer quotient and the remainder of `a / b`. |
| `pmod(a, b)` | The remainder of `a / b` that is not negative. StarRocks. |
| `remainder(a, b)` | `a - b * round(a / b)`. Oracle. |
| `gcd(a, b)`, `lcm(a, b)` | The greatest common divisor and the least common multiple. |
| `factorial(n)` | `n!`. |
| `sqrt(x)`, `cbrt(x)` | The square root and the cube root. |
| `exp(x)`, `ln(x)`, `log10(x)`, `log2(x)` | e to the power `x`, and the logarithms. |
| `log(x)`, `log(base, x)` | The base-10 logarithm, or the logarithm in `base`. |
| `pow(x, y)`, `power(x, y)` | `x` to the power `y`. |
| `pi()` | π. |
| `degrees(radians)`, `radians(degrees)` | The angle in the other unit. |
| `sin(x)`, `cos(x)`, `tan(x)`, `cot(x)` | The trigonometric functions of radians. |
| `asin(x)`, `acos(x)`, `atan(x)`, `atan2(y, x)` | The inverses, in radians. |
| `atn2(y, x)` | `atan2`. SQL Server. |
| `sind(x)`, `cosd(x)`, `tand(x)`, `cotd(x)`, `asind(x)`, `acosd(x)`, `atand(x)`, `atan2d(y, x)` | The same in degrees. |
| `sinh(x)`, `cosh(x)`, `tanh(x)`, `asinh(x)`, `acosh(x)`, `atanh(x)` | The hyperbolic functions and their inverses. |
| `erf(x)`, `erfc(x)` | The error function and its complement. |
| `scale(x)`, `min_scale(x)`, `trim_scale(x)` | The decimal places of a numeric, the fewest that keep its value, and the value with only those. |
| `width_bucket(x, low, high, count)`, `width_bucket(x, bounds)` | The bucket of `x` among `count` equal buckets, or among the bounds of an array. |
| `random([seed])`, `rand([seed])` | A number from 0 up to 1. `rand` is MySQL's. |
| `random_normal([mean [, deviation]])` | A number from the normal distribution. |
| `bitand(a, b)` | The bits that both integers have. Oracle. |
| `bit_count(x)` | The bits set in a bit string or bytes. |
| `cash_words(money)` | A money value in English words. |
| `binary_double_infinity()`, `binary_double_nan()`, `binary_float_infinity()`, `binary_float_nan()` | Infinity and NaN of the floating types. Oracle. |

## Text

| Function | Result |
| --- | --- |
| `length(text)`, `char_length(text)`, `character_length(text)` | The characters of the text. |
| `len(text)` | The characters without the trailing spaces. SQL Server. |
| `lengthc(text)`, `lengthb(text)` | The characters, or the bytes. Oracle. |
| `octet_length(text)`, `vsize(value)` | The bytes; `vsize` is Oracle's. |
| `lower(text)`, `upper(text)`, `initcap(text)` | The text in lower case, in upper case, or with each word capitalized. |
| `lcase(text)`, `ucase(text)` | `lower` and `upper`. MySQL. |
| `nls_lower(text [, settings])`, `nls_upper(text [, settings])`, `nls_initcap(text [, settings])` | The same with Oracle's language settings. Oracle. |
| `concat(value, ...)`, `concat_ws(separator, value, ...)` | The values joined; NULLs are left out. |
| `format(pattern, value, ...)` | The pattern with `%s`, `%I` and `%L` filled. |
| `printf(pattern, value, ...)` | The pattern with C's `%` codes filled. SQLite. |
| `left(text, n)`, `right(text, n)` | The first or the last `n` characters. |
| `substring(text, from [, count])`, `substr(text, from [, count])` | Part of the text, from character `from` (1 is the first). Also `substring(text FROM from FOR count)`. |
| `mid(text, from [, count])` | `substring`. MySQL. |
| `substrb(text, from [, count])` | Part of the text, counted in bytes. Oracle. |
| `substring_index(text, separator, count)` | The text before the `count`-th separator; a negative count counts from the end. MySQL. |
| `split_part(text, separator, n)` | The `n`-th field. |
| `position(part, text)`, `strpos(text, part)` | Where `part` starts in the text, or 0. Also `position(part IN text)`. |
| `instr(text, part [, from [, nth]])`, `instrb(text, part [, from [, nth]])` | Where the `nth` `part` starts, in characters or bytes. Oracle; MySQL has `instr`. |
| `charindex(part, text [, from])` | Where `part` starts. SQL Server. |
| `locate(part, text [, from])` | Where `part` starts. MySQL. |
| `starts_with(text, prefix)`, `ends_with(text, suffix)` | Whether the text starts or ends with the part. |
| `lpad(text, length [, fill])`, `rpad(text, length [, fill])` | The text filled to `length` on the left or the right. |
| `trim(text)`, `btrim(text [, characters])` | The text without the characters (spaces) at both ends. Also `trim([BOTH \| LEADING \| TRAILING] [characters] FROM text)`. |
| `ltrim(text [, characters])`, `rtrim(text [, characters])` | The same at the start or the end. They also trim bytes. |
| `repeat(text, n)`, `replicate(text, n)` | The text `n` times; `replicate` is SQL Server's. |
| `space(n)` | `n` spaces. MySQL, SQL Server. |
| `replace(text, from, to)` | Each `from` replaced by `to`. |
| `translate(text, from, to)` | Each character of `from` replaced by the character at the same place in `to`. |
| `reverse(text)` | The characters in the opposite order. |
| `ascii(text)`, `unicode(text)` | The code of the first character. `unicode` is SQL Server's and SQLite's. |
| `chr(code)` | The character of the code. |
| `char(code, ...)`, `nchar(code, ...)` | The characters of the codes. MySQL; SQL Server. |
| `asciistr(text)` | The text with each character that is not ASCII written as `\XXXX`. Oracle. |
| `unistr(text)` | The text with each `\XXXX` escape decoded. |
| `normalize(text [, form])`, `is_normalized(text [, form])` | The text in Unicode form NFC, NFD, NFKC or NFKD, and whether it is in it. |
| `quote_ident(text)`, `quote_literal(value)`, `quote_nullable(value)` | The text as an identifier or as a literal for SQL; `quote_nullable` gives `NULL` for NULL. |
| `quote(value)` | The value as an SQL literal. SQLite. |
| `parse_ident(text [, strict])` | The parts of a qualified identifier, as an array. |
| `soundex(text)` | The Soundex code. |
| `md5(text)` | The MD5 hash, in hexadecimal. |
| `to_hex(n)` | The integer in hexadecimal. |
| `to_char(value [, format])`, `to_nchar(value [, format])` | A number or a time as text, in the format. `to_nchar` is Oracle's. |
| `to_number(text [, format])` | The text as a number, read in the format. |
| `like(pattern, text [, escape])`, `glob(pattern, text)` | `text LIKE pattern` and `text GLOB pattern`. SQLite. |
| `similar_to_escape(pattern [, escape])` | The regular expression of a `SIMILAR TO` pattern. |
| `dump(value [, format])` | The type, the length and the bytes of a value. Oracle. |
| `to_clob(value)`, `to_nclob(value)`, `to_blob(value)`, `empty_clob()`, `empty_blob()` | Large text and bytes values. Oracle. |

## Regular expressions

The patterns are POSIX regular expressions, as in PostgreSQL. `flags` are letters such as `i` (ignore case) and `g` (all
matches).

| Function | Result |
| --- | --- |
| `regexp_like(text, pattern [, flags])` | Whether the pattern matches. |
| `regexp(text, pattern [, flags])` | `regexp_like`. MySQL, SQLite. |
| `regexp_count(text, pattern [, from [, flags]])` | How many times the pattern matches. |
| `regexp_instr(text, pattern [, from [, nth [, end [, flags [, group]]]]])` | Where a match starts (or ends). |
| `regexp_substr(text, pattern [, from [, nth [, flags [, group]]]])` | The text of a match. |
| `regexp_match(text, pattern [, flags])` | The groups of the first match, as an array. |
| `regexp_matches(text, pattern [, flags])` | Rows: the groups of each match. |
| `regexp_replace(text, pattern, replacement [, from [, nth]] [, flags])` | The text with the matches replaced. |
| `regexp_split_to_array(text, pattern [, flags])` | The parts between the matches, as an array. |
| `regexp_split_to_table(text, pattern [, flags])` | Rows: the parts between the matches. |

## Binary data and hashes

| Function | Result |
| --- | --- |
| `encode(bytes, format)`, `decode(text, format)` | Bytes as `base64`, `escape` or `hex` text, and back. |
| `get_bit(bytes, n)`, `get_byte(bytes, n)` | One bit or byte, counted from 0. |
| `set_bit(bytes, n, bit)`, `set_byte(bytes, n, byte)` | The bytes with one bit or byte changed. |
| `hex(value)` | The bytes or the number in hexadecimal. MySQL, SQLite. |
| `hextoraw(text)`, `rawtohex(bytes)` | Hexadecimal text as bytes, and back. Oracle. |
| `sha224(bytes)`, `sha256(bytes)`, `sha384(bytes)`, `sha512(bytes)` | The SHA-2 hash. |
| `digest(data, algorithm)` | The hash of `md5`, `sha1`, `sha224`, `sha256`, `sha384` or `sha512`. pgcrypto. |
| `standard_hash(value [, algorithm])` | The SHA-1 hash, or the hash of `algorithm`. Oracle. |
| `ora_hash(value [, buckets [, seed]])` | A bucket number from the hash of the value. Oracle. |
| `crc32(value)` | The CRC-32 of the value. MySQL. |

## Dates and times

| Function | Result |
| --- | --- |
| `now()`, `current_timestamp`, `transaction_timestamp()` | The time when the transaction started. |
| `statement_timestamp()`, `clock_timestamp()` | The time when the statement started, and the time now. |
| `current_date`, `current_time`, `localtime`, `localtimestamp` | The date, the time with its zone, and the local time and timestamp. |
| `getdate()`, `sysdatetime()`, `getutcdate()` | The local time, and the UTC time. SQL Server. |
| `sysdate()`, `systimestamp()` | The time now. Oracle. |
| `curdate()`, `curtime()`, `utc_timestamp()` | The date, the time and the UTC timestamp. MySQL. |
| `today()` | The date. |
| `dbtimezone()`, `sessiontimezone()` | The time zone of the database and of the session. Oracle. |
| `make_date(year, month, day)`, `make_time(hour, minute, second)` | A date, or a time. |
| `make_timestamp(year, month, day, hour, minute, second)`, `make_timestamptz(..., [zone])` | A timestamp, without or with a time zone. |
| `make_interval([years [, months [, weeks [, days [, hours [, minutes [, seconds]]]]]]])` | An interval. |
| `datefromparts(year, month, day)` | A date. SQL Server. |
| `to_date(text [, format])`, `to_timestamp(text [, format])`, `to_timestamp(seconds)` | The text read in the format, or the Unix time as a timestamp. |
| `to_timestamp_tz(text [, format])`, `from_tz(timestamp, zone)` | A timestamp with a time zone. Oracle. |
| `str_to_date(text [, format])` | The text read in a MySQL format. MySQL. |
| `numtodsinterval(n, unit)`, `numtoyminterval(n, unit)` | An interval of days to seconds, or of years and months. Oracle. |
| `date(time, modifier ...)`, `time(time, modifier ...)`, `datetime(time, modifier ...)`, `julianday(time, ...)`, `unixepoch(time, ...)`, `strftime(format, time, ...)`, `timediff(a, b)` | SQLite's date and time functions. SQLite. |
| `date_part(field, source)`, `datepart(field, source)` | One field of a date, a time or an interval, such as `year` or `epoch`. Also `extract(field FROM source)`. `datepart` is SQL Server's. |
| `year(t)`, `quarter(t)`, `month(t)`, `week(t)`, `day(t)`, `hour(t)`, `minute(t)`, `second(t)` | One field. MySQL. |
| `dayofweek(t)`, `dayofyear(t)`, `weekday(t)` | The day in the week (1 is Sunday), in the year, and in the week (0 is Monday). MySQL. |
| `date_format(t [, format])` | The time as text, in a MySQL format. MySQL. |
| `unix_timestamp([t])` | The seconds since 1970-01-01 UTC. MySQL. |
| `isfinite(value)` | Whether a date, a timestamp or an interval is finite. |
| `age(a [, b])` | The interval from `b` (midnight today when left out) to `a`, in years, months and days. |
| `date_add(t, interval [, zone])`, `date_subtract(t, interval [, zone])` | `t` plus or minus the interval. |
| `adddate(t, n)`, `subdate(t, n)`, `date_sub(t, interval)` | `t` plus or minus days or an interval. MySQL. |
| `dateadd(unit, n, t)` | `t` plus `n` units. SQL Server. |
| `timestampadd(unit, n, t)`, `timestampdiff(unit, a, b)` | `t` plus `n` units, and the units from `a` to `b`. MySQL. |
| `datediff(unit, a, b)`, `datediff(a, b)`, `date_diff(unit, a, b)` | The units, or the days, between two times. SQL Server; MySQL. |
| `add_months(t, n)`, `months_between(a, b)` | `t` plus `n` months, and the months between two dates. Oracle. |
| `last_day(t)`, `eomonth(t)` | The last day of the month. Oracle, MySQL; SQL Server. |
| `next_day(t, weekday)` | The first date after `t` that is that day of the week. Oracle. |
| `date_trunc(unit, t [, zone])`, `datetrunc(unit, t)` | `t` cut down to the start of its unit. `datetrunc` is SQL Server's. |
| `date_bin(stride, t, origin)` | `t` cut down to the start of its stride, counted from `origin`. |
| `time_bucket(width, t [, origin])` | The start of the bucket of `t`. Buckets are counted from 2000-01-03, a Monday; a width of months counts calendar months. TimescaleDB. |
| `justify_days(interval)`, `justify_hours(interval)`, `justify_interval(interval)` | The interval with 30 days as a month, 24 hours as a day, or both. |
| `timezone(zone, t)` | `t AT TIME ZONE zone`. |
| `sys_extract_utc(t)`, `tz_offset(zone)` | The UTC time of a timestamp with a zone, and the offset of a zone. Oracle. |

## JSON

| Function | Result |
| --- | --- |
| `to_json(value)`, `to_jsonb(value)`, `array_to_json(array [, pretty])` | The value as JSON. |
| `json(value)` | The value as JSON text. SQLite. |
| `json_build_array(value, ...)`, `jsonb_build_array(value, ...)`, `json_array(value, ...)` | A JSON array of the values. |
| `json_build_object(key, value, ...)`, `jsonb_build_object(key, value, ...)` | A JSON object of the pairs. |
| `json_object(keys [, values])`, `jsonb_object(keys [, values])` | A JSON object from text arrays. |
| `json_quote(text)` | The text as a JSON string. SQLite, MySQL. |
| `parse_json(text)`, `json_parse(text)` | The text read as JSON. StarRocks. |
| `json_serialize(json)` | The JSON as text. |
| `json_extract_path(json, key, ...)`, `jsonb_extract_path(json, key, ...)` | The value at the path. `#>` is the operator. |
| `json_extract_path_text(json, key, ...)`, `jsonb_extract_path_text(json, key, ...)` | The value at the path, as text. `#>>` is the operator. |
| `json_extract(json, path, ...)`, `jsonb_extract(json, path, ...)` | The value at a `$.a.b` path. SQLite, MySQL. |
| `get_json_string(json, path)`, `get_json_int(json, path)`, `get_json_double(json, path)` | The value at a `$.a.b` path, as text, an integer or a float. StarRocks. |
| `json_value(json, path ...)`, `json_query(json, path ...)` | The scalar, or the object or array, at an SQL/JSON path. |
| `json_array_length(json)`, `jsonb_array_length(json)` | The elements of a JSON array. |
| `json_typeof(json)`, `jsonb_typeof(json)` | `object`, `array`, `string`, `number`, `boolean` or `null`. |
| `json_type(json [, path])` | The type at the path. SQLite, MySQL. |
| `json_valid(text)`, `isjson(text)` | Whether the text is JSON. SQLite, MySQL; SQL Server. |
| `jsonb_pretty(json)` | The JSON as indented text. |
| `json_strip_nulls(json)`, `jsonb_strip_nulls(json)` | The JSON without object fields that are null. |
| `json_exists(json, path ...)` | Whether an SQL/JSON path finds an item. |
| `json_populate_record(base, json)`, `jsonb_populate_record(base, json)` | The row type of `base` with the fields of a JSON object. |
| `json_to_record(json)`, `jsonb_to_record(json)` | A row of a JSON object; `AS t(column type, ...)` names its columns. |
| `json_populate_recordset(base, json)`, `jsonb_populate_recordset(base, json)`, `json_to_recordset(json)`, `jsonb_to_recordset(json)` | Rows: the same for each object of a JSON array. |
| `jsonb_set(json, path, value [, create])`, `jsonb_set_lax(json, path, value [, create [, null_treatment]])` | The JSON with the value at the path replaced. |
| `jsonb_insert(json, path, value [, after])` | The JSON with the value inserted at the path. |
| `json_set(json, path, value, ...)`, `json_insert(json, path, value, ...)`, `json_replace(json, path, value, ...)`, `json_remove(json, path, ...)` | The JSON with the values at `$.a.b` paths set, added, replaced or removed. SQLite, MySQL. |
| `json_modify(json, path, value)` | The JSON with the value at the path set. SQL Server. |
| `jsonb_concat(a, b)`, `jsonb_contains(a, b)`, `jsonb_contained(a, b)` | The functions of `\|\|`, `@>` and `<@`. |
| `jsonb_delete(json, key)`, `jsonb_delete_array(json, keys)`, `jsonb_delete_path(json, path)` | The functions of `-` and `#-`. |
| `jsonb_exists(json, key)`, `jsonb_exists_any(json, keys)`, `jsonb_exists_all(json, keys)` | The functions of `?`, `?\|` and `?&`. |
| `jsonb_path_exists(json, path [, vars [, silent]])`, `jsonb_path_match(...)`, `jsonb_path_query_array(...)`, `jsonb_path_query_first(...)` | Whether the SQL/JSON path finds an item, the result of a path predicate, all items as an array, and the first item. |
| `jsonb_path_exists_tz(...)`, `jsonb_path_match_tz(...)`, `jsonb_path_query_array_tz(...)`, `jsonb_path_query_first_tz(...)` | The same, with time zones in date and time items. |
| `jsonb_path_query(json, path [, vars [, silent]])`, `jsonb_path_query_tz(...)` | Rows: each item that the path finds. |
| `json_array_elements(json)`, `jsonb_array_elements(json)`, `json_array_elements_text(json)`, `jsonb_array_elements_text(json)` | Rows: each element of a JSON array, as JSON or as text. |
| `json_each(json)`, `jsonb_each(json)`, `json_each_text(json)`, `jsonb_each_text(json)` | Rows: each key and value of a JSON object. |
| `json_object_keys(json)`, `jsonb_object_keys(json)` | Rows: each key of a JSON object. |

## Arrays

| Function | Result |
| --- | --- |
| `array_append(array, value)`, `array_prepend(value, array)`, `array_cat(a, b)` | The array with a value at the end or the start, and two arrays joined. |
| `array_length(array [, dimension])`, `cardinality(array)` | The elements of a dimension, and of all dimensions. |
| `array_lower(array [, dimension])`, `array_upper(array [, dimension])`, `array_ndims(array)`, `array_dims(array)` | The bounds, the number of dimensions, and the dimensions as text. |
| `array_fill(value, lengths [, lower_bounds])` | An array of the value. |
| `array_position(array, value [, from])`, `array_positions(array, value)` | The first place of the value, and all places. |
| `array_remove(array, value)`, `array_replace(array, from, to)` | The array without the value, and with each `from` replaced. |
| `array_sample(array, n)`, `array_shuffle(array)` | `n` random elements, and the elements in random order. |
| `array_to_string(array, separator [, null_text])`, `string_to_array(text, separator [, null_text])` | The elements joined as text, and text split into an array. |
| `string_to_table(text, separator [, null_text])` | Rows: the parts of the text. |
| `trim_array(array, n)` | The array without its last `n` elements. |
| `generate_subscripts(array, dimension [, reverse])` | Rows: the subscripts of a dimension. |
| `unnest(array)` | Rows: the elements. |

## Ranges

| Function | Result |
| --- | --- |
| `int4range(low, high [, bounds])`, `int8range(...)`, `numrange(...)`, `daterange(...)`, `tsrange(...)`, `tstzrange(...)` | A range. `bounds` is `'[)'` when left out. |
| `multirange(range)` | A multirange of one range. |
| `isempty(range)` | Whether the range is empty. |
| `lower_inc(range)`, `upper_inc(range)`, `lower_inf(range)`, `upper_inf(range)` | Whether a bound is in the range, and whether it is infinite. `lower` and `upper` give the bounds. |
| `range_merge(a, b)` | The smallest range that holds both. |

## Network addresses

| Function | Result |
| --- | --- |
| `host(inet)`, `abbrev(inet)`, `family(inet)`, `masklen(inet)` | The address as text, the short text form, 4 or 6, and the length of the mask. |
| `netmask(inet)`, `hostmask(inet)`, `network(inet)`, `broadcast(inet)` | The masks, the network and the broadcast address. |
| `macaddr8_set7bit(macaddr8)` | The address with its 7th bit set. |
| `set_masklen(inet, length)` | The address with another mask length. |
| `inet_same_family(a, b)`, `inet_merge(a, b)` | Whether both addresses are IPv4 or both IPv6, and the smallest network that holds both. |

## Text search

Text search is the one of PostgreSQL: `tsvector` documents and `tsquery` queries. GizDB's BM25 search is in [Search text
and vectors](../guides/data.md#search-text-and-vectors).

| Function | Result |
| --- | --- |
| `to_tsvector([config,] text)`, `json_to_tsvector(config, json, filter)`, `jsonb_to_tsvector(config, json, filter)` | The lexemes of a text, or of the strings of a JSON value. |
| `array_to_tsvector(array)`, `tsvector_to_array(tsvector)` | A document of the lexemes, and the lexemes of a document. |
| `setweight(tsvector, weight [, lexemes])`, `strip(tsvector)` | The document with weights set, or without positions and weights. |
| `ts_delete(tsvector, lexemes)`, `ts_filter(tsvector, weights)` | The document without the lexemes, or with only the weights. |
| `to_tsquery([config,] text)`, `plainto_tsquery([config,] text)`, `phraseto_tsquery([config,] text)`, `websearch_to_tsquery([config,] text)` | A query from its syntax, from words, from a phrase, or from the syntax of a web search. |
| `tsquery_phrase(a, b [, distance])`, `querytree(tsquery)`, `numnode(tsquery)` | Two queries as a phrase, the part an index can use, and the size of a query. |
| `ts_rewrite(tsquery, target, substitute)` | The query with a part replaced. |
| `ts_rank([weights,] tsvector, tsquery [, normalization])`, `ts_rank_cd(...)` | The rank of a document for a query, by frequency or by cover density. |
| `ts_headline([config,] document, tsquery [, options])` | The document with the matches marked. |
| `ts_lexize(dictionary, token)` | The lexemes of a token in a dictionary. |
| `ts_debug([config,] text)`, `ts_parse(parser, text)`, `ts_token_type(parser)`, `ts_stat(query [, weights])` | Rows: how a text is read, its tokens, the token types, and the lexemes of the documents of a query. |
| `match(column, query)`, `score()` | In `WHERE`, the rows that a BM25 index finds; in the select list or `ORDER BY`, their score. GizDB. |
| `highlight(text, query, before, after, max_characters)` | An excerpt of the text with each match between `before` and `after`. GizDB. |

## Vectors

The `vector` type, the distance operators `<->`, `<=>` and `<#>`, and the indexes are in [Vector search in
GizDB](../guides/vector.md).

| Function | Result |
| --- | --- |
| `vector(value)`, `to_vector(value)`, `string_to_vector(text)`, `vec_f32(value)` | The value as a `vector`. |
| `vector_dims(v)`, `vector_dimension_count(v)`, `vec_length(v)` | The dimensions. |
| `vector_norm(v)`, `l2_norm(v)`, `l2_normalize(v)` | The Euclidean length, and the vector with length 1. pgvector. |
| `subvector(v, from, count)`, `binary_quantize(v)` | Part of a vector, and a bit string with 1 where an element is above 0. pgvector. |
| `l2_distance(a, b)`, `euclidean_distance(a, b)`, `vec_distance_l2(a, b)` | The Euclidean distance (`<->`). |
| `distance(a, b [, metric])`, `vector_distance(a, b [, metric])` | The distance of the metric `l2` (default), `cosine` or `inner`. |
| `cosine_distance(a, b)`, `vec_distance_cosine(a, b)` | 1 minus the cosine of the angle (`<=>`). |
| `inner_product(a, b)`, `dot_product(a, b)`, `negative_inner_product(a, b)` | The inner product, and its negative (`<#>`). |
| `l1_distance(a, b)`, `hamming_distance(a, b)`, `jaccard_distance(a, b)` | The taxicab distance of vectors, and the distances of bit strings. pgvector. |

## Search ranking

| Function | Result |
| --- | --- |
| `rrf()` | In a hybrid search (`match` OR `near` in `WHERE`), the reciprocal rank fusion score of the row. GizDB. |
| `rrf(rank, rank, k [, weight, weight])` | The reciprocal rank fusion score of two ranks; a NULL rank is a search that did not find the row. GizDB. |
| `recall_at_k(ranked, relevant [, k])`, `precision_at_k(ranked, relevant [, k])` | The recall and the precision of the first `k` ranked ids (all when `k` is 0 or NULL). GizDB. |
| `ndcg_at_k(ranked, relevant [, k [, gains]])` | The normalized discounted cumulative gain; `gains` are the graded relevance of each relevant id. GizDB. |
| `reciprocal_rank(ranked, relevant [, k])`, `average_precision(ranked, relevant [, k])` | One query's term of MRR and of MAP: `avg` of them over the queries is the mean. GizDB. |

## Machine learning

`pgml.train`, `pgml.predict`, `pgml.embed` and the other functions of PostgresML are in [the reference of Machine
learning in GizDB](../guides/sql-ml.md#reference).

| Function | Result |
| --- | --- |
| `pgml.add(a, b)`, `pgml.subtract(a, b)`, `pgml.multiply(a, b)`, `pgml.divide(a, b)` | Two arrays of numbers, element by element. PostgresML. |
| `pgml.dot_product(a, b)`, `pgml.cosine_similarity(a, b)`, `pgml.distance_l1(a, b)`, `pgml.distance_l2(a, b)` | The product, the similarity and the distances of two arrays. PostgresML. |
| `pgml.norm_l0(a)`, `pgml.norm_l1(a)`, `pgml.norm_l2(a)`, `pgml.norm_max(a)` | The norms of an array. PostgresML. |
| `pgml.normalize_l1(a)`, `pgml.normalize_l2(a)`, `pgml.normalize_max(a)` | The array divided by its norm. PostgresML. |
| `ols_predict(model, x, ...)` | The prediction of a model that `ols` returned (Aggregate functions). GizDB. |

## Metrics

The functions read the JSON carriers `giz.metric.counter.v1` and `giz.metric.histogram.v1`. GizDB.

| Function | Result |
| --- | --- |
| `metric_counter(json)`, `metric_histogram(json)` | A counter, or a histogram, read and checked. |
| `metric_counter_delta(current, previous)`, `metric_histogram_delta(current, previous)` | A cumulative value as the change from the previous one. |
| `metric_counter_cumulative(delta, previous)`, `metric_histogram_cumulative(delta, previous)` | A change added to the previous cumulative value. |
| `metric_counter_rate(current, previous)` | The change for each second. |
| `metric_histogram_quantile(histogram, p)` | The bounds that hold the quantile `p`. |

## XML

| Function | Result |
| --- | --- |
| `xmlcomment(text)`, `xmlconcat(xml, ...)` | An XML comment, and XML values joined. |
| `xml_is_well_formed(text)`, `xml_is_well_formed_document(text)`, `xml_is_well_formed_content(text)` | Whether the text is XML, an XML document, or XML content. |
| `xpath(path, xml [, namespaces])`, `xpath_exists(path, xml [, namespaces])` | The nodes that an XPath finds, as an array, and whether it finds one. |
| `xmlforest(value [AS name], ...)` | An XML element for each value, named by its column or its `AS` name. |

## Identifiers and sequences

| Function | Result |
| --- | --- |
| `gen_random_uuid()`, `uuid_generate_v4()` | A random UUID. |
| `uuid()`, `newid()`, `sys_guid()` | A random UUID. MySQL; SQL Server; Oracle. |
| `nextval(sequence)`, `currval(sequence)`, `lastval()` | The next value of a sequence, and the last value that the session took. |
| `last_insert_rowid()`, `changes()` | The row id of the last insert, and the rows that the last statement changed. SQLite. |
| `scope_identity()` | The last identity value of the session. SQL Server. |

## Session and server

| Function | Result |
| --- | --- |
| `current_database()`, `current_catalog`, `db_name()`, `ora_database_name()` | The name of the database. `db_name` is SQL Server's, `ora_database_name` Oracle's. |
| `current_schema()`, `current_schemas(include_implicit)`, `schema_name()` | The first schema of the search path, and all of them. `schema_name` is SQL Server's. |
| `current_user`, `current_role`, `session_user`, `user` | The name of the role. |
| `suser_name()`, `uid()`, `userenv(parameter)` | The user, and session facts. SQL Server; Oracle. |
| `version()`, `sqlite_version()` | The version of the server. `sqlite_version` is SQLite's. |
| `getdatabaseencoding()`, `pg_client_encoding()` | The encodings: always `UTF8`. |
| `current_setting(name [, missing_ok])`, `set_config(name, value, is_local)` | A setting of the session, and a change of it. |
| `pg_backend_pid()` | The id of the session. |
| `pg_sleep(seconds)` | Waits. |
| `pg_notify(channel, payload)`, `pg_notification_queue_usage()` | Sends a notification to the listeners of a channel, and the part of the queue in use. |
| `pg_is_in_recovery()`, `pg_promote([wait [, seconds]])` | Whether the server is a standby, and makes a standby the primary. |
| `pg_trigger_depth()` | How many triggers are running in each other. |
| `pg_typeof(value)`, `typeof(value)` | The type of a value. `typeof` is SQLite's. |
| `pg_column_size(value)` | The bytes that a value takes. |
| `pg_input_is_valid(text, type)` | Whether the text is a value of the type. |
| `pg_input_error_info(text, type)` | Rows: the error that reading the text as the type gives (`message`, `detail`, `hint`, `sql_error_code`). |
| `pg_split_walfile_name(name)` | Rows: the segment number and the timeline of a WAL file name. |
| `pg_collation_for(value)` | The collation of a value. |
| `pg_log_backend_memory_contexts(pid)` | Logs the memory of a session. |
| `table(collection)` | Rows: the elements of a nested table or varray. Oracle. |

## Transactions and snapshots

| Function | Result |
| --- | --- |
| `pg_current_xact_id()`, `pg_current_xact_id_if_assigned()`, `txid_current()`, `txid_current_if_assigned()` | The id of the transaction. |
| `pg_current_snapshot()`, `txid_current_snapshot()` | The snapshot of the transaction. |
| `pg_snapshot_xmin(snapshot)`, `pg_snapshot_xmax(snapshot)`, `txid_snapshot_xmin(snapshot)`, `txid_snapshot_xmax(snapshot)` | The bounds of a snapshot. |
| `pg_snapshot_xip(snapshot)`, `txid_snapshot_xip(snapshot)` | Rows: the transactions in progress in a snapshot. |
| `pg_visible_in_snapshot(id, snapshot)`, `txid_visible_in_snapshot(id, snapshot)` | Whether a transaction is visible in a snapshot. |
| `pg_xact_status(id)`, `txid_status(id)` | `committed`, `aborted`, `in progress`, or NULL. |

## Advisory locks

A lock is a `bigint` key or two `integer` keys. A session lock is held until its unlock or the end of the session, an
`xact` lock until the end of the transaction. Refer to [Named locks](../internals/database.md#named-locks).

| Function | Result |
| --- | --- |
| `pg_advisory_lock(key)`, `pg_advisory_lock_shared(key)` | Waits for the lock and takes it. |
| `pg_advisory_xact_lock(key)`, `pg_advisory_xact_lock_shared(key)` | The same, until the end of the transaction. |
| `pg_try_advisory_lock(key)`, `pg_try_advisory_lock_shared(key)`, `pg_try_advisory_xact_lock(key)`, `pg_try_advisory_xact_lock_shared(key)` | Takes the lock if it is free; whether it did. |
| `pg_advisory_unlock(key)`, `pg_advisory_unlock_shared(key)`, `pg_advisory_unlock_all()` | Releases a lock (whether it was held), or all locks of the session. |

## Catalog

These functions read the system catalogs, as in PostgreSQL.

| Function | Result |
| --- | --- |
| `format_type(type, modifier)` | The SQL name of a type. |
| `col_description(table, column)`, `obj_description(object [, catalog])`, `shobj_description(object, catalog)` | The comment of a column or an object. |
| `pg_get_constraintdef(constraint [, pretty])`, `pg_get_indexdef(index [, column, pretty])`, `pg_get_triggerdef(trigger [, pretty])`, `pg_get_ruledef(rule [, pretty])`, `pg_get_viewdef(view [, pretty])` | The SQL that defines the object. |
| `pg_get_functiondef(function)`, `pg_get_function_arguments(function)`, `pg_get_function_identity_arguments(function)`, `pg_get_function_result(function)` | The SQL of a function, of its arguments and of its result. |
| `pg_get_statisticsobjdef(statistics)`, `pg_get_statisticsobjdef_columns(statistics)` | The SQL of an extended statistics object, and of its columns. |
| `pg_get_expr(expression, relation [, pretty])` | The SQL of a stored expression. |
| `pg_get_serial_sequence(table, column)` | The sequence of a serial or identity column. |
| `pg_get_userbyid(role)` | The name of a role. |
| `pg_describe_object(catalog, object, sub)`, `pg_get_object_address(type, names, arguments)` | An object as text, and the address of a named object. |
| `pg_table_is_visible(table)`, `pg_type_is_visible(type)`, `pg_function_is_visible(function)` | Whether the search path finds the object by its name alone. |
| `pg_is_other_temp_schema(schema)`, `pg_my_temp_schema()` | Whether a schema is the temporary schema of another session, and the one of this session. |
| `has_table_privilege([role,] table, privilege)`, `has_any_column_privilege(...)`, `has_column_privilege(...)`, `has_database_privilege(...)`, `has_foreign_data_wrapper_privilege(...)`, `has_function_privilege(...)`, `has_language_privilege(...)`, `has_schema_privilege(...)`, `has_sequence_privilege(...)`, `has_server_privilege(...)`, `has_tablespace_privilege(...)`, `has_type_privilege(...)`, `pg_has_role([role,] role, privilege)` | Whether the role has the privilege. |
| `acldefault(type, owner)`, `makeaclitem(grantee, grantor, privileges, grantable)` | The default privileges of an object type, and one privilege item. |
| `to_regclass(text)`, `to_regcollation(text)`, `to_regnamespace(text)`, `to_regoper(text)`, `to_regoperator(text)`, `to_regproc(text)`, `to_regprocedure(text)`, `to_regrole(text)`, `to_regtype(text)` | The object of a name, or NULL when there is none. |
| `pg_filenode_relation(tablespace, filenode)`, `pg_relation_filenode(relation)` | The relation of a file, and the file of a relation. |
| `pg_partition_ancestors(table)`, `pg_partition_root(table)`, `pg_relation_is_publishable(table)` | The parents of a partition, its root, and whether a publication can hold the table. |
| `pg_partition_tree(table)` | Rows: the partitions of a table, with their parents and levels. |
| `pg_relation_is_updatable(relation, include_triggers)`, `pg_column_is_updatable(relation, column, include_triggers)` | The events that a relation takes (a bit mask), and whether a column can be updated. |
| `satisfies_hash_partition(parent, modulus, remainder, value, ...)` | Whether the values go to the hash partition of that modulus and remainder. |
| `pg_index_has_property(index, property)`, `pg_index_column_has_property(index, column, property)`, `pg_indexam_has_property(method, property)`, `amvalidate(operator_class)` | The properties of an index, of its column and of its access method, and the check of an operator class. |
| `pg_tablespace_location(tablespace)` | The folder of a tablespace. |
| `pg_char_to_encoding(name)`, `pg_encoding_to_char(code)` | The code of an encoding name, and back. |
| `brin_summarize_range(index, block)`, `brin_summarize_new_values(index)`, `brin_desummarize_range(index, block)` | The maintenance functions of BRIN indexes. |

## Sizes

| Function | Result |
| --- | --- |
| `pg_relation_size(relation [, fork])`, `pg_table_size(table)`, `pg_indexes_size(table)`, `pg_total_relation_size(table)` | The bytes of a relation, of a table without indexes, of its indexes, and of all of them. |
| `pg_database_size(database)` | The bytes of a database. |
| `pg_size_pretty(bytes)`, `pg_size_bytes(text)` | Bytes as `kB`, `MB` or `GB` text, and back. |

## Statistics

The counters are the ones of [pg_stat_user_tables](../operations/gizdb-server.md). The calls of a function are counted;
their times are 0.

| Function | Result |
| --- | --- |
| `pg_stat_get_numscans(table)`, `pg_stat_get_tuples_inserted(table)`, `pg_stat_get_tuples_updated(table)`, `pg_stat_get_tuples_deleted(table)`, `pg_stat_get_tuples_hot_updated(table)`, `pg_stat_get_live_tuples(table)`, `pg_stat_get_dead_tuples(table)` | The counters of a table. |
| `pg_stat_get_xact_numscans(table)`, `pg_stat_get_xact_tuples_inserted(table)`, `pg_stat_get_xact_tuples_updated(table)`, `pg_stat_get_xact_tuples_deleted(table)`, `pg_stat_get_xact_tuples_hot_updated(table)` | The counters of the open transaction. |
| `pg_stat_get_function_calls(function)`, `pg_stat_get_function_total_time(function)`, `pg_stat_get_function_self_time(function)`, `pg_stat_get_xact_function_calls(function)`, `pg_stat_get_xact_function_total_time(function)`, `pg_stat_get_xact_function_self_time(function)` | The calls of a function, and their times. |
| `pg_stat_get_snapshot_timestamp()`, `pg_stat_have_stats(kind, database, object)` | When the statistics were read, and whether an object has them. |
| `pg_stat_reset()`, `pg_stat_reset_shared(target)`, `pg_stat_reset_slru(name)`, `pg_stat_reset_subscription_stats(subscription)`, `pg_stat_reset_single_table_counters(table)`, `pg_stat_reset_single_function_counters(function)`, `pg_stat_reset_replication_slot(slot)` | Sets counters to 0. |
| `pg_stat_clear_snapshot()`, `pg_stat_force_next_flush()` | Reads the statistics again at the next use, and sends the counts of the session at once. |

## Large objects

| Function | Result |
| --- | --- |
| `lo_create(oid)`, `lo_creat(mode)`, `lo_import(path [, oid])` | A new large object. |
| `lo_get(oid [, offset, length])` | The bytes of a large object. |
| `lo_from_bytea(oid, bytes)`, `lo_put(oid, offset, bytes)` | A new large object with the bytes, and bytes written into one. |
| `lo_open(oid, mode)`, `loread(descriptor, length)`, `lowrite(descriptor, bytes)` | Opens a large object, and reads or writes at the position. |
| `lo_lseek(descriptor, offset, whence)`, `lo_truncate(descriptor, length)`, `lo_truncate64(descriptor, length)` | Moves the position, and cuts the object to a length. |
| `lo_export(oid, path)` | Writes a large object to a file. |
| `lo_tell(descriptor)`, `lo_tell64(descriptor)`, `lo_close(descriptor)`, `lo_unlink(oid)` | The position in an open object, its close, and the removal of an object. |

## Oracle packages

| Function | Result |
| --- | --- |
| `dbms_assert.enquote_literal(text)`, `dbms_assert.enquote_name(text [, capitalize])`, `dbms_assert.simple_sql_name(text)` | The text quoted as a literal or a name, and the check of a simple name. Oracle. |
| `dbms_lob.getlength(lob)`, `dbms_lob.instr(lob, pattern [, from [, nth]])`, `dbms_lob.substr(lob [, count [, from]])` | The length of a large value, a position in it, and part of it. Oracle. |
| `dbms_random.value([low, high])`, `dbms_random.random()`, `dbms_random.string(kind, length)` | A random number from 0 up to 1 (or `low` up to `high`), a random integer, and a random text. Oracle. |
| `dbms_utility.format_error_stack()`, `dbms_utility.format_error_backtrace()`, `dbms_utility.get_time()` | The error being handled, where it came from, and a time in hundredths of a second. Oracle. |
| `dbms_db_version.version()` | The major version. Oracle. |
| `utl_encode.base64_encode(raw)`, `utl_encode.base64_decode(raw)` | Bytes as Base64, and back. Oracle. |
| `utl_raw.cast_to_raw(text)`, `utl_raw.cast_to_varchar2(raw)`, `utl_raw.length(raw)` | Text as bytes, bytes as text, and the length in bytes. Oracle. |

## Key-value rows

The rows of the key-value interface ([Use the Redis protocol](../guides/data.md#use-the-redis-protocol)) are keyed by an
identity of the tenant, the database and the key. GizDB.

| Function | Result |
| --- | --- |
| `gizdb_kv_identity(tenant, database, key)` | The identity of a key, to find its row. |
| `gizdb_kv_tenant(identity)`, `gizdb_kv_db(identity)`, `gizdb_kv_key(identity)` | One part of an identity, or NULL for another value. |
| `gizdb_kv_shard(identity, shards)` | The shard of the key. |

## Aggregate functions

An aggregate takes `DISTINCT`, `ORDER BY` and `FILTER (WHERE ...)`, and is a window function with `OVER`.

| Function | Result |
| --- | --- |
| `count(*)`, `count(value)`, `count_big(value)` | The rows, or the values that are not NULL. `count_big` is SQL Server's. |
| `count_if(condition)` | The rows where the condition is true. StarRocks. |
| `sum(x)`, `total(x)`, `avg(x)` | The sum and the mean. `total` (SQLite) is 0.0 when there are no rows. |
| `min(x)`, `max(x)` | The smallest and the largest value. With two or more arguments they are `least` and `greatest`. |
| `min_by(value, key)`, `max_by(value, key)` | The value of the row with the smallest or the largest key. StarRocks. |
| `any_value(x)`, `arbitrary(x)` | One of the values. `arbitrary` is StarRocks'. |
| `every(condition)`, `bool_and(condition)`, `bool_or(condition)` | Whether all, or any, conditions are true. |
| `bit_and(x)`, `bit_or(x)`, `bit_xor(x)` | The bits of all values combined. |
| `array_agg(value)` | The values as an array. |
| `string_agg(text, separator)`, `listagg(text [, separator])`, `group_concat(text [, separator])` | The values joined. Oracle; MySQL, SQLite. |
| `json_agg(value)`, `jsonb_agg(value)`, `json_arrayagg(value)`, `json_group_array(value)` | The values as a JSON array. `json_group_array` is SQLite's. |
| `json_object_agg(key, value)`, `jsonb_object_agg(key, value)`, `json_objectagg(key: value)`, `json_group_object(key, value)` | The pairs as a JSON object. `json_group_object` is SQLite's. |
| `xmlagg(xml)` | The XML values joined. |
| `range_agg(range)`, `range_intersect_agg(range)` | The union of the ranges as a multirange, and their intersection. |
| `stddev(x)`, `stddev_samp(x)`, `stddev_pop(x)`, `stdev(x)`, `stdevp(x)` | The standard deviation of a sample or of the population. `stdev` and `stdevp` are SQL Server's. |
| `variance(x)`, `var_samp(x)`, `var_pop(x)`, `var(x)`, `varp(x)` | The variance. `var` and `varp` are SQL Server's. |
| `corr(y, x)`, `covar_samp(y, x)`, `covar_pop(y, x)` | The correlation and the covariance. |
| `regr_slope(y, x)`, `regr_intercept(y, x)`, `regr_r2(y, x)`, `regr_count(y, x)`, `regr_avgx(y, x)`, `regr_avgy(y, x)`, `regr_sxx(y, x)`, `regr_syy(y, x)`, `regr_sxy(y, x)` | The linear regression of `y` on `x`. |
| `percentile_cont(fraction) WITHIN GROUP (ORDER BY x)`, `percentile_disc(fraction) WITHIN GROUP (ORDER BY x)` | The value at the fraction, interpolated or taken from a row. |
| `mode() WITHIN GROUP (ORDER BY x)` | The most frequent value. |
| `median(x)` | The middle value. Oracle. |
| `percentile_approx(x, fraction)`, `approx_count_distinct(x)` | The value at the fraction, and the count of distinct values. StarRocks. |
| `ols(y, x, ...)`, `ols_summary(y, x, ...)` | The coefficients of the least squares fit of `y` on the `x` values, and the fit with its statistics. Refer to [OLS](../internals/database.md#ols). GizDB. |
| `metric_histogram_merge(histogram)` | The histograms merged. GizDB. |
| `pgml.sum(array)`, `pgml.min(array)`, `pgml.max(array)`, `pgml.min_abs(array)`, `pgml.max_abs(array)` | The arrays combined element by element. PostgresML. |

## Window functions

A window function needs `OVER`.

| Function | Result |
| --- | --- |
| `row_number()` | The number of the row in its partition, from 1. |
| `rank()`, `dense_rank()` | The rank of the row, with gaps, or without gaps. |
| `percent_rank()`, `cume_dist()` | The relative rank, and the part of the rows before or tied with the row. |
| `ntile(n)` | The bucket of the row among `n` buckets. |
| `lag(value [, offset [, default]])`, `lead(value [, offset [, default]])` | The value of a row before or after. |
| `first_value(value)`, `last_value(value)`, `nth_value(value, n)` | The value of the first, the last or the `n`-th row of the frame. |

## Rows

Each other function that returns rows is under its subject, marked "Rows".

| Function | Result |
| --- | --- |
| `generate_series(start, stop [, step])` | Rows: the numbers or the times from `start` to `stop`. |
| `pg_options_to_table(options)` | Rows: the name and the value of each `name=value` option. |
| `changes(table, after [, wait_ms [, column, value]])` | Rows: the committed changes of a table after a position ([Live queries](../guides/data.md#live-queries)). GizDB. |
| `read_csv(path [, option => value ...])`, `read_csv_auto(path ...)` | Rows: the CSV files of a path or a glob under the server's files directory ([Query files](../guides/data.md#query-files)). |
| `read_parquet(path ...)`, `read_json(path ...)`, `read_json_auto(path ...)`, `read_ndjson(path ...)`, `read_file(path ...)` | Rows: the Parquet files, the files of JSON lines, or files of the format of their extension. |

## Support functions

PostgreSQL's catalogs name these functions: they are the operators, the aggregate steps and the hash functions of its
types. A program calls the operators instead.

| Function | Result |
| --- | --- |
| `booleq(a, b)`, `boolne(a, b)` | `=` and `<>` of booleans. |
| `int2larger(a, b)`, `int2smaller(a, b)`, `int4larger(a, b)`, `int4smaller(a, b)`, `int8larger(a, b)`, `int8smaller(a, b)`, `float4larger(a, b)`, `float4smaller(a, b)`, `float8larger(a, b)`, `float8smaller(a, b)`, `numeric_larger(a, b)`, `numeric_smaller(a, b)`, `cashlarger(a, b)`, `cashsmaller(a, b)`, `text_larger(a, b)`, `text_smaller(a, b)`, `array_larger(a, b)`, `array_smaller(a, b)` | The larger or the smaller value: the steps of `max` and `min`. |
| `numeric_inc(x)` | `x + 1`. |
| `textcat(a, b)` | `a \|\| b`. |
| `range_contains(a, b)`, `range_contains_elem(range, value)`, `range_contains_multirange(range, multirange)`, `multirange_contains_elem(multirange, value)`, `multirange_contains_range(multirange, range)`, `multirange_contains_multirange(a, b)` | `@>`. |
| `range_contained_by(a, b)`, `elem_contained_by_range(value, range)`, `elem_contained_by_multirange(value, multirange)`, `range_contained_by_multirange(range, multirange)`, `multirange_contained_by_range(multirange, range)`, `multirange_contained_by_multirange(a, b)` | `<@`. |
| `range_overlaps(a, b)`, `range_overlaps_multirange(...)`, `multirange_overlaps_range(...)`, `multirange_overlaps_multirange(...)` | `&&`. |
| `range_adjacent(a, b)`, `range_adjacent_multirange(...)`, `multirange_adjacent_range(...)`, `multirange_adjacent_multirange(...)` | `-\|-`. |
| `range_before(a, b)`, `range_before_multirange(...)`, `multirange_before_range(...)`, `multirange_before_multirange(...)` | `<<`. |
| `range_after(a, b)`, `range_after_multirange(...)`, `multirange_after_range(...)`, `multirange_after_multirange(...)` | `>>`. |
| `range_overleft(a, b)`, `range_overleft_multirange(...)`, `multirange_overleft_range(...)`, `multirange_overleft_multirange(...)` | `&<`. |
| `range_overright(a, b)`, `range_overright_multirange(...)`, `multirange_overright_range(...)`, `multirange_overright_multirange(...)` | `&>`. |
| `range_union(a, b)`, `multirange_union(a, b)`, `range_intersect(a, b)`, `multirange_intersect(a, b)`, `range_minus(a, b)`, `multirange_minus(a, b)` | `+`, `*` and `-`. |
| `booland_statefunc(state, value)`, `boolor_statefunc(state, value)`, `float8_accum(state, x)`, `float8_combine(a, b)`, `float8_regr_accum(state, y, x)`, `float8_regr_combine(a, b)` | Steps of aggregates. |
| `float4send(x)`, `float8send(x)` | The binary form of a float. |
| `tsvectorin(text)`, `tsvectorout(tsvector)`, `tsqueryin(text)`, `tsqueryout(tsquery)` | The text forms of the text search types. |
| `hashint2(x [, seed])`, `hashint4(...)`, `hashint8(...)`, `hashfloat4(...)`, `hashfloat8(...)`, `hash_numeric(...)`, `hashtext(...)`, `hashname(...)`, `hashchar(...)`, `hashbpchar(...)`, `hashvarlena(...)`, `bytea_hash(...)`, `hashoid(...)`, `hashoidvector(...)`, `hashenum(...)`, `hashinet(...)`, `hashmacaddr(...)`, `hashmacaddr8(...)`, `hash_aclitem(...)`, `hash_array(...)`, `hash_range(...)`, `hash_multirange(...)`, `hash_record(...)`, `jsonb_hash(...)`, `pg_lsn_hash(...)`, `uuid_hash(...)`, `interval_hash(x)`, `time_hash(...)`, `timetz_hash(...)`, `timestamp_hash(...)` | The hash of a value, as the hash indexes and hash partitions of PostgreSQL compute it. |

SQL functions

This page lists every function that GizDB runs by name. A function that has the name of a PostgreSQL 16 function works as that function does. A label after a description names the database whose function it follows otherwise: MySQL, SQLite, Oracle, SQL Server, StarRocks, pgvector, TimescaleDB, or GizDB for its own functions. Brackets show optional arguments, and ... shows that more arguments of the same kind can follow. "Rows" marks a function that returns rows (use it in FROM).

The SQL tests (tests/db/functions) fail when GizDB runs a function that this page does not list, or when this page lists a function that GizDB does not run. A type name also casts as a function (int4('7'), date('2026-10-22')); the types are in Dialects.

Conditions and comparisons

Function	Result
`coalesce(value, ...)`	The first argument that is not NULL.
`nullif(a, b)`	NULL when `a` equals `b`, else `a`.
`greatest(value, ...)`, `least(value, ...)`	The largest or the smallest argument. NULL arguments are left out.
`ifnull(value, other)`	`other` when `value` is NULL. MySQL, SQLite.
`nvl(value, other)`	`other` when `value` is NULL. Oracle.
`nvl2(value, if_not_null, if_null)`	The second argument when `value` is not NULL, else the third. Oracle.
`isnull(value)`, `isnull(value, other)`	With one argument, whether `value` is NULL (MySQL). With two, `ifnull` (SQL Server).
`if(condition, then, otherwise)`, `iif(condition, then, otherwise)`	`then` when `condition` is true, else `otherwise`. MySQL; SQL Server, SQLite.
`decode(value, search, result [, search, result ...] [, default])`	The result of the first `search` that equals `value`; NULL equals NULL. Oracle. With two text arguments it is PostgreSQL's `decode(text, format)` (Binary data and hashes).
`lnnvl(condition)`	True when `condition` is false or unknown. Oracle.
`nanvl(x, other)`	`other` when `x` is NaN. Oracle.
`zeroifnull(x)`	0 when `x` is NULL. Snowflake.
`num_nulls(value, ...)`, `num_nonnulls(value, ...)`	How many arguments are NULL, or are not NULL.
`field(value, first, ...)`	The position of `value` in the list from 1, or 0. MySQL.
`row(value, ...)`	A row of the values.

Numbers

Function	Result
`abs(x)`, `sign(x)`	The absolute value; -1, 0 or 1.
`ceil(x)`, `ceiling(x)`, `floor(x)`	The nearest integer above or below.
`round(x [, digits])`, `trunc(x [, digits])`	`x` rounded, or cut, to `digits` decimal places (0 when left out).
`truncate(x, digits)`	`trunc`. MySQL.
`div(a, b)`, `mod(a, b)`	The integer quotient and the remainder of `a / b`.
`pmod(a, b)`	The remainder of `a / b` that is not negative. StarRocks.
`remainder(a, b)`	`a - b * round(a / b)`. Oracle.
`gcd(a, b)`, `lcm(a, b)`	The greatest common divisor and the least common multiple.
`factorial(n)`	`n!`.
`sqrt(x)`, `cbrt(x)`	The square root and the cube root.
`exp(x)`, `ln(x)`, `log10(x)`, `log2(x)`	e to the power `x`, and the logarithms.
`log(x)`, `log(base, x)`	The base-10 logarithm, or the logarithm in `base`.
`pow(x, y)`, `power(x, y)`	`x` to the power `y`.
`pi()`	π.
`degrees(radians)`, `radians(degrees)`	The angle in the other unit.
`sin(x)`, `cos(x)`, `tan(x)`, `cot(x)`	The trigonometric functions of radians.
`asin(x)`, `acos(x)`, `atan(x)`, `atan2(y, x)`	The inverses, in radians.
`atn2(y, x)`	`atan2`. SQL Server.
`sind(x)`, `cosd(x)`, `tand(x)`, `cotd(x)`, `asind(x)`, `acosd(x)`, `atand(x)`, `atan2d(y, x)`	The same in degrees.
`sinh(x)`, `cosh(x)`, `tanh(x)`, `asinh(x)`, `acosh(x)`, `atanh(x)`	The hyperbolic functions and their inverses.
`erf(x)`, `erfc(x)`	The error function and its complement.
`scale(x)`, `min_scale(x)`, `trim_scale(x)`	The decimal places of a numeric, the fewest that keep its value, and the value with only those.
`width_bucket(x, low, high, count)`, `width_bucket(x, bounds)`	The bucket of `x` among `count` equal buckets, or among the bounds of an array.
`random([seed])`, `rand([seed])`	A number from 0 up to 1. `rand` is MySQL's.
`random_normal([mean [, deviation]])`	A number from the normal distribution.
`bitand(a, b)`	The bits that both integers have. Oracle.
`bit_count(x)`	The bits set in a bit string or bytes.
`cash_words(money)`	A money value in English words.
`binary_double_infinity()`, `binary_double_nan()`, `binary_float_infinity()`, `binary_float_nan()`	Infinity and NaN of the floating types. Oracle.

Text

Function	Result
`length(text)`, `char_length(text)`, `character_length(text)`	The characters of the text.
`len(text)`	The characters without the trailing spaces. SQL Server.
`lengthc(text)`, `lengthb(text)`	The characters, or the bytes. Oracle.
`octet_length(text)`, `vsize(value)`	The bytes; `vsize` is Oracle's.
`lower(text)`, `upper(text)`, `initcap(text)`	The text in lower case, in upper case, or with each word capitalized.
`lcase(text)`, `ucase(text)`	`lower` and `upper`. MySQL.
`nls_lower(text [, settings])`, `nls_upper(text [, settings])`, `nls_initcap(text [, settings])`	The same with Oracle's language settings. Oracle.
`concat(value, ...)`, `concat_ws(separator, value, ...)`	The values joined; NULLs are left out.
`format(pattern, value, ...)`	The pattern with `%s`, `%I` and `%L` filled.
`printf(pattern, value, ...)`	The pattern with C's `%` codes filled. SQLite.
`left(text, n)`, `right(text, n)`	The first or the last `n` characters.
`substring(text, from [, count])`, `substr(text, from [, count])`	Part of the text, from character `from` (1 is the first). Also `substring(text FROM from FOR count)`.
`mid(text, from [, count])`	`substring`. MySQL.
`substrb(text, from [, count])`	Part of the text, counted in bytes. Oracle.
`substring_index(text, separator, count)`	The text before the `count`-th separator; a negative count counts from the end. MySQL.
`split_part(text, separator, n)`	The `n`-th field.
`position(part, text)`, `strpos(text, part)`	Where `part` starts in the text, or 0. Also `position(part IN text)`.
`instr(text, part [, from [, nth]])`, `instrb(text, part [, from [, nth]])`	Where the `nth` `part` starts, in characters or bytes. Oracle; MySQL has `instr`.
`charindex(part, text [, from])`	Where `part` starts. SQL Server.
`locate(part, text [, from])`	Where `part` starts. MySQL.
`starts_with(text, prefix)`, `ends_with(text, suffix)`	Whether the text starts or ends with the part.
`lpad(text, length [, fill])`, `rpad(text, length [, fill])`	The text filled to `length` on the left or the right.
`trim(text)`, `btrim(text [, characters])`	The text without the characters (spaces) at both ends. Also `trim([BOTH \| LEADING \| TRAILING] [characters] FROM text)`.
`ltrim(text [, characters])`, `rtrim(text [, characters])`	The same at the start or the end. They also trim bytes.
`repeat(text, n)`, `replicate(text, n)`	The text `n` times; `replicate` is SQL Server's.
`space(n)`	`n` spaces. MySQL, SQL Server.
`replace(text, from, to)`	Each `from` replaced by `to`.
`translate(text, from, to)`	Each character of `from` replaced by the character at the same place in `to`.
`reverse(text)`	The characters in the opposite order.
`ascii(text)`, `unicode(text)`	The code of the first character. `unicode` is SQL Server's and SQLite's.
`chr(code)`	The character of the code.
`char(code, ...)`, `nchar(code, ...)`	The characters of the codes. MySQL; SQL Server.
`asciistr(text)`	The text with each character that is not ASCII written as `\XXXX`. Oracle.
`unistr(text)`	The text with each `\XXXX` escape decoded.
`normalize(text [, form])`, `is_normalized(text [, form])`	The text in Unicode form NFC, NFD, NFKC or NFKD, and whether it is in it.
`quote_ident(text)`, `quote_literal(value)`, `quote_nullable(value)`	The text as an identifier or as a literal for SQL; `quote_nullable` gives `NULL` for NULL.
`quote(value)`	The value as an SQL literal. SQLite.
`parse_ident(text [, strict])`	The parts of a qualified identifier, as an array.
`soundex(text)`	The Soundex code.
`md5(text)`	The MD5 hash, in hexadecimal.
`to_hex(n)`	The integer in hexadecimal.
`to_char(value [, format])`, `to_nchar(value [, format])`	A number or a time as text, in the format. `to_nchar` is Oracle's.
`to_number(text [, format])`	The text as a number, read in the format.
`like(pattern, text [, escape])`, `glob(pattern, text)`	`text LIKE pattern` and `text GLOB pattern`. SQLite.
`similar_to_escape(pattern [, escape])`	The regular expression of a `SIMILAR TO` pattern.
`dump(value [, format])`	The type, the length and the bytes of a value. Oracle.
`to_clob(value)`, `to_nclob(value)`, `to_blob(value)`, `empty_clob()`, `empty_blob()`	Large text and bytes values. Oracle.

Regular expressions

The patterns are POSIX regular expressions, as in PostgreSQL. flags are letters such as i (ignore case) and g (all matches).

Function	Result
`regexp_like(text, pattern [, flags])`	Whether the pattern matches.
`regexp(text, pattern [, flags])`	`regexp_like`. MySQL, SQLite.
`regexp_count(text, pattern [, from [, flags]])`	How many times the pattern matches.
`regexp_instr(text, pattern [, from [, nth [, end [, flags [, group]]]]])`	Where a match starts (or ends).
`regexp_substr(text, pattern [, from [, nth [, flags [, group]]]])`	The text of a match.
`regexp_match(text, pattern [, flags])`	The groups of the first match, as an array.
`regexp_matches(text, pattern [, flags])`	Rows: the groups of each match.
`regexp_replace(text, pattern, replacement [, from [, nth]] [, flags])`	The text with the matches replaced.
`regexp_split_to_array(text, pattern [, flags])`	The parts between the matches, as an array.
`regexp_split_to_table(text, pattern [, flags])`	Rows: the parts between the matches.

Binary data and hashes

Function	Result
`encode(bytes, format)`, `decode(text, format)`	Bytes as `base64`, `escape` or `hex` text, and back.
`get_bit(bytes, n)`, `get_byte(bytes, n)`	One bit or byte, counted from 0.
`set_bit(bytes, n, bit)`, `set_byte(bytes, n, byte)`	The bytes with one bit or byte changed.
`hex(value)`	The bytes or the number in hexadecimal. MySQL, SQLite.
`hextoraw(text)`, `rawtohex(bytes)`	Hexadecimal text as bytes, and back. Oracle.
`sha224(bytes)`, `sha256(bytes)`, `sha384(bytes)`, `sha512(bytes)`	The SHA-2 hash.
`digest(data, algorithm)`	The hash of `md5`, `sha1`, `sha224`, `sha256`, `sha384` or `sha512`. pgcrypto.
`standard_hash(value [, algorithm])`	The SHA-1 hash, or the hash of `algorithm`. Oracle.
`ora_hash(value [, buckets [, seed]])`	A bucket number from the hash of the value. Oracle.
`crc32(value)`	The CRC-32 of the value. MySQL.

Dates and times

Function	Result
`now()`, `current_timestamp`, `transaction_timestamp()`	The time when the transaction started.
`statement_timestamp()`, `clock_timestamp()`	The time when the statement started, and the time now.
`current_date`, `current_time`, `localtime`, `localtimestamp`	The date, the time with its zone, and the local time and timestamp.
`getdate()`, `sysdatetime()`, `getutcdate()`	The local time, and the UTC time. SQL Server.
`sysdate()`, `systimestamp()`	The time now. Oracle.
`curdate()`, `curtime()`, `utc_timestamp()`	The date, the time and the UTC timestamp. MySQL.
`today()`	The date.
`dbtimezone()`, `sessiontimezone()`	The time zone of the database and of the session. Oracle.
`make_date(year, month, day)`, `make_time(hour, minute, second)`	A date, or a time.
`make_timestamp(year, month, day, hour, minute, second)`, `make_timestamptz(..., [zone])`	A timestamp, without or with a time zone.
`make_interval([years [, months [, weeks [, days [, hours [, minutes [, seconds]]]]]]])`	An interval.
`datefromparts(year, month, day)`	A date. SQL Server.
`to_date(text [, format])`, `to_timestamp(text [, format])`, `to_timestamp(seconds)`	The text read in the format, or the Unix time as a timestamp.
`to_timestamp_tz(text [, format])`, `from_tz(timestamp, zone)`	A timestamp with a time zone. Oracle.
`str_to_date(text [, format])`	The text read in a MySQL format. MySQL.
`numtodsinterval(n, unit)`, `numtoyminterval(n, unit)`	An interval of days to seconds, or of years and months. Oracle.
`date(time, modifier ...)`, `time(time, modifier ...)`, `datetime(time, modifier ...)`, `julianday(time, ...)`, `unixepoch(time, ...)`, `strftime(format, time, ...)`, `timediff(a, b)`	SQLite's date and time functions. SQLite.
`date_part(field, source)`, `datepart(field, source)`	One field of a date, a time or an interval, such as `year` or `epoch`. Also `extract(field FROM source)`. `datepart` is SQL Server's.
`year(t)`, `quarter(t)`, `month(t)`, `week(t)`, `day(t)`, `hour(t)`, `minute(t)`, `second(t)`	One field. MySQL.
`dayofweek(t)`, `dayofyear(t)`, `weekday(t)`	The day in the week (1 is Sunday), in the year, and in the week (0 is Monday). MySQL.
`date_format(t [, format])`	The time as text, in a MySQL format. MySQL.
`unix_timestamp([t])`	The seconds since 1970-01-01 UTC. MySQL.
`isfinite(value)`	Whether a date, a timestamp or an interval is finite.
`age(a [, b])`	The interval from `b` (midnight today when left out) to `a`, in years, months and days.
`date_add(t, interval [, zone])`, `date_subtract(t, interval [, zone])`	`t` plus or minus the interval.
`adddate(t, n)`, `subdate(t, n)`, `date_sub(t, interval)`	`t` plus or minus days or an interval. MySQL.
`dateadd(unit, n, t)`	`t` plus `n` units. SQL Server.
`timestampadd(unit, n, t)`, `timestampdiff(unit, a, b)`	`t` plus `n` units, and the units from `a` to `b`. MySQL.
`datediff(unit, a, b)`, `datediff(a, b)`, `date_diff(unit, a, b)`	The units, or the days, between two times. SQL Server; MySQL.
`add_months(t, n)`, `months_between(a, b)`	`t` plus `n` months, and the months between two dates. Oracle.
`last_day(t)`, `eomonth(t)`	The last day of the month. Oracle, MySQL; SQL Server.
`next_day(t, weekday)`	The first date after `t` that is that day of the week. Oracle.
`date_trunc(unit, t [, zone])`, `datetrunc(unit, t)`	`t` cut down to the start of its unit. `datetrunc` is SQL Server's.
`date_bin(stride, t, origin)`	`t` cut down to the start of its stride, counted from `origin`.
`time_bucket(width, t [, origin])`	The start of the bucket of `t`. Buckets are counted from 2000-01-03, a Monday; a width of months counts calendar months. TimescaleDB.
`justify_days(interval)`, `justify_hours(interval)`, `justify_interval(interval)`	The interval with 30 days as a month, 24 hours as a day, or both.
`timezone(zone, t)`	`t AT TIME ZONE zone`.
`sys_extract_utc(t)`, `tz_offset(zone)`	The UTC time of a timestamp with a zone, and the offset of a zone. Oracle.

JSON

Function	Result
`to_json(value)`, `to_jsonb(value)`, `array_to_json(array [, pretty])`	The value as JSON.
`json(value)`	The value as JSON text. SQLite.
`json_build_array(value, ...)`, `jsonb_build_array(value, ...)`, `json_array(value, ...)`	A JSON array of the values.
`json_build_object(key, value, ...)`, `jsonb_build_object(key, value, ...)`	A JSON object of the pairs.
`json_object(keys [, values])`, `jsonb_object(keys [, values])`	A JSON object from text arrays.
`json_quote(text)`	The text as a JSON string. SQLite, MySQL.
`parse_json(text)`, `json_parse(text)`	The text read as JSON. StarRocks.
`json_serialize(json)`	The JSON as text.
`json_extract_path(json, key, ...)`, `jsonb_extract_path(json, key, ...)`	The value at the path. `#>` is the operator.
`json_extract_path_text(json, key, ...)`, `jsonb_extract_path_text(json, key, ...)`	The value at the path, as text. `#>>` is the operator.
`json_extract(json, path, ...)`, `jsonb_extract(json, path, ...)`	The value at a `$.a.b` path. SQLite, MySQL.
`get_json_string(json, path)`, `get_json_int(json, path)`, `get_json_double(json, path)`	The value at a `$.a.b` path, as text, an integer or a float. StarRocks.
`json_value(json, path ...)`, `json_query(json, path ...)`	The scalar, or the object or array, at an SQL/JSON path.
`json_array_length(json)`, `jsonb_array_length(json)`	The elements of a JSON array.
`json_typeof(json)`, `jsonb_typeof(json)`	`object`, `array`, `string`, `number`, `boolean` or `null`.
`json_type(json [, path])`	The type at the path. SQLite, MySQL.
`json_valid(text)`, `isjson(text)`	Whether the text is JSON. SQLite, MySQL; SQL Server.
`jsonb_pretty(json)`	The JSON as indented text.
`json_strip_nulls(json)`, `jsonb_strip_nulls(json)`	The JSON without object fields that are null.
`json_exists(json, path ...)`	Whether an SQL/JSON path finds an item.
`json_populate_record(base, json)`, `jsonb_populate_record(base, json)`	The row type of `base` with the fields of a JSON object.
`json_to_record(json)`, `jsonb_to_record(json)`	A row of a JSON object; `AS t(column type, ...)` names its columns.
`json_populate_recordset(base, json)`, `jsonb_populate_recordset(base, json)`, `json_to_recordset(json)`, `jsonb_to_recordset(json)`	Rows: the same for each object of a JSON array.
`jsonb_set(json, path, value [, create])`, `jsonb_set_lax(json, path, value [, create [, null_treatment]])`	The JSON with the value at the path replaced.
`jsonb_insert(json, path, value [, after])`	The JSON with the value inserted at the path.
`json_set(json, path, value, ...)`, `json_insert(json, path, value, ...)`, `json_replace(json, path, value, ...)`, `json_remove(json, path, ...)`	The JSON with the values at `$.a.b` paths set, added, replaced or removed. SQLite, MySQL.
`json_modify(json, path, value)`	The JSON with the value at the path set. SQL Server.
`jsonb_concat(a, b)`, `jsonb_contains(a, b)`, `jsonb_contained(a, b)`	The functions of `\|\|`, `@>` and `<@`.
`jsonb_delete(json, key)`, `jsonb_delete_array(json, keys)`, `jsonb_delete_path(json, path)`	The functions of `-` and `#-`.
`jsonb_exists(json, key)`, `jsonb_exists_any(json, keys)`, `jsonb_exists_all(json, keys)`	The functions of `?`, `?\|` and `?&`.
`jsonb_path_exists(json, path [, vars [, silent]])`, `jsonb_path_match(...)`, `jsonb_path_query_array(...)`, `jsonb_path_query_first(...)`	Whether the SQL/JSON path finds an item, the result of a path predicate, all items as an array, and the first item.
`jsonb_path_exists_tz(...)`, `jsonb_path_match_tz(...)`, `jsonb_path_query_array_tz(...)`, `jsonb_path_query_first_tz(...)`	The same, with time zones in date and time items.
`jsonb_path_query(json, path [, vars [, silent]])`, `jsonb_path_query_tz(...)`	Rows: each item that the path finds.
`json_array_elements(json)`, `jsonb_array_elements(json)`, `json_array_elements_text(json)`, `jsonb_array_elements_text(json)`	Rows: each element of a JSON array, as JSON or as text.
`json_each(json)`, `jsonb_each(json)`, `json_each_text(json)`, `jsonb_each_text(json)`	Rows: each key and value of a JSON object.
`json_object_keys(json)`, `jsonb_object_keys(json)`	Rows: each key of a JSON object.

Arrays

Function	Result
`array_append(array, value)`, `array_prepend(value, array)`, `array_cat(a, b)`	The array with a value at the end or the start, and two arrays joined.
`array_length(array [, dimension])`, `cardinality(array)`	The elements of a dimension, and of all dimensions.
`array_lower(array [, dimension])`, `array_upper(array [, dimension])`, `array_ndims(array)`, `array_dims(array)`	The bounds, the number of dimensions, and the dimensions as text.
`array_fill(value, lengths [, lower_bounds])`	An array of the value.
`array_position(array, value [, from])`, `array_positions(array, value)`	The first place of the value, and all places.
`array_remove(array, value)`, `array_replace(array, from, to)`	The array without the value, and with each `from` replaced.
`array_sample(array, n)`, `array_shuffle(array)`	`n` random elements, and the elements in random order.
`array_to_string(array, separator [, null_text])`, `string_to_array(text, separator [, null_text])`	The elements joined as text, and text split into an array.
`string_to_table(text, separator [, null_text])`	Rows: the parts of the text.
`trim_array(array, n)`	The array without its last `n` elements.
`generate_subscripts(array, dimension [, reverse])`	Rows: the subscripts of a dimension.
`unnest(array)`	Rows: the elements.

Ranges

Function	Result
`int4range(low, high [, bounds])`, `int8range(...)`, `numrange(...)`, `daterange(...)`, `tsrange(...)`, `tstzrange(...)`	A range. `bounds` is `'[)'` when left out.
`multirange(range)`	A multirange of one range.
`isempty(range)`	Whether the range is empty.
`lower_inc(range)`, `upper_inc(range)`, `lower_inf(range)`, `upper_inf(range)`	Whether a bound is in the range, and whether it is infinite. `lower` and `upper` give the bounds.
`range_merge(a, b)`	The smallest range that holds both.

Network addresses

Function	Result
`host(inet)`, `abbrev(inet)`, `family(inet)`, `masklen(inet)`	The address as text, the short text form, 4 or 6, and the length of the mask.
`netmask(inet)`, `hostmask(inet)`, `network(inet)`, `broadcast(inet)`	The masks, the network and the broadcast address.
`macaddr8_set7bit(macaddr8)`	The address with its 7th bit set.
`set_masklen(inet, length)`	The address with another mask length.
`inet_same_family(a, b)`, `inet_merge(a, b)`	Whether both addresses are IPv4 or both IPv6, and the smallest network that holds both.

Text search

Text search is the one of PostgreSQL: tsvector documents and tsquery queries. GizDB's BM25 search is in Search text and vectors.

Function	Result
`to_tsvector([config,] text)`, `json_to_tsvector(config, json, filter)`, `jsonb_to_tsvector(config, json, filter)`	The lexemes of a text, or of the strings of a JSON value.
`array_to_tsvector(array)`, `tsvector_to_array(tsvector)`	A document of the lexemes, and the lexemes of a document.
`setweight(tsvector, weight [, lexemes])`, `strip(tsvector)`	The document with weights set, or without positions and weights.
`ts_delete(tsvector, lexemes)`, `ts_filter(tsvector, weights)`	The document without the lexemes, or with only the weights.
`to_tsquery([config,] text)`, `plainto_tsquery([config,] text)`, `phraseto_tsquery([config,] text)`, `websearch_to_tsquery([config,] text)`	A query from its syntax, from words, from a phrase, or from the syntax of a web search.
`tsquery_phrase(a, b [, distance])`, `querytree(tsquery)`, `numnode(tsquery)`	Two queries as a phrase, the part an index can use, and the size of a query.
`ts_rewrite(tsquery, target, substitute)`	The query with a part replaced.
`ts_rank([weights,] tsvector, tsquery [, normalization])`, `ts_rank_cd(...)`	The rank of a document for a query, by frequency or by cover density.
`ts_headline([config,] document, tsquery [, options])`	The document with the matches marked.
`ts_lexize(dictionary, token)`	The lexemes of a token in a dictionary.
`ts_debug([config,] text)`, `ts_parse(parser, text)`, `ts_token_type(parser)`, `ts_stat(query [, weights])`	Rows: how a text is read, its tokens, the token types, and the lexemes of the documents of a query.
`match(column, query)`, `score()`	In `WHERE`, the rows that a BM25 index finds; in the select list or `ORDER BY`, their score. GizDB.
`highlight(text, query, before, after, max_characters)`	An excerpt of the text with each match between `before` and `after`. GizDB.

Vectors

The vector type, the distance operators <->, <=> and <#>, and the indexes are in Vector search in GizDB.

Function	Result
`vector(value)`, `to_vector(value)`, `string_to_vector(text)`, `vec_f32(value)`	The value as a `vector`.
`vector_dims(v)`, `vector_dimension_count(v)`, `vec_length(v)`	The dimensions.
`vector_norm(v)`, `l2_norm(v)`, `l2_normalize(v)`	The Euclidean length, and the vector with length 1. pgvector.
`subvector(v, from, count)`, `binary_quantize(v)`	Part of a vector, and a bit string with 1 where an element is above 0. pgvector.
`l2_distance(a, b)`, `euclidean_distance(a, b)`, `vec_distance_l2(a, b)`	The Euclidean distance (`<->`).
`distance(a, b [, metric])`, `vector_distance(a, b [, metric])`	The distance of the metric `l2` (default), `cosine` or `inner`.
`cosine_distance(a, b)`, `vec_distance_cosine(a, b)`	1 minus the cosine of the angle (`<=>`).
`inner_product(a, b)`, `dot_product(a, b)`, `negative_inner_product(a, b)`	The inner product, and its negative (`<#>`).
`l1_distance(a, b)`, `hamming_distance(a, b)`, `jaccard_distance(a, b)`	The taxicab distance of vectors, and the distances of bit strings. pgvector.

Search ranking

Function	Result
`rrf()`	In a hybrid search (`match` OR `near` in `WHERE`), the reciprocal rank fusion score of the row. GizDB.
`rrf(rank, rank, k [, weight, weight])`	The reciprocal rank fusion score of two ranks; a NULL rank is a search that did not find the row. GizDB.
`recall_at_k(ranked, relevant [, k])`, `precision_at_k(ranked, relevant [, k])`	The recall and the precision of the first `k` ranked ids (all when `k` is 0 or NULL). GizDB.
`ndcg_at_k(ranked, relevant [, k [, gains]])`	The normalized discounted cumulative gain; `gains` are the graded relevance of each relevant id. GizDB.
`reciprocal_rank(ranked, relevant [, k])`, `average_precision(ranked, relevant [, k])`	One query's term of MRR and of MAP: `avg` of them over the queries is the mean. GizDB.

Machine learning

pgml.train, pgml.predict, pgml.embed and the other functions of PostgresML are in the reference of Machine learning in GizDB.

Function	Result
`pgml.add(a, b)`, `pgml.subtract(a, b)`, `pgml.multiply(a, b)`, `pgml.divide(a, b)`	Two arrays of numbers, element by element. PostgresML.
`pgml.dot_product(a, b)`, `pgml.cosine_similarity(a, b)`, `pgml.distance_l1(a, b)`, `pgml.distance_l2(a, b)`	The product, the similarity and the distances of two arrays. PostgresML.
`pgml.norm_l0(a)`, `pgml.norm_l1(a)`, `pgml.norm_l2(a)`, `pgml.norm_max(a)`	The norms of an array. PostgresML.
`pgml.normalize_l1(a)`, `pgml.normalize_l2(a)`, `pgml.normalize_max(a)`	The array divided by its norm. PostgresML.
`ols_predict(model, x, ...)`	The prediction of a model that `ols` returned (Aggregate functions). GizDB.

Metrics

The functions read the JSON carriers giz.metric.counter.v1 and giz.metric.histogram.v1. GizDB.

Function	Result
`metric_counter(json)`, `metric_histogram(json)`	A counter, or a histogram, read and checked.
`metric_counter_delta(current, previous)`, `metric_histogram_delta(current, previous)`	A cumulative value as the change from the previous one.
`metric_counter_cumulative(delta, previous)`, `metric_histogram_cumulative(delta, previous)`	A change added to the previous cumulative value.
`metric_counter_rate(current, previous)`	The change for each second.
`metric_histogram_quantile(histogram, p)`	The bounds that hold the quantile `p`.

XML

Function	Result
`xmlcomment(text)`, `xmlconcat(xml, ...)`	An XML comment, and XML values joined.
`xml_is_well_formed(text)`, `xml_is_well_formed_document(text)`, `xml_is_well_formed_content(text)`	Whether the text is XML, an XML document, or XML content.
`xpath(path, xml [, namespaces])`, `xpath_exists(path, xml [, namespaces])`	The nodes that an XPath finds, as an array, and whether it finds one.
`xmlforest(value [AS name], ...)`	An XML element for each value, named by its column or its `AS` name.

Identifiers and sequences

Function	Result
`gen_random_uuid()`, `uuid_generate_v4()`	A random UUID.
`uuid()`, `newid()`, `sys_guid()`	A random UUID. MySQL; SQL Server; Oracle.
`nextval(sequence)`, `currval(sequence)`, `lastval()`	The next value of a sequence, and the last value that the session took.
`last_insert_rowid()`, `changes()`	The row id of the last insert, and the rows that the last statement changed. SQLite.
`scope_identity()`	The last identity value of the session. SQL Server.

Session and server

Function	Result
`current_database()`, `current_catalog`, `db_name()`, `ora_database_name()`	The name of the database. `db_name` is SQL Server's, `ora_database_name` Oracle's.
`current_schema()`, `current_schemas(include_implicit)`, `schema_name()`	The first schema of the search path, and all of them. `schema_name` is SQL Server's.
`current_user`, `current_role`, `session_user`, `user`	The name of the role.
`suser_name()`, `uid()`, `userenv(parameter)`	The user, and session facts. SQL Server; Oracle.
`version()`, `sqlite_version()`	The version of the server. `sqlite_version` is SQLite's.
`getdatabaseencoding()`, `pg_client_encoding()`	The encodings: always `UTF8`.
`current_setting(name [, missing_ok])`, `set_config(name, value, is_local)`	A setting of the session, and a change of it.
`pg_backend_pid()`	The id of the session.
`pg_sleep(seconds)`	Waits.
`pg_notify(channel, payload)`, `pg_notification_queue_usage()`	Sends a notification to the listeners of a channel, and the part of the queue in use.
`pg_is_in_recovery()`, `pg_promote([wait [, seconds]])`	Whether the server is a standby, and makes a standby the primary.
`pg_trigger_depth()`	How many triggers are running in each other.
`pg_typeof(value)`, `typeof(value)`	The type of a value. `typeof` is SQLite's.
`pg_column_size(value)`	The bytes that a value takes.
`pg_input_is_valid(text, type)`	Whether the text is a value of the type.
`pg_input_error_info(text, type)`	Rows: the error that reading the text as the type gives (`message`, `detail`, `hint`, `sql_error_code`).
`pg_split_walfile_name(name)`	Rows: the segment number and the timeline of a WAL file name.
`pg_collation_for(value)`	The collation of a value.
`pg_log_backend_memory_contexts(pid)`	Logs the memory of a session.
`table(collection)`	Rows: the elements of a nested table or varray. Oracle.

Transactions and snapshots

Function	Result
`pg_current_xact_id()`, `pg_current_xact_id_if_assigned()`, `txid_current()`, `txid_current_if_assigned()`	The id of the transaction.
`pg_current_snapshot()`, `txid_current_snapshot()`	The snapshot of the transaction.
`pg_snapshot_xmin(snapshot)`, `pg_snapshot_xmax(snapshot)`, `txid_snapshot_xmin(snapshot)`, `txid_snapshot_xmax(snapshot)`	The bounds of a snapshot.
`pg_snapshot_xip(snapshot)`, `txid_snapshot_xip(snapshot)`	Rows: the transactions in progress in a snapshot.
`pg_visible_in_snapshot(id, snapshot)`, `txid_visible_in_snapshot(id, snapshot)`	Whether a transaction is visible in a snapshot.
`pg_xact_status(id)`, `txid_status(id)`	`committed`, `aborted`, `in progress`, or NULL.

Advisory locks

A lock is a bigint key or two integer keys. A session lock is held until its unlock or the end of the session, an xact lock until the end of the transaction. Refer to Named locks.

Function	Result
`pg_advisory_lock(key)`, `pg_advisory_lock_shared(key)`	Waits for the lock and takes it.
`pg_advisory_xact_lock(key)`, `pg_advisory_xact_lock_shared(key)`	The same, until the end of the transaction.
`pg_try_advisory_lock(key)`, `pg_try_advisory_lock_shared(key)`, `pg_try_advisory_xact_lock(key)`, `pg_try_advisory_xact_lock_shared(key)`	Takes the lock if it is free; whether it did.
`pg_advisory_unlock(key)`, `pg_advisory_unlock_shared(key)`, `pg_advisory_unlock_all()`	Releases a lock (whether it was held), or all locks of the session.

Catalog

These functions read the system catalogs, as in PostgreSQL.

Function	Result
`format_type(type, modifier)`	The SQL name of a type.
`col_description(table, column)`, `obj_description(object [, catalog])`, `shobj_description(object, catalog)`	The comment of a column or an object.
`pg_get_constraintdef(constraint [, pretty])`, `pg_get_indexdef(index [, column, pretty])`, `pg_get_triggerdef(trigger [, pretty])`, `pg_get_ruledef(rule [, pretty])`, `pg_get_viewdef(view [, pretty])`	The SQL that defines the object.
`pg_get_functiondef(function)`, `pg_get_function_arguments(function)`, `pg_get_function_identity_arguments(function)`, `pg_get_function_result(function)`	The SQL of a function, of its arguments and of its result.
`pg_get_statisticsobjdef(statistics)`, `pg_get_statisticsobjdef_columns(statistics)`	The SQL of an extended statistics object, and of its columns.
`pg_get_expr(expression, relation [, pretty])`	The SQL of a stored expression.
`pg_get_serial_sequence(table, column)`	The sequence of a serial or identity column.
`pg_get_userbyid(role)`	The name of a role.
`pg_describe_object(catalog, object, sub)`, `pg_get_object_address(type, names, arguments)`	An object as text, and the address of a named object.
`pg_table_is_visible(table)`, `pg_type_is_visible(type)`, `pg_function_is_visible(function)`	Whether the search path finds the object by its name alone.
`pg_is_other_temp_schema(schema)`, `pg_my_temp_schema()`	Whether a schema is the temporary schema of another session, and the one of this session.
`has_table_privilege([role,] table, privilege)`, `has_any_column_privilege(...)`, `has_column_privilege(...)`, `has_database_privilege(...)`, `has_foreign_data_wrapper_privilege(...)`, `has_function_privilege(...)`, `has_language_privilege(...)`, `has_schema_privilege(...)`, `has_sequence_privilege(...)`, `has_server_privilege(...)`, `has_tablespace_privilege(...)`, `has_type_privilege(...)`, `pg_has_role([role,] role, privilege)`	Whether the role has the privilege.
`acldefault(type, owner)`, `makeaclitem(grantee, grantor, privileges, grantable)`	The default privileges of an object type, and one privilege item.
`to_regclass(text)`, `to_regcollation(text)`, `to_regnamespace(text)`, `to_regoper(text)`, `to_regoperator(text)`, `to_regproc(text)`, `to_regprocedure(text)`, `to_regrole(text)`, `to_regtype(text)`	The object of a name, or NULL when there is none.
`pg_filenode_relation(tablespace, filenode)`, `pg_relation_filenode(relation)`	The relation of a file, and the file of a relation.
`pg_partition_ancestors(table)`, `pg_partition_root(table)`, `pg_relation_is_publishable(table)`	The parents of a partition, its root, and whether a publication can hold the table.
`pg_partition_tree(table)`	Rows: the partitions of a table, with their parents and levels.
`pg_relation_is_updatable(relation, include_triggers)`, `pg_column_is_updatable(relation, column, include_triggers)`	The events that a relation takes (a bit mask), and whether a column can be updated.
`satisfies_hash_partition(parent, modulus, remainder, value, ...)`	Whether the values go to the hash partition of that modulus and remainder.
`pg_index_has_property(index, property)`, `pg_index_column_has_property(index, column, property)`, `pg_indexam_has_property(method, property)`, `amvalidate(operator_class)`	The properties of an index, of its column and of its access method, and the check of an operator class.
`pg_tablespace_location(tablespace)`	The folder of a tablespace.
`pg_char_to_encoding(name)`, `pg_encoding_to_char(code)`	The code of an encoding name, and back.
`brin_summarize_range(index, block)`, `brin_summarize_new_values(index)`, `brin_desummarize_range(index, block)`	The maintenance functions of BRIN indexes.

Sizes

Function	Result
`pg_relation_size(relation [, fork])`, `pg_table_size(table)`, `pg_indexes_size(table)`, `pg_total_relation_size(table)`	The bytes of a relation, of a table without indexes, of its indexes, and of all of them.
`pg_database_size(database)`	The bytes of a database.
`pg_size_pretty(bytes)`, `pg_size_bytes(text)`	Bytes as `kB`, `MB` or `GB` text, and back.

Statistics

The counters are the ones of pg_stat_user_tables. The calls of a function are counted; their times are 0.

Function	Result
`pg_stat_get_numscans(table)`, `pg_stat_get_tuples_inserted(table)`, `pg_stat_get_tuples_updated(table)`, `pg_stat_get_tuples_deleted(table)`, `pg_stat_get_tuples_hot_updated(table)`, `pg_stat_get_live_tuples(table)`, `pg_stat_get_dead_tuples(table)`	The counters of a table.
`pg_stat_get_xact_numscans(table)`, `pg_stat_get_xact_tuples_inserted(table)`, `pg_stat_get_xact_tuples_updated(table)`, `pg_stat_get_xact_tuples_deleted(table)`, `pg_stat_get_xact_tuples_hot_updated(table)`	The counters of the open transaction.
`pg_stat_get_function_calls(function)`, `pg_stat_get_function_total_time(function)`, `pg_stat_get_function_self_time(function)`, `pg_stat_get_xact_function_calls(function)`, `pg_stat_get_xact_function_total_time(function)`, `pg_stat_get_xact_function_self_time(function)`	The calls of a function, and their times.
`pg_stat_get_snapshot_timestamp()`, `pg_stat_have_stats(kind, database, object)`	When the statistics were read, and whether an object has them.
`pg_stat_reset()`, `pg_stat_reset_shared(target)`, `pg_stat_reset_slru(name)`, `pg_stat_reset_subscription_stats(subscription)`, `pg_stat_reset_single_table_counters(table)`, `pg_stat_reset_single_function_counters(function)`, `pg_stat_reset_replication_slot(slot)`	Sets counters to 0.
`pg_stat_clear_snapshot()`, `pg_stat_force_next_flush()`	Reads the statistics again at the next use, and sends the counts of the session at once.

Large objects

Function	Result
`lo_create(oid)`, `lo_creat(mode)`, `lo_import(path [, oid])`	A new large object.
`lo_get(oid [, offset, length])`	The bytes of a large object.
`lo_from_bytea(oid, bytes)`, `lo_put(oid, offset, bytes)`	A new large object with the bytes, and bytes written into one.
`lo_open(oid, mode)`, `loread(descriptor, length)`, `lowrite(descriptor, bytes)`	Opens a large object, and reads or writes at the position.
`lo_lseek(descriptor, offset, whence)`, `lo_truncate(descriptor, length)`, `lo_truncate64(descriptor, length)`	Moves the position, and cuts the object to a length.
`lo_export(oid, path)`	Writes a large object to a file.
`lo_tell(descriptor)`, `lo_tell64(descriptor)`, `lo_close(descriptor)`, `lo_unlink(oid)`	The position in an open object, its close, and the removal of an object.

Oracle packages

Function	Result
`dbms_assert.enquote_literal(text)`, `dbms_assert.enquote_name(text [, capitalize])`, `dbms_assert.simple_sql_name(text)`	The text quoted as a literal or a name, and the check of a simple name. Oracle.
`dbms_lob.getlength(lob)`, `dbms_lob.instr(lob, pattern [, from [, nth]])`, `dbms_lob.substr(lob [, count [, from]])`	The length of a large value, a position in it, and part of it. Oracle.
`dbms_random.value([low, high])`, `dbms_random.random()`, `dbms_random.string(kind, length)`	A random number from 0 up to 1 (or `low` up to `high`), a random integer, and a random text. Oracle.
`dbms_utility.format_error_stack()`, `dbms_utility.format_error_backtrace()`, `dbms_utility.get_time()`	The error being handled, where it came from, and a time in hundredths of a second. Oracle.
`dbms_db_version.version()`	The major version. Oracle.
`utl_encode.base64_encode(raw)`, `utl_encode.base64_decode(raw)`	Bytes as Base64, and back. Oracle.
`utl_raw.cast_to_raw(text)`, `utl_raw.cast_to_varchar2(raw)`, `utl_raw.length(raw)`	Text as bytes, bytes as text, and the length in bytes. Oracle.

Key-value rows

The rows of the key-value interface (Use the Redis protocol) are keyed by an identity of the tenant, the database and the key. GizDB.

Function	Result
`gizdb_kv_identity(tenant, database, key)`	The identity of a key, to find its row.
`gizdb_kv_tenant(identity)`, `gizdb_kv_db(identity)`, `gizdb_kv_key(identity)`	One part of an identity, or NULL for another value.
`gizdb_kv_shard(identity, shards)`	The shard of the key.

Aggregate functions

An aggregate takes DISTINCT, ORDER BY and FILTER (WHERE ...), and is a window function with OVER.

Function	Result
`count(*)`, `count(value)`, `count_big(value)`	The rows, or the values that are not NULL. `count_big` is SQL Server's.
`count_if(condition)`	The rows where the condition is true. StarRocks.
`sum(x)`, `total(x)`, `avg(x)`	The sum and the mean. `total` (SQLite) is 0.0 when there are no rows.
`min(x)`, `max(x)`	The smallest and the largest value. With two or more arguments they are `least` and `greatest`.
`min_by(value, key)`, `max_by(value, key)`	The value of the row with the smallest or the largest key. StarRocks.
`any_value(x)`, `arbitrary(x)`	One of the values. `arbitrary` is StarRocks'.
`every(condition)`, `bool_and(condition)`, `bool_or(condition)`	Whether all, or any, conditions are true.
`bit_and(x)`, `bit_or(x)`, `bit_xor(x)`	The bits of all values combined.
`array_agg(value)`	The values as an array.
`string_agg(text, separator)`, `listagg(text [, separator])`, `group_concat(text [, separator])`	The values joined. Oracle; MySQL, SQLite.
`json_agg(value)`, `jsonb_agg(value)`, `json_arrayagg(value)`, `json_group_array(value)`	The values as a JSON array. `json_group_array` is SQLite's.
`json_object_agg(key, value)`, `jsonb_object_agg(key, value)`, `json_objectagg(key: value)`, `json_group_object(key, value)`	The pairs as a JSON object. `json_group_object` is SQLite's.
`xmlagg(xml)`	The XML values joined.
`range_agg(range)`, `range_intersect_agg(range)`	The union of the ranges as a multirange, and their intersection.
`stddev(x)`, `stddev_samp(x)`, `stddev_pop(x)`, `stdev(x)`, `stdevp(x)`	The standard deviation of a sample or of the population. `stdev` and `stdevp` are SQL Server's.
`variance(x)`, `var_samp(x)`, `var_pop(x)`, `var(x)`, `varp(x)`	The variance. `var` and `varp` are SQL Server's.
`corr(y, x)`, `covar_samp(y, x)`, `covar_pop(y, x)`	The correlation and the covariance.
`regr_slope(y, x)`, `regr_intercept(y, x)`, `regr_r2(y, x)`, `regr_count(y, x)`, `regr_avgx(y, x)`, `regr_avgy(y, x)`, `regr_sxx(y, x)`, `regr_syy(y, x)`, `regr_sxy(y, x)`	The linear regression of `y` on `x`.
`percentile_cont(fraction) WITHIN GROUP (ORDER BY x)`, `percentile_disc(fraction) WITHIN GROUP (ORDER BY x)`	The value at the fraction, interpolated or taken from a row.
`mode() WITHIN GROUP (ORDER BY x)`	The most frequent value.
`median(x)`	The middle value. Oracle.
`percentile_approx(x, fraction)`, `approx_count_distinct(x)`	The value at the fraction, and the count of distinct values. StarRocks.
`ols(y, x, ...)`, `ols_summary(y, x, ...)`	The coefficients of the least squares fit of `y` on the `x` values, and the fit with its statistics. Refer to OLS. GizDB.
`metric_histogram_merge(histogram)`	The histograms merged. GizDB.
`pgml.sum(array)`, `pgml.min(array)`, `pgml.max(array)`, `pgml.min_abs(array)`, `pgml.max_abs(array)`	The arrays combined element by element. PostgresML.

Window functions

A window function needs OVER.

Function	Result
`row_number()`	The number of the row in its partition, from 1.
`rank()`, `dense_rank()`	The rank of the row, with gaps, or without gaps.
`percent_rank()`, `cume_dist()`	The relative rank, and the part of the rows before or tied with the row.
`ntile(n)`	The bucket of the row among `n` buckets.
`lag(value [, offset [, default]])`, `lead(value [, offset [, default]])`	The value of a row before or after.
`first_value(value)`, `last_value(value)`, `nth_value(value, n)`	The value of the first, the last or the `n`-th row of the frame.

Rows

Each other function that returns rows is under its subject, marked "Rows".

Function	Result
`generate_series(start, stop [, step])`	Rows: the numbers or the times from `start` to `stop`.
`pg_options_to_table(options)`	Rows: the name and the value of each `name=value` option.
`changes(table, after [, wait_ms [, column, value]])`	Rows: the committed changes of a table after a position (Live queries). GizDB.
`read_csv(path [, option => value ...])`, `read_csv_auto(path ...)`	Rows: the CSV files of a path or a glob under the server's files directory (Query files).
`read_parquet(path ...)`, `read_json(path ...)`, `read_json_auto(path ...)`, `read_ndjson(path ...)`, `read_file(path ...)`	Rows: the Parquet files, the files of JSON lines, or files of the format of their extension.

Support functions

PostgreSQL's catalogs name these functions: they are the operators, the aggregate steps and the hash functions of its types. A program calls the operators instead.

Function	Result
`booleq(a, b)`, `boolne(a, b)`	`=` and `<>` of booleans.
`int2larger(a, b)`, `int2smaller(a, b)`, `int4larger(a, b)`, `int4smaller(a, b)`, `int8larger(a, b)`, `int8smaller(a, b)`, `float4larger(a, b)`, `float4smaller(a, b)`, `float8larger(a, b)`, `float8smaller(a, b)`, `numeric_larger(a, b)`, `numeric_smaller(a, b)`, `cashlarger(a, b)`, `cashsmaller(a, b)`, `text_larger(a, b)`, `text_smaller(a, b)`, `array_larger(a, b)`, `array_smaller(a, b)`	The larger or the smaller value: the steps of `max` and `min`.
`numeric_inc(x)`	`x + 1`.
`textcat(a, b)`	`a \|\| b`.
`range_contains(a, b)`, `range_contains_elem(range, value)`, `range_contains_multirange(range, multirange)`, `multirange_contains_elem(multirange, value)`, `multirange_contains_range(multirange, range)`, `multirange_contains_multirange(a, b)`	`@>`.
`range_contained_by(a, b)`, `elem_contained_by_range(value, range)`, `elem_contained_by_multirange(value, multirange)`, `range_contained_by_multirange(range, multirange)`, `multirange_contained_by_range(multirange, range)`, `multirange_contained_by_multirange(a, b)`	`<@`.
`range_overlaps(a, b)`, `range_overlaps_multirange(...)`, `multirange_overlaps_range(...)`, `multirange_overlaps_multirange(...)`	`&&`.
`range_adjacent(a, b)`, `range_adjacent_multirange(...)`, `multirange_adjacent_range(...)`, `multirange_adjacent_multirange(...)`	`-\|-`.
`range_before(a, b)`, `range_before_multirange(...)`, `multirange_before_range(...)`, `multirange_before_multirange(...)`	`<<`.
`range_after(a, b)`, `range_after_multirange(...)`, `multirange_after_range(...)`, `multirange_after_multirange(...)`	`>>`.
`range_overleft(a, b)`, `range_overleft_multirange(...)`, `multirange_overleft_range(...)`, `multirange_overleft_multirange(...)`	`&<`.
`range_overright(a, b)`, `range_overright_multirange(...)`, `multirange_overright_range(...)`, `multirange_overright_multirange(...)`	`&>`.
`range_union(a, b)`, `multirange_union(a, b)`, `range_intersect(a, b)`, `multirange_intersect(a, b)`, `range_minus(a, b)`, `multirange_minus(a, b)`	`+`, `*` and `-`.
`booland_statefunc(state, value)`, `boolor_statefunc(state, value)`, `float8_accum(state, x)`, `float8_combine(a, b)`, `float8_regr_accum(state, y, x)`, `float8_regr_combine(a, b)`	Steps of aggregates.
`float4send(x)`, `float8send(x)`	The binary form of a float.
`tsvectorin(text)`, `tsvectorout(tsvector)`, `tsqueryin(text)`, `tsqueryout(tsquery)`	The text forms of the text search types.
`hashint2(x [, seed])`, `hashint4(...)`, `hashint8(...)`, `hashfloat4(...)`, `hashfloat8(...)`, `hash_numeric(...)`, `hashtext(...)`, `hashname(...)`, `hashchar(...)`, `hashbpchar(...)`, `hashvarlena(...)`, `bytea_hash(...)`, `hashoid(...)`, `hashoidvector(...)`, `hashenum(...)`, `hashinet(...)`, `hashmacaddr(...)`, `hashmacaddr8(...)`, `hash_aclitem(...)`, `hash_array(...)`, `hash_range(...)`, `hash_multirange(...)`, `hash_record(...)`, `jsonb_hash(...)`, `pg_lsn_hash(...)`, `uuid_hash(...)`, `interval_hash(x)`, `time_hash(...)`, `timetz_hash(...)`, `timestamp_hash(...)`	The hash of a value, as the hash indexes and hash partitions of PostgreSQL compute it.