Expressions and functions
Expressions appear in filter, derive, having, select, sort, group keys, aggregate arguments, and join conditions.
Literals and identifiers
| Form | Examples | Notes |
|---|---|---|
| Numbers | 42, 12.5, 007 | Integers and decimals; no leading sign (unary - applies). |
| Strings | 'Ada', "Ada", 'it''s', 'a\nb' | Single- or double-quoted, single line; escapes \\ \' \" \n; the formatter canonicalizes to single quotes. |
| Booleans and NULL | true, false, null | Case-insensitive; formatted as TRUE/FALSE/NULL. |
| Identifiers | quantity, orders.customer | Unquoted names match [A-Za-z_][A-Za-z0-9_]*; dots qualify a source label. |
| Quoted identifiers | `order`, `Order Date`, `2024_sales` | Backticks allow reserved words, spaces, and digit-leading names; double the backtick to escape it; quote each dotted part separately. |
Operators
| Category | Operators |
|---|---|
| Arithmetic | + - * / %, unary + - |
| Comparison | = == != <> < <= > >= |
| Logic | and or not |
| NULL checks | is null, is not null |
| Membership / ranges | in (...), not in (...), between a and b, not between ... |
| Patterns | like, not like (% and _, case-sensitive) |
| Concatenation | || |
Precedence, lowest to highest: or → and → not → comparisons → + - || → * / % → unary + -. Chained comparisons (a < b < c) are rejected; combine them with and or use between. A prefix not negates the whole comparison: not a in (1, 2) means NOT (a IN (1, 2)).
Conditional and conversion
case when quantity >= 6 then 'bulk' when quantity >= 3 then 'medium' else 'small' end cast(quantity as integer) cast(price_text as double)
Type names for cast follow DuckDB (integer, double, varchar, boolean, date, timestamp, ...).
Aggregate functions
| Call | Result |
|---|---|
COUNT(*) | Rows in the group. |
COUNT(expr) | Non-NULL values. |
COUNT(DISTINCT expr) | Distinct non-NULL values. |
SUM, AVG, MIN, MAX | Numeric or comparable aggregates; NULLs are ignored, empty groups yield NULL (COUNT yields 0). |
Aggregates are valid only inside an aggregate stage and must be named: revenue = SUM(line_total). Later stages reference the name.
Window functions
window rank = ROW_NUMBER() over (partition by customer order by quantity desc, order_id) window total = SUM(line_total) over ()
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and the aggregates SUM, AVG, COUNT, MIN, MAX. over () computes over the whole relation (grand totals); COUNT(*) over (...) is the only wildcard form. Frame clauses are not implemented yet.
Scalar functions
| Function | Description |
|---|---|
COALESCE(a, b, ...) | First non-NULL argument. |
NULLIF(a, b) | NULL when a = b, else a. |
LOWER(s), UPPER(s) | Case conversion. |
TRIM(s) | Strip surrounding whitespace. |
LENGTH(s) | String length. |
CONCAT(a, b, ...) | Concatenate values (skips NULLs). |
ABS(n), ROUND(n, digits) | Numeric helpers. |
strftime(date, format) | Format a date/timestamp, e.g. strftime(order_date, '%Y-%m'). |
date_trunc(part, date) | Truncate to day, month, year, ... |
Function and aggregate names are not reserved and are case-insensitive. Use the sql hatch for anything outside this list.