Module expr

Expand description

Type-safe SQL expressions: comparisons, logic, aggregates and functions.

Every expression carries three facts in its type:

its SQL type, such as the dialect’s integer or text type;
whether it can be NULL (NonNull or Null);
whether it is a plain value or an aggregate (Scalar or Agg).

The functions in this module read those facts from their arguments and compute them for their result. Comparing a number with text, summing a text column, or calling a PostgreSQL-only function on SQLite fails to compile. Expressions also record which tables they read (ExprSources); a query checks that against its FROM/JOIN scope.

Rust values (i32, &str, bool, …) can be used wherever an expression is expected. They are sent as bound parameters (? on SQLite and MySQL, $1, $2, … on PostgreSQL).

The examples in this module use a users table with the columns id, age (integer), name (text), email (nullable text), score (nullable real), active (boolean) and created_at (timestamp).

§Examples

let adults = and(gt(users.age, 18), like(users.name, "A%"));
assert_eq!(adults.sql(), r#"("users"."age" > ? AND "users"."name" LIKE ?)"#);

// Arithmetic uses the Rust operators.
let next_year = users.age.clone() + 1;
assert_eq!(next_year.sql(), r#""users"."age" + ?"#);

Comparing an integer column with text does not compile:

let wrong = eq(users.age, "hello");

Structs§

Agg: Aggregate marker: an aggregate expression such as COUNT(...) or SUM(...).
AliasedExpr: An expression renamed with AS "name"; created by alias or AliasExt::alias.
AllAgg: SELECT-list status: every selected expression is an aggregate.
AllScalar: SELECT-list status: every selected expression is scalar.
CaseBuilder: A CASE builder with at least one branch.
CaseInit: A CASE builder before its first branch; created by case.
Excluded: A column of the row that failed to insert (EXCLUDED."column"); created by excluded.
MixedAgg: SELECT-list status: the list mixes scalar and aggregate expressions.
NamedExpr: An expression whose output name is a type-level Tag; created by AliasExt::named.
NonNull: Nullability marker: the expression is never NULL.
Null: Nullability marker: the expression can be NULL.
SQLExpr: A SQL fragment together with its type information.
Scalar: Aggregate marker: a plain per-row expression (not an aggregate).
WindowFnExpr: A window function that still needs its OVER (...) clause.
WindowSpec: The contents of an OVER (...) clause; start one with window.

Enums§

FrameBound: One end of a window frame, for WindowSpec::rows_between and WindowSpec::range_between.

Traits§

AggregateKind: Type-level marker saying whether an expression is an aggregate.
AggregatePolicy: Result types of sum and avg for a numeric SQL type on dialect D.
AliasExt: Method syntax for naming a selected expression.
BooleanAggregatePolicy: SQL types that BOOL_AND, BOOL_OR and EVERY accept on dialect D.
CastTarget: The target argument of cast: a SQL type name such as "VARCHAR(255)", or a type marker value whose DefaultCastTypeName is used.
CastTypePolicy: Casts from Source to Target that dialect D allows.
CombineAggStatus: Combines the SELECT-list statuses of two expressions.
ComparisonOperand: A value that can be the right-hand side of a comparison with L.
ConditionList: A tuple of conditions that all and any can combine.
DateTruncPolicy: Temporal types that date_trunc accepts on dialect D, and its result type. On PostgreSQL, timestamp and timestamptz are accepted and keep their type.
DefaultCastTypeName: The SQL type name cast writes when given a type marker, such as "INTEGER" for SQLite’s Integer.
Expr: A typed SQL expression.
ExprExt: Method syntax for comparisons: users.age.gt(18) instead of gt(users.age, 18).
ExprSources: The tables an expression reads, as a type-level tree.
GreatestLeastPolicy: How GREATEST and LEAST treat NULL on a dialect.
HasAggStatus: The SELECT-list status of a type that can appear in a SELECT list.
InSubqueryLhs: Left side of in_subquery and not_in_subquery.
InstrPolicy: Dialects that provide instr (SQLite and MySQL), and its result type.
LengthPolicy: Result type of length, char_length and octet_length for a text SQL type on dialect D: INTEGER on SQLite, int4 on PostgreSQL, BIGINT on MySQL.
Log2Policy: Dialects that provide LOG2, and the nullability of its result.
MathExt: Dialects that provide the optional math functions.
Nullability: Type-level marker saying whether an expression can be NULL.
RoundingPolicy: Result type of round, round_to, ceil, floor and trunc for a numeric SQL type on dialect D.
SelectQuery: A complete SELECT statement, accepted by exists and not_exists.
StatisticalAggregatePolicy: Result types of the standard deviation and variance aggregates for a numeric SQL type on dialect D.
SubqueryType: Maps a select marker (the type-level record of what a query selects) to the SQL type the query produces as a subquery.

Functions§

abs: Absolute value (ABS).
age: The interval between two timestamps (AGE(a, b), that is a - b), on PostgreSQL.
alias: Renames an expression: expr AS "name".
all: Logical AND of every condition in a tuple.
and: Logical AND of two conditions.
any: Logical OR of every condition in a tuple.
array_agg: Collects values into a SQL array (ARRAY_AGG), on PostgreSQL.
avg: Average of numeric values (AVG).
avg_distinct: Average of distinct numeric values (AVG(DISTINCT expr)).
between: Range check (BETWEEN), inclusive on both ends.
bool_and: True when every non-NULL input is true (BOOL_AND), on PostgreSQL.
bool_or: True when any non-NULL input is true (BOOL_OR), on PostgreSQL.
case: Starts a searched CASE expression.
cast: Converts an expression to another SQL type (CAST(expr AS type)).
ceil: Rounds up to the nearest integer (CEIL).
char_length: Number of characters in a text value.
clock_timestamp: The actual wall-clock time (CLOCK_TIMESTAMP()), on PostgreSQL.
coalesce: The first non-NULL of two values (COALESCE(expr, default)).
coalesce_many: The first non-NULL of several values (COALESCE(first, rest...)).
collate: Applies a collation to a text expression (expr COLLATE "name").
concat: Joins two text values.
concat_ws: Joins values with a separator, skipping NULLs (CONCAT_WS).
count: Row or value count (COUNT).
count_distinct: Count of distinct non-NULL values (COUNT(DISTINCT expr)).
cume_dist: Fraction of rows ordered at or before this row (CUME_DIST()).
current_date: The current date (CURRENT_DATE), on every dialect.
current_time: The current time (CURRENT_TIME), on every dialect.
current_timestamp: The current date and time (CURRENT_TIMESTAMP), on every dialect.
currval: The value most recently returned by NEXTVAL for a sequence in this session (CURRVAL), on PostgreSQL.
date: The date part of a time value (DATE), on SQLite.
date_bin: Rounds a timestamp down into fixed-size buckets (DATE_BIN), on PostgreSQL 14+.
date_trunc: Truncates a timestamp to a unit (DATE_TRUNC(unit, expr)), on PostgreSQL.
datetime: A time value as YYYY-MM-DD HH:MM:SS (DATETIME), on SQLite.
dense_rank: Rank of the row, without gaps after ties (DENSE_RANK()).
distinct: Prefixes an expression with DISTINCT.
eq: Equality comparison (=).
every: True when every non-NULL input is true (EVERY), on PostgreSQL.
excluded: Refers to the value a conflicting insert tried to write (EXCLUDED."column").
exists: Whether a subquery returns any row (EXISTS (SELECT ...)).
exp: e raised to the argument (EXP).
extract: One field of a time value (EXTRACT(field FROM expr)), on PostgreSQL.
first_value: The value of expr in the first row of the frame (FIRST_VALUE(expr)).
floor: Rounds down to the nearest integer (FLOOR).
greatest: The larger of two values (GREATEST(left, right)), on PostgreSQL and MySQL.
group_concat: Joins text values with commas (GROUP_CONCAT), on SQLite and MySQL.
gt: Greater-than comparison (>).
gte: Greater-than-or-equal comparison (>=).
ifnull: The first value, or default when it is NULL (IFNULL(expr, default)).
in_array: Membership in a list of values (expr IN (v1, v2, ...)).
in_subquery: Membership in a subquery’s rows (lhs IN (SELECT ...)).
initcap: Capitalizes the first letter of each word (INITCAP), on PostgreSQL.
instr: Position of search within a text value (INSTR), on SQLite and MySQL.
is_distinct_from: NULL-safe inequality (IS DISTINCT FROM).
is_false: Falsity test (IS FALSE) that never returns NULL.
is_not_distinct_from: NULL-safe equality (IS NOT DISTINCT FROM).
is_not_null: Non-NULL check (IS NOT NULL).
is_null: NULL check (IS NULL).
is_true: Truth test (IS TRUE) that never returns NULL.
json_agg: Collects values into a JSON array (JSON_AGG), on PostgreSQL.
json_object_agg: Collects key/value pairs into a JSON object (JSON_OBJECT_AGG), on PostgreSQL.
jsonb_agg: Collects values into a JSONB array (JSONB_AGG), on PostgreSQL.
jsonb_object_agg: Collects key/value pairs into a JSONB object (JSONB_OBJECT_AGG), on PostgreSQL.
julianday: A time value as a Julian day number (JULIANDAY), on SQLite.
lag: The value of expr in the previous row (LAG(expr)).
lag_with_default: The value of expr offset rows back, or default (LAG(expr, offset, default)).
last_value: The value of expr in the last row of the frame (LAST_VALUE(expr)).
lead: The value of expr in the next row (LEAD(expr)).
lead_with_default: The value of expr offset rows ahead, or default (LEAD(expr, offset, default)).
least: The smaller of two values (LEAST(left, right)), on PostgreSQL and MySQL.
left: The first n characters of a text value (LEFT), on PostgreSQL and MySQL.
length: Length of a text value (LENGTH).
like: Pattern match (LIKE).
ln: Natural logarithm (LN).
localtime: The current time without time zone (LOCALTIME), on PostgreSQL.
localtimestamp: The current date and time without time zone (LOCALTIMESTAMP), on PostgreSQL.
log: Logarithm of value in base base (LOG(base, value)).
log2: Base-2 logarithm (LOG2), on SQLite and MySQL.
log10: Base-10 logarithm (LOG10).
lower: Converts text to lower case (LOWER).
lpad: Pads a text value on the left to length characters (LPAD), on PostgreSQL and MySQL.
lt: Less-than comparison (<).
lte: Less-than-or-equal comparison (<=).
ltrim: Removes leading spaces (LTRIM).
make_date: Builds a date from year, month and day (MAKE_DATE), on PostgreSQL.
make_timestamp: Builds a timestamp from its parts (MAKE_TIMESTAMP), on PostgreSQL.
max: Largest value (MAX).
min: Smallest value (MIN).
mod_: Remainder of a division, rendered with the % operator.
ne: Inequality comparison (<>).
neq: Inequality comparison (<>); same as ne.
nextval: Advances a sequence and returns the new value (NEXTVAL), on PostgreSQL.
not: Logical negation (NOT).
not_between: Negated range check (NOT BETWEEN).
not_exists: Whether a subquery returns no rows (NOT EXISTS (SELECT ...)).
not_in_array: Non-membership in a list of values (expr NOT IN (v1, v2, ...)).
not_in_subquery: Non-membership in a subquery’s rows (lhs NOT IN (SELECT ...)).
not_like: Negated pattern match (NOT LIKE).
now: The current date and time (NOW()), on PostgreSQL.
nth_value: The value of expr in the n-th row of the frame, from 1 (NTH_VALUE(expr, n)).
ntile: Splits the partition into n groups of nearly equal size (NTILE(n)).
nullif: NULL when two values are equal, otherwise the first (NULLIF(a, b)).
octet_length: Number of bytes in a text value (OCTET_LENGTH).
or: Logical OR of two conditions.
percent_rank: Relative rank: (rank - 1) / (rows - 1) (PERCENT_RANK()).
pi: The constant pi (PI()).
power: base raised to exponent (POWER).
random: A random value.
rank: Rank of the row, with gaps after ties (RANK()).
raw: A raw SQL fragment with a declared SQL type, typed as nullable.
raw_non_null: A raw SQL fragment with a declared SQL type, typed as non-null.
raw_nullable: A raw SQL fragment with a declared SQL type, typed as nullable; same as raw.
regexp_match: Capture groups of the first POSIX regular expression match (REGEXP_MATCH), on PostgreSQL.
regexp_match_flags: Capture groups of the first POSIX regular expression match, with flags, on PostgreSQL.
regexp_replace: Replaces the first POSIX regular expression match (REGEXP_REPLACE), on PostgreSQL.
regexp_replace_flags: Replaces POSIX regular expression matches, with flags, on PostgreSQL.
repeat: Repeats a text value n times (REPEAT), on PostgreSQL and MySQL.
replace: Replaces every occurrence of from with to (REPLACE).
reverse: Reverses a text value (REVERSE), on PostgreSQL and MySQL.
right: The last n characters of a text value (RIGHT), on PostgreSQL and MySQL.
round: Rounds to the nearest integer (ROUND(expr)).
round_to: Rounds to precision decimal places (ROUND(expr, precision)).
row_number: Number of the row within its partition, from 1 (ROW_NUMBER()).
rpad: Pads a text value on the right to length characters (RPAD), on PostgreSQL and MySQL.
rtrim: Removes trailing spaces (RTRIM).
setval: Sets a sequence’s current value (SETVAL), on PostgreSQL.
sign: Sign of a number: -1, 0 or 1 (SIGN).
split_part: The n-th field of a text value split on delimiter (SPLIT_PART), on PostgreSQL.
sqrt: Square root (SQRT).
starts_with: Whether a text value starts with prefix (STARTS_WITH), on PostgreSQL.
stddev_pop: Population standard deviation (STDDEV_POP), on PostgreSQL and MySQL.
stddev_samp: Sample standard deviation (STDDEV_SAMP), on PostgreSQL and MySQL.
strftime: Formats a time value as text (STRFTIME(format, expr)), on SQLite.
string_agg: Joins text values with a delimiter (STRING_AGG), on PostgreSQL.
string_concat: Joins two text values; same as concat.
strpos: Position of search within a text value (STRPOS), on PostgreSQL.
substr: Part of a text value (SUBSTR(expr, start, len)).
sum: Sum of numeric values (SUM).
sum_distinct: Sum of distinct numeric values (SUM(DISTINCT expr)).
time: The time part of a time value (TIME), on SQLite.
timediff: The difference between two time values as text (TIMEDIFF), on SQLite 3.43+.
to_char: Formats a time value as text (TO_CHAR(expr, format)), on PostgreSQL.
to_date: Parses text into a date using a format (TO_DATE(text, format)), on PostgreSQL.
to_number: Parses text into a number using a format (TO_NUMBER(text, format)), on PostgreSQL.
to_timestamp: Converts Unix time in seconds to a timestamp (TO_TIMESTAMP), on PostgreSQL.
total: Floating-point sum that is never NULL (TOTAL), on SQLite.
translate: Replaces characters one for one (TRANSLATE), on PostgreSQL.
trim: Removes leading and trailing spaces (TRIM).
trunc: Truncates toward zero.
typeof: Same as typeof_, spelled with a raw identifier.
typeof_: The storage class of a value as text (TYPEOF), on SQLite.
unixepoch: A time value as seconds since 1970-01-01 (UNIXEPOCH), on SQLite 3.38+.
upper: Converts text to upper case (UPPER).
var_pop: Population variance (VAR_POP), on PostgreSQL and MySQL.
var_samp: Sample variance (VAR_SAMP), on PostgreSQL and MySQL.
variance: Sample variance: VARIANCE on PostgreSQL, VAR_SAMP on MySQL.
window: Starts an empty window specification (OVER ()).

Type Aliases§

AggExpr: A non-null, aggregate SQLExpr.
NullableAggExpr: A nullable, aggregate SQLExpr.
NullableExpr: A nullable, scalar SQLExpr.
ScalarExpr: A non-null, scalar SQLExpr.

Module expr

Module expr Copy item path

§Examples

Structs§

Enums§

Traits§

Functions§

Type Aliases§

Module expr