Disc provides a comprehensive standard library of built-in functions available in EdgeQL queries. Each function compiles to its PostgreSQL equivalent at query time.
This reference documents every function registered in the Disc compiler. Functions are organized by category.
See also: EdgeQL | Schema | CLI
lenReturns the length of a string.
len(val: str) -> int64
Example:
select len('hello');
SQL equivalent: LENGTH('hello')
str_lowerConverts a string to lowercase.
str_lower(val: str) -> str
Example:
select str_lower('HELLO WORLD');
# => 'hello world'
SQL equivalent: LOWER('HELLO WORLD')
str_upperConverts a string to uppercase.
str_upper(val: str) -> str
Example:
select str_upper('hello world');
# => 'HELLO WORLD'
SQL equivalent: UPPER('hello world')
str_titleConverts a string to title case (capitalizes the first letter of each word).
str_title(val: str) -> str
Example:
select str_title('hello world');
# => 'Hello World'
SQL equivalent: INITCAP('hello world')
str_trimRemoves leading and trailing whitespace from a string.
str_trim(val: str) -> str
Example:
select str_trim(' hello ');
# => 'hello'
SQL equivalent: TRIM(' hello ')
str_ltrimRemoves leading whitespace from a string.
str_ltrim(val: str) -> str
Example:
select str_ltrim(' hello');
# => 'hello'
SQL equivalent: LTRIM(' hello')
str_rtrimRemoves trailing whitespace from a string.
str_rtrim(val: str) -> str
Example:
select str_rtrim('hello ');
# => 'hello'
SQL equivalent: RTRIM('hello ')
str_repeatRepeats a string a given number of times.
str_repeat(val: str, n: int64) -> str
Example:
select str_repeat('ab', 3);
# => 'ababab'
SQL equivalent: REPEAT('ab', 3)
str_replaceReplaces all occurrences of a substring with another string.
str_replace(val: str, old: str, new: str) -> str
Example:
select str_replace('hello world', 'world', 'disc');
# => 'hello disc'
SQL equivalent: REPLACE('hello world', 'world', 'disc')
str_splitSplits a string by a delimiter and returns an array.
str_split(val: str, delimiter: str) -> array<str>
Example:
select str_split('a,b,c', ',');
# => ['a', 'b', 'c']
SQL equivalent: STRING_TO_ARRAY('a,b,c', ',')
str_starts_withReturns true if the string starts with the given prefix.
str_starts_with(val: str, prefix: str) -> bool
Example:
select str_starts_with('hello', 'he');
# => true
SQL equivalent: STARTS_WITH('hello', 'he')
str_ends_withReturns true if the string ends with the given suffix.
str_ends_with(val: str, suffix: str) -> bool
Example:
select str_ends_with('hello', 'lo');
# => true
SQL equivalent: RIGHT('hello', LENGTH('lo')) = 'lo'
str_pad_startPads a string on the left to a specified length. Uses spaces by default, or a custom fill character.
str_pad_start(val: str, n: int64, fill?: str) -> str
Example:
select str_pad_start('42', 5, '0');
# => '00042'
select str_pad_start('hi', 6);
# => ' hi'
SQL equivalent: LPAD('42', 5, '0')
str_pad_endPads a string on the right to a specified length. Uses spaces by default, or a custom fill character.
str_pad_end(val: str, n: int64, fill?: str) -> str
Example:
select str_pad_end('hi', 6, '.');
# => 'hi....'
SQL equivalent: RPAD('hi', 6, '.')
containsReturns true if the haystack string contains the needle substring.
This function is overloaded -- when used with range arguments, it checks whether a range contains an element (see Range Functions).
contains(haystack: str, needle: str) -> bool
Example:
select contains('hello world', 'world');
# => true
select contains('hello world', 'xyz');
# => false
SQL equivalent: STRPOS('hello world', 'world') > 0
findReturns the 0-based index of the first occurrence of needle in haystack. Returns -1 if not found.
find(haystack: str, needle: str) -> int64
Example:
select find('hello world', 'world');
# => 6
select find('hello', 'xyz');
# => -1
SQL equivalent: STRPOS('hello world', 'world') - 1
Note: PostgreSQL’s STRPOS is 1-indexed and returns 0 when not found. Disc adjusts the result to match EdgeQL’s 0-indexed convention.
re_matchReturns the first match of a regular expression pattern against a string. Returns an array of matched groups.
Note: Argument order in EdgeQL is (pattern, string), but this compiles to PostgreSQL’s REGEXP_MATCH(string, pattern) with arguments swapped.
re_match(pattern: str, val: str) -> array<str>
Example:
select re_match('^[a-z]+$', 'hello');
# => ['hello']
select re_match('(\w+)@(\w+)', 'user@host');
# => ['user@host', 'user', 'host']
SQL equivalent: REGEXP_MATCH('hello', '^[a-z]+$')
re_match_allReturns all matches of a regular expression pattern against a string. Each match is an array of groups.
re_match_all(pattern: str, val: str) -> array<array<str>>
Example:
select re_match_all('[0-9]+', 'a1b2c3');
# => [['1'], ['2'], ['3']]
SQL equivalent: REGEXP_MATCHES('a1b2c3', '[0-9]+', 'g')
re_replaceReplaces occurrences of a regex pattern with a substitution string.
Note: Argument order in EdgeQL is (pattern, sub, string), but this compiles to PostgreSQL’s REGEXP_REPLACE(string, pattern, sub) with arguments reordered.
re_replace(pattern: str, sub: str, val: str) -> str
Example:
select re_replace('[0-9]', 'X', 'a1b2');
# => 'aXbX'
SQL equivalent: REGEXP_REPLACE('a1b2', '[0-9]', 'X')
re_testReturns true if the string matches the regular expression pattern.
re_test(pattern: str, val: str) -> bool
Example:
select re_test('^[a-z]+$', 'hello');
# => true
select re_test('^[0-9]+$', 'hello');
# => false
SQL equivalent: 'hello' ~ '^[a-z]+$'
randomReturns a pseudo-random float64 in the range 0.0 <= x < 1.0. Volatile — a new value every call.
random() -> float64
Example:
# random row ordering
select User { name } order by random() limit 1;
SQL equivalent: RANDOM()
math_absReturns the absolute value of a number.
math_abs(val: anyreal) -> anyreal
Example:
select math_abs(-42);
# => 42
select math_abs(-3.14);
# => 3.14
SQL equivalent: ABS(-42)
math_ceilReturns the smallest integer greater than or equal to the given value.
math_ceil(val: anyreal) -> float64
Example:
select math_ceil(3.2);
# => 4.0
select math_ceil(-1.5);
# => -1.0
SQL equivalent: CEIL(3.2)
math_floorReturns the largest integer less than or equal to the given value.
math_floor(val: anyreal) -> float64
Example:
select math_floor(3.8);
# => 3.0
select math_floor(-1.2);
# => -2.0
SQL equivalent: FLOOR(3.8)
roundRounds a number to the nearest integer.
round(val: anyreal) -> float64
Example:
select round(3.5);
# => 4.0
select round(3.4);
# => 3.0
SQL equivalent: ROUND(3.5)
math_sqrtReturns the square root of a number.
math_sqrt(val: anyreal) -> float64
Example:
select math_sqrt(16);
# => 4.0
Can also be invoked with the math:: namespace prefix:
select math::sqrt(16);
SQL equivalent: SQRT(16)
math_powRaises a base to the given exponent.
math_pow(base: anyreal, exp: anyreal) -> float64
Example:
select math::pow(2, 10);
# => 1024.0
SQL equivalent: POWER(2, 10)
math_powerAlias for math_pow. Raises a base to the given exponent.
math_power(base: anyreal, exp: anyreal) -> float64
Example:
select math_power(3, 3);
# => 27.0
SQL equivalent: POWER(3, 3)
math_logReturns the logarithm of a value with the specified base.
math_log(base: anyreal, val: anyreal) -> float64
Example:
select math::log(10, 100);
# => 2.0
SQL equivalent: LOG(10, 100)
math_lnReturns the natural logarithm (base e) of a value.
math_ln(val: anyreal) -> float64
Example:
select math::ln(2.718);
# => ~1.0
SQL equivalent: LN(2.718)
math_log10Returns the base-10 logarithm of a value.
math_log10(val: anyreal) -> float64
Example:
select math_log10(100);
# => 2.0
SQL equivalent: LOG(10, val) (computed via PostgreSQL’s LOG function with base 10)
math_log2Returns the base-2 logarithm of a value.
math_log2(val: anyreal) -> float64
Example:
select math_log2(8);
# => 3.0
SQL equivalent: LOG(2, val) (computed via PostgreSQL’s LOG function with base 2)
math_piReturns the value of pi.
math_pi() -> float64
Example:
select math::pi();
# => 3.141592653589793
SQL equivalent: PI()
math_eReturns the value of Euler’s number (e).
math_e() -> float64
Example:
select math::e();
# => 2.718281828459045
SQL equivalent: EXP(1)
math_meanReturns the arithmetic mean (average) of a set of values. This is an aggregate function and can also be used as a window function.
math_mean(expr: anyreal) -> float64
Example:
select math_mean(User.age);
SQL equivalent: AVG(...) (same as avg)
Aggregate functions operate on sets of values and return a single result. All aggregate functions listed here can also be used as window functions with an OVER clause.
countReturns the number of elements in a set.
count(expr: any) -> int64
Example:
select count(User);
select count(User filter .active = true);
SQL equivalent: COUNT(...)
sumReturns the sum of a set of numeric values.
sum(expr: anyreal) -> int64
Example:
select sum(Order.total);
SQL equivalent: SUM(...)
minReturns the minimum value in a set.
min(expr: any) -> int64
Example:
select min(Product.price);
SQL equivalent: MIN(...)
maxReturns the maximum value in a set.
max(expr: any) -> int64
Example:
select max(Product.price);
SQL equivalent: MAX(...)
avgReturns the arithmetic mean of a set of numeric values.
avg(expr: anyreal) -> float64
Example:
select avg(Order.total);
SQL equivalent: AVG(...)
stddevReturns the sample standard deviation (equivalent to stddev_samp).
stddev(expr: anyreal) -> float64
Example:
select stddev(Measurement.value);
SQL equivalent: STDDEV(...)
stddev_popReturns the population standard deviation.
stddev_pop(expr: anyreal) -> float64
Example:
select stddev_pop(Measurement.value);
SQL equivalent: STDDEV_POP(...)
stddev_sampReturns the sample standard deviation.
stddev_samp(expr: anyreal) -> float64
Example:
select stddev_samp(Measurement.value);
SQL equivalent: STDDEV_SAMP(...)
array_aggCollects a set of values into an array.
array_agg(expr: any) -> array
Example:
select array_agg(User.name);
# => ['Ada', 'Billie', 'Cher']
SQL equivalent: ARRAY_AGG(...)
Type conversion functions cast values from one type to another. They compile to PostgreSQL CAST expressions.
to_strConverts any value to a string.
to_str(val: any) -> str
Example:
select to_str(42);
# => '42'
SQL equivalent: CAST(42 AS text)
to_int16Converts a value to a 16-bit integer.
to_int16(val: any) -> int16
Example:
select to_int16('42');
SQL equivalent: CAST('42' AS smallint)
to_int32Converts a value to a 32-bit integer.
to_int32(val: any) -> int32
Example:
select to_int32('42');
SQL equivalent: CAST('42' AS integer)
to_int64Converts a value to a 64-bit integer.
to_int64(val: any) -> int64
Example:
select to_int64('42');
SQL equivalent: CAST('42' AS bigint)
to_float32Converts a value to a 32-bit floating-point number.
to_float32(val: any) -> float32
Example:
select to_float32('3.14');
SQL equivalent: CAST('3.14' AS real)
to_float64Converts a value to a 64-bit floating-point number.
to_float64(val: any) -> float64
Example:
select to_float64('3.14');
SQL equivalent: CAST('3.14' AS double precision)
to_bigintConverts a value to an arbitrary-precision integer.
to_bigint(val: any) -> bigint
Example:
select to_bigint('999');
SQL equivalent: CAST('999' AS numeric)
to_decimalConverts a value to an arbitrary-precision decimal.
to_decimal(val: any) -> decimal
Example:
select to_decimal('1.5');
SQL equivalent: CAST('1.5' AS numeric)
to_boolConverts a value to a boolean.
to_bool(val: any) -> bool
Example:
select to_bool('true');
# => true
SQL equivalent: CAST('true' AS boolean)
to_uuidConverts a string to a UUID.
to_uuid(val: any) -> uuid
Example:
select to_uuid('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11');
SQL equivalent: CAST('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS uuid)
to_jsonConverts any value to JSON.
to_json(val: any) -> json
Example:
select to_json('hello');
select to_json(42);
SQL equivalent: TO_JSONB('hello')
json_typeofReturns the type of a JSON value as a string. Possible return values: "string", "number", "boolean", "null", "object", "array".
json_typeof(val: json) -> str
Example:
select json_typeof(to_json(42));
# => 'number'
select json_typeof(to_json('hello'));
# => 'string'
SQL equivalent: JSONB_TYPEOF(...)
json_getRetrieves a value from a JSON object by key.
json_get(val: json, key: str) -> json
Example:
select json_get(to_json('{"name": "Ada", "age": 30}'), 'name');
# => '"Ada"'
SQL equivalent: val -> 'name' (the -> operator)
json_array_unpackExpands a JSON array into a set of JSON elements.
json_array_unpack(val: json) -> set of json
Example:
select json_array_unpack(to_json('[1, 2, 3]'));
# => {1, 2, 3} (as individual JSON values)
SQL equivalent: JSONB_ARRAY_ELEMENTS(...)
json_object_unpackExpands a JSON object into a set of key-value pairs.
json_object_unpack(val: json) -> set of json
Example:
select json_object_unpack(to_json('{"a": 1, "b": 2}'));
SQL equivalent: JSONB_EACH(...)
array_aggCollects a set of values into an array. See Aggregate Functions for details.
array_unpackUnpacks an array into a set of individual elements.
array_unpack(val: array) -> set of any
Example:
select array_unpack([1, 2, 3]);
# => {1, 2, 3}
SQL equivalent: UNNEST(ARRAY[1, 2, 3])
array_joinJoins array elements into a single string using a delimiter.
array_join(val: array<str>, delimiter: str) -> str
Example:
select array_join(['a', 'b', 'c'], ',');
# => 'a,b,c'
select array_join(['hello', 'world'], ' ');
# => 'hello world'
SQL equivalent: ARRAY_TO_STRING(ARRAY['a','b','c'], ',')
array_getReturns the element at a given 0-based index in an array.
array_get(val: array, index: int64) -> any
Example:
select array_get([10, 20, 30], 0);
# => 10
select array_get([10, 20, 30], 2);
# => 30
SQL equivalent: (ARRAY[10,20,30])[0 + 1]
Note: PostgreSQL arrays are 1-indexed. Disc automatically adjusts by adding 1 to the index.
distinctReturns the distinct elements of a set, removing duplicates.
distinct(val: any) -> set of any
Example:
select distinct User.role;
SQL equivalent: DISTINCT ...
existsReturns true if a set is non-empty.
exists(val: any) -> bool
Example:
select exists(
select User filter .email = 'admin@example.com'
);
# => true (if such a user exists)
Note: The EdgeQL parser treats exists as a unary keyword operator. It may compile to EXISTS (subquery) for subqueries or expr IS NOT NULL for scalar values.
enumerateReturns a set of (index, value) tuples, pairing each element with its 0-based position.
enumerate(val: any) -> set of tuple<int64, any>
Example:
select enumerate(User.name);
# => {(0, 'Ada'), (1, 'Billie'), (2, 'Cher')}
SQL equivalent: jsonb_build_array(ROW_NUMBER() OVER () - 1, val)
anyReturns true if any element in a set of boolean values is true.
any(vals: bool) -> bool
Example:
select any(User.is_admin);
# => true (if at least one admin exists)
SQL equivalent: BOOL_OR(...)
allReturns true if all elements in a set of boolean values are true.
all(vals: bool) -> bool
Example:
select all(User.email_verified);
# => true (only if every user is verified)
SQL equivalent: BOOL_AND(...)
Assertion functions are pass-through functions that enforce cardinality constraints at runtime. They do not map to a specific SQL function -- they wrap the expression and validate the result.
assert_existsAsserts that the expression returns at least one result. Raises an error if the set is empty.
assert_exists(expr: any) -> any
Example:
select assert_exists(
(select User filter .id = <uuid>$user_id)
);
assert_singleAsserts that the expression returns at most one result. Raises an error if the set contains more than one element.
assert_single(expr: any) -> any
Example:
select assert_single(
(select User filter .email = 'unique@example.com')
);
uuid_generate_v4Generates a random UUID (version 4).
uuid_generate_v4() -> uuid
Example:
select uuid_generate_v4();
# => 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' (random each time)
SQL equivalent: GEN_RANDOM_UUID()
uuid_generate_v1mcGenerates a UUID. In Disc, this maps to the same implementation as uuid_generate_v4 because PostgreSQL 16 does not provide a built-in v1mc generator.
uuid_generate_v1mc() -> uuid
Example:
select uuid_generate_v1mc();
SQL equivalent: GEN_RANDOM_UUID()
disc_uuidv7Generates a time-ordered UUID (version 7, RFC 9562). The high 48 bits are a millisecond timestamp, so values sort chronologically and keep primary-key index inserts sequential. This is the default generator for every object type’s id (Disc diverges from Gel’s random v4 here). It’s provided by a bootstrapped stdlib function built on pgcrypto, so it works on PostgreSQL 16/17/18 without relying on PG 18’ native uuidv7().
disc_uuidv7() -> uuid
Example:
select disc_uuidv7();
# => '019ef662-b2a4-77a9-a6b3-c1d5c2208af9' (time-ordered)
SQL equivalent: disc_uuidv7() (Disc stdlib function)
datetime_currentReturns the current date and time (with time zone).
datetime_current() -> datetime
Example:
select datetime_current();
# => '2026-03-20T14:30:00+00:00'
Commonly used as a default value in schema definitions:
type User {
created_at: datetime {
default := datetime_current();
};
};
SQL equivalent: NOW()
datetime_of_transactionReturns the timestamp of the current transaction. Unlike datetime_current(), this value remains constant for the duration of the transaction.
datetime_of_transaction() -> datetime
Example:
select datetime_of_transaction();
SQL equivalent: TRANSACTION_TIMESTAMP()
datetime_of_statementReturns the timestamp of the current statement. This value remains constant for the duration of a single statement but may change between statements in a transaction.
datetime_of_statement() -> datetime
Example:
select datetime_of_statement();
SQL equivalent: STATEMENT_TIMESTAMP()
datetime_getExtracts a component (field) from a datetime value. Valid fields include 'year', 'month', 'day', 'hour', 'minute', 'second', 'epoch', and others supported by PostgreSQL’s EXTRACT.
datetime_get(val: datetime, field: str) -> float64
Example:
select datetime_get(datetime_current(), 'year');
# => 2026
select datetime_get(datetime_current(), 'month');
# => 3
SQL equivalent: EXTRACT(year FROM NOW())
datetime_truncateTruncates a datetime value to the specified precision. Valid fields include 'year', 'month', 'day', 'hour', 'minute', 'second'.
datetime_truncate(val: datetime, field: str) -> datetime
Example:
select datetime_truncate(datetime_current(), 'month');
# => '2026-03-01T00:00:00+00:00'
SQL equivalent: DATE_TRUNC('month', NOW())
to_datetimeConverts a string or other value to a datetime (timestamp with time zone).
to_datetime(val: any) -> datetime
Example:
select to_datetime('2024-01-01');
select to_datetime('2024-01-01T12:00:00Z');
SQL equivalent: CAST('2024-01-01' AS timestamp with time zone)
to_durationConverts a string to a duration (interval).
to_duration(val: any) -> duration
Example:
select to_duration('PT1H');
select to_duration('P1DT12H');
SQL equivalent: CAST('PT1H' AS interval)
cal_to_local_dateConverts a value to a local date (date without time zone information).
cal_to_local_date(val: any) -> cal::local_date
Example:
select cal::to_local_date('2024-01-01');
SQL equivalent: CAST('2024-01-01' AS date)
cal_to_local_timeConverts a value to a local time (time without time zone information).
cal_to_local_time(val: any) -> cal::local_time
Example:
select cal::to_local_time('12:00:00');
SQL equivalent: CAST('12:00:00' AS time without time zone)
cal_to_local_datetimeConverts a value to a local datetime (timestamp without time zone information).
cal_to_local_datetime(val: any) -> cal::local_datetime
Example:
select cal::to_local_datetime('2024-01-01T12:00:00');
SQL equivalent: CAST('2024-01-01T12:00:00' AS timestamp without time zone)
Window functions operate over a partition (window) of rows and are used with the OVER clause. These functions cannot be called without a window specification.
row_numberReturns the sequential number of the current row within its partition, starting from 1.
row_number() -> int64
Example:
select User {
name,
row_num := row_number() over (order by .name)
};
SQL equivalent: ROW_NUMBER() OVER (...)
rankReturns the rank of the current row within its partition, with gaps for ties.
rank() -> int64
Example:
select Student {
name,
score_rank := rank() over (order by .score desc)
};
SQL equivalent: RANK() OVER (...)
dense_rankReturns the rank of the current row within its partition, without gaps for ties.
dense_rank() -> int64
Example:
select Student {
name,
score_rank := dense_rank() over (order by .score desc)
};
SQL equivalent: DENSE_RANK() OVER (...)
ntileDistributes the rows in an ordered partition into a specified number of groups (buckets).
ntile(n: int64) -> int64
Example:
select Product {
name,
price_quartile := ntile(4) over (order by .price)
};
SQL equivalent: NTILE(4) OVER (...)
lagReturns the value of an expression evaluated at the row that is a given offset before the current row within the partition. Returns default (or null) if no such row exists.
lag(expr: any, offset?: int64, default?: any) -> any
Example:
select Sale {
date,
amount,
prev_amount := lag(.amount, 1) over (order by .date)
};
SQL equivalent: LAG(amount, 1) OVER (...)
leadReturns the value of an expression evaluated at the row that is a given offset after the current row within the partition. Returns default (or null) if no such row exists.
lead(expr: any, offset?: int64, default?: any) -> any
Example:
select Sale {
date,
amount,
next_amount := lead(.amount, 1) over (order by .date)
};
SQL equivalent: LEAD(amount, 1) OVER (...)
first_valueReturns the value of an expression evaluated at the first row of the window frame.
first_value(expr: any) -> any
Example:
select Sale {
date,
amount,
first_sale := first_value(.amount) over (order by .date)
};
SQL equivalent: FIRST_VALUE(amount) OVER (...)
last_valueReturns the value of an expression evaluated at the last row of the window frame.
last_value(expr: any) -> any
Example:
select Sale {
date,
amount,
last_sale := last_value(.amount) over (order by .date)
};
SQL equivalent: LAST_VALUE(amount) OVER (...)
bytes_get_bitReturns the value of a specific bit (0 or 1) at the given index in a byte string.
bytes_get_bit(val: bytes, index: int64) -> int64
Example:
select bytes_get_bit(b'\xff', 0);
# => 1
SQL equivalent: GET_BIT(..., 0)
bytes_to_strConverts a byte string to a text string using the specified encoding.
bytes_to_str(val: bytes, encoding: str) -> str
Example:
select bytes_to_str(b'hello', 'UTF8');
# => 'hello'
SQL equivalent: CONVERT_FROM(..., 'UTF8')
Range functions operate on range types, which represent a span of values with optional inclusive/exclusive bounds.
rangeCreates a range from a lower and upper bound.
range(lower: any, upper: any) -> range
Example:
select range(1, 10);
range_get_lowerReturns the lower bound of a range.
range_get_lower(r: range) -> any
Example:
select range_get_lower(range(1, 10));
# => 1
SQL equivalent: LOWER(int4range(1, 10))
range_get_upperReturns the upper bound of a range.
range_get_upper(r: range) -> any
Example:
select range_get_upper(range(1, 10));
# => 10
SQL equivalent: UPPER(int4range(1, 10))
range_is_emptyReturns true if the range is empty.
range_is_empty(r: range) -> bool
Example:
select range_is_empty(range(1, 1));
# => true
SQL equivalent: ISEMPTY(int4range(1, 1))
range_unpackUnpacks a range into a set of its individual values (for discrete ranges like integer ranges).
range_unpack(r: range) -> set of any
Example:
select range_unpack(range(1, 5));
# => {1, 2, 3, 4}
SQL equivalent: UNNEST(int4range(1, 5))
range_is_inclusive_lowerReturns true if the lower bound of the range is inclusive.
range_is_inclusive_lower(r: range) -> bool
Example:
select range_is_inclusive_lower(range(1, 10));
# => true
SQL equivalent: LOWER_INC(int4range(1, 10))
range_is_inclusive_upperReturns true if the upper bound of the range is inclusive.
range_is_inclusive_upper(r: range) -> bool
Example:
select range_is_inclusive_upper(range(1, 10));
# => false
SQL equivalent: UPPER_INC(int4range(1, 10))
multirangeCreates a multirange from one or more ranges.
multirange(r: range) -> multirange
Example:
select multirange(range(1, 5));
overlapsReturns true if two ranges overlap.
overlaps(r1: range, r2: range) -> bool
Example:
select overlaps(range(1, 5), range(3, 8));
# => true
select overlaps(range(1, 3), range(5, 8));
# => false
SQL equivalent: r1 && r2 (the && overlap operator)
contains (range overload)Returns true if a range contains an element. This is the range overload of the contains function.
contains(r: range, elem: any) -> bool
Example:
select contains(range(1, 10), 5);
# => true
SQL equivalent: r @> elem (the @> containment operator)
Sequence functions interact with PostgreSQL sequences for generating auto-incrementing values.
sequence_nextReturns the next value from a named sequence.
sequence_next(name: str) -> int64
Example:
select sequence_next('my_seq');
# => 1 (then 2, 3, etc.)
SQL equivalent: NEXTVAL('my_seq')
sequence_resetResets a named sequence to a specified value.
sequence_reset(name: str, val: int64) -> int64
Example:
select sequence_reset('my_seq', 1);
SQL equivalent: SETVAL('my_seq', 1)
Schema introspection functions provide runtime access to the database schema. They are resolved at compile time and return JSON representations of schema metadata.
schema::typesReturns a JSON array describing all object types in the schema.
schema::types() -> json
Example:
select schema::types();
schema::get_typeReturns a JSON object describing a specific type by name.
schema::get_type(name: str) -> json
Example:
select schema::get_type('User');
schema::functionsReturns a JSON array describing all registered functions.
schema::functions() -> json
Example:
select schema::functions();
Full-text search functions are part of the fts extension (ext::fts). They require an fts::index to be defined on the target type in your schema.
fts::searchReturns true if a row’s full-text search vector matches the given query string. Typically used in filter clauses.
fts::search(query: str) -> bool
Example:
select Article {
title,
body
} filter fts::search('database migration');
SQL equivalent: fts_vector @@ plainto_tsquery('english', 'database migration')
The query string is parsed using PostgreSQL’s plainto_tsquery with the English language configuration by default.
fts::rankReturns a relevance score for a full-text search query against a row’s search vector. Higher values indicate better matches. Useful for ordering search results.
fts::rank(query: str) -> float64
Example:
select Article {
title,
body,
relevance := fts::rank('database migration')
}
filter fts::search('database migration')
order by fts::rank('database migration') desc;
SQL equivalent: ts_rank(fts_vector, plainto_tsquery('english', 'database migration'))
| Category | Functions |
|---|---|
| String | len, str_lower, str_upper, str_title, str_trim, str_ltrim, str_rtrim, str_repeat, str_replace, str_split, str_starts_with, str_ends_with, str_pad_start, str_pad_end, contains, find |
| Regex | re_match, re_match_all, re_replace, re_test |
| Math | random, math_abs, math_ceil, math_floor, round, math_sqrt, math_pow, math_power, math_log, math_ln, math_log10, math_log2, math_pi, math_e, math_mean |
| Aggregate | count, sum, min, max, avg, stddev, stddev_pop, stddev_samp, array_agg, any, all |
| Type Conversion | to_str, to_int16, to_int32, to_int64, to_float32, to_float64, to_bigint, to_decimal, to_bool, to_uuid |
| JSON | to_json, json_typeof, json_get, json_array_unpack, json_object_unpack |
| Array | array_agg, array_unpack, array_join, array_get |
| Set | distinct, exists, enumerate, any, all |
| Assertion | assert_exists, assert_single |
| UUID | disc_uuidv7, uuid_generate_v4, uuid_generate_v1mc |
| Datetime | datetime_current, datetime_of_transaction, datetime_of_statement, datetime_get, datetime_truncate, to_datetime, to_duration |
| Calendar | cal_to_local_date, cal_to_local_time, cal_to_local_datetime |
| Window | row_number, rank, dense_rank, ntile, lag, lead, first_value, last_value |
| Bytes | bytes_get_bit, bytes_to_str |
| Range | range, range_get_lower, range_get_upper, range_is_empty, range_unpack, range_is_inclusive_lower, range_is_inclusive_upper, multirange, overlaps, contains (overload) |
| Sequence | sequence_next, sequence_reset |
| Introspection | schema::types, schema::get_type, schema::functions |
| Full-Text Search | fts::search, fts::rank |