Querying

SELECT supports the usual shape: WHERE, ORDER BY, LIMIT / OFFSET, DISTINCT, joins, GROUP BY with HAVING, set operations, subqueries, and WITH (common table expressions). An ORDER BY key is a general expression (ORDER BY a + b, ORDER BY abs(x), ORDER BY sum(x) in a grouped query — a column name is its simplest form), an output-column alias / name (SELECT a + b AS s … ORDER BY s sorts by the aliased value — a bare name binds an output column before an input column, matching PostgreSQL), or an output-column position (ORDER BY 1 sorts by the first select-list item), each with optional ASC / DESC and NULLS FIRST / NULLS LAST. Aggregates use PostgreSQL-style widening (for example, sum over numeric returns numeric, exact), and accept a leading DISTINCT (count(DISTINCT x)) and a trailing FILTER (WHERE …) (count(*) FILTER (WHERE x > 0)). GROUP BY also takes GROUPING SETS, ROLLUP, and CUBE to compute several groupings at once, with the GROUPING(col) function to tell subtotal rows apart, and groups by an output column, a select-list position (GROUP BY 1), or any expression (GROUP BY a + b), not just a column. The ordered-set aggregates mode, percentile_cont, and percentile_disc are written with a WITHIN GROUP (ORDER BY …) clause (for example, percentile_cont(0.5) WITHIN GROUP (ORDER BY x) for the median), and the hypothetical-set aggregates rank, dense_rank, percent_rank, and cume_dist take a hypothetical row the same way (rank(50) WITHIN GROUP (ORDER BY score)). FILTER (WHERE …) and these aggregate forms also compose with window functions (OVER).

Grouping and aggregation:

Filtering and ordering — edit the WHERE and ORDER BY and re-run:

Common table expressions (WITH)

A WITH clause names a query and exposes it to the FROM clause like a table. Define one or more — each is visible to later ones and to the main query:

CTEs follow PostgreSQL’s evaluation rule: a CTE referenced once is inlined, one referenced several times (or marked MATERIALIZED) runs once and its rows are buffered and reused. Add an optional column-rename list (WITH t (a, b) AS (…)).

Data-modifying CTEs

A CTE’s body — and the statement a WITH prefixes — may be an INSERT, UPDATE, or DELETE. Its RETURNING rows flow forward like any CTE’s, so one statement can write and read its own changes:

The canonical use is moving rows between tables atomically:

WITH moved AS (DELETE FROM inbox WHERE ready RETURNING *)
INSERT INTO archive SELECT * FROM moved;

Every part of the statement reads one pre-statement snapshot — the parts cannot see each other’s effects on the target tables, so data crosses only through a CTE’s RETURNING buffer (SELECT count(*) FROM inbox next to the DELETE above still sees the rows). The data-modifying parts run once each, in order, and the whole statement is one all-or-nothing transaction.

Recursive CTEs (WITH RECURSIVE)

WITH RECURSIVE lets a CTE reference itself, computing a result by iterating to a fixpoint — hierarchy walks, graph reachability, generated series. The body is a UNION: the left side (the non-recursive term) seeds the result, and the right side (the recursive term) is re-evaluated against the rows the previous step produced until it yields none:

UNION ALL keeps every row; UNION drops rows that duplicate one already produced (which is what makes a cyclic graph walk terminate). The column types are fixed by the non-recursive term. A recursion with no stopping condition runs until it hits the statement’s cost ceiling — set one when running untrusted queries. SEARCH / CYCLE clauses and mutual recursion are not yet supported.

Nested WITH

A WITH clause may also prefix any parenthesized query — a subquery, a derived table, or another CTE’s body — so you can scope helper CTEs to just the part of the statement that needs them. The nested CTEs (including WITH RECURSIVE) get the full machinery above:

A nested WITH establishes its own scope: its CTEs are visible only inside that parenthesized query. One difference from PostgreSQL: a nested WITH does not inherit the enclosing statement’s CTEs — an outer CTE name referenced inside an inner WITH resolves to a base table (or is an error), not the outer CTE. Data-modifying CTEs (INSERT/UPDATE/DELETE) stay top-level only, as in PostgreSQL.

Derived tables (FROM (SELECT …) AS t)

A FROM item can be a parenthesized subquery used as a relation — a derived table. It is an anonymous, always-inlined single-reference CTE: the body runs in place, and you reference its output columns through the alias. The alias is optional (matching PostgreSQL 18); when present it may carry a column-rename list (AS t (a, b)):

By default the body is an independent query — it cannot see the enclosing query’s other FROM relations. Mark it LATERAL (below) to correlate it. A parenthesized join (FROM (a JOIN b …)) and a WITH body are not yet supported.

VALUES lists in FROM (FROM (VALUES …) AS v(x))

A derived table’s body may also be a VALUES list — a literal table of rows you write inline, without a base table. The columns default to column1, column2, … (overridable by the alias’s column-rename list), and each column’s type unifies across the rows the way UNION does. Each value is a general constant expression (qty * 2 below):

Order or limit the outer query (FROM (VALUES …) v ORDER BY x); a trailing ORDER BY / LIMIT inside the parentheses is not yet supported.

LATERAL joins

A LATERAL FROM item may reference columns of the FROM relations that appear before it — a dependent join re-evaluated once per left-hand row. It is the idiomatic way to express top-N-per-group: for each category, the single most expensive product.

Reach it through explicit join syntax — CROSS JOIN LATERAL, JOIN LATERAL … ON …, or LEFT JOIN LATERAL … ON … (a LEFT JOIN keeps a left row even when the lateral side produces no rows) — or through a comma: FROM t, LATERAL (…) is the same as t CROSS JOIN LATERAL (…). A sub-SELECT or VALUES body is lateral only with the keyword; a table function (generate_series, unnest) is implicitly lateral, so … CROSS JOIN unnest(t.tags) correlates with or without it.

A comma in FROM is an implicit CROSS JOIN generally — FROM a, b is a CROSS JOIN b. The comma binds looser than JOIN, so each comma-separated item is its own join group: a join’s ON may reference only relations inside its own group (FROM a, b JOIN c ON a.x = c.x is an error), matching PostgreSQL.

To select all columns of one relation in a join, use a qualified star t.* — the per-table counterpart of *. Unlike bare *, it can be mixed with other items: SELECT a.*, b.label FROM a JOIN b ON … expands all of a’s columns followed by b.label.

JOIN ... USING (col, …) is an equi-join on the named columns that merges each into a single output column — a JOIN b USING (k) joins on a.k = b.k and the result carries one k (listed first, before the other columns). A bare k then refers to the merged column; a.k and b.k still reach each side. It works with LEFT/RIGHT joins and multi-column lists; FULL JOIN ... USING is not yet supported.

NATURAL JOIN is the shorthand: a NATURAL JOIN b joins on every column the two tables share by name (merging each, like USING). With no shared column it is a CROSS JOIN. It composes with LEFT/RIGHT (a NATURAL LEFT JOIN b); NATURAL FULL JOIN is not yet supported.

Set-returning functions in FROM

A FROM item can be a set-returning function — a computed row source instead of a stored table. generate_series(start, stop[, step]) yields an integer series; unnest(anyarray) expands an array into one row per element (a multidimensional array flattens, a NULL element becomes a NULL row, and a NULL or empty array yields no rows). The produced relation has one column, named after the function or its alias, and composes with WHERE / ORDER BY / LIMIT / joins like any other:

Array containment and overlap

The @> (contains), <@ (contained by), and && (overlaps) operators compare two arrays as sets: a @> b is true when every element of b appears in a, a && b when they share at least one element. Matching is strict — a NULL element matches nothing, including another NULL — and a NULL whole array yields NULL:

Quantified comparisons (ANY / ALL)

A comparison operator followed by ANY (or its synonym SOME) or ALL over an array tests it against every element. x = ANY(arr) is the array spelling of IN — true when x equals some element; x op ALL(arr) is true when the comparison holds for every element. Both are three-valued, exactly like IN: a NULL element (or a NULL x) makes the result NULL when no element settles it, an empty array makes ANY false and ALL true, and a NULL whole array yields NULL:

The operand may also be a subquery — x op ANY/ALL(SELECT …), the subquery spelling of IN. x = ANY(SELECT …) is exactly x IN (SELECT …); the other operators generalize it over the subquery’s single column, with the same three-valued fold (and no row-count limit):

Variadic functions (VARIADIC)

A variadic function takes a variable number of trailing arguments. num_nulls and num_nonnulls count the NULL / non-NULL arguments — either as a spread of values, or, with the VARIADIC keyword, by passing one array whose elements are the arguments. The two forms agree, and the spread form never returns NULL (it counts), while VARIADIC over a NULL array yields NULL:

Arrays of composite types

An array’s element type can be a composite type, so a column holds a list of rows: addr[] is an array of addr. Build one with the ARRAY[ROW(…)] constructor or the '{…}'::addr[] text literal, subscript it to read an element (places[1]), and reach into a field with (places[1]).street. Comparison, ORDER BY, DISTINCT, and GROUP BY all work — a NULL field inside a composite element is comparable (so two arrays with matching NULL fields are equal and sort together), unlike a bare row comparison:

unnest expands a composite array into one composite row per element — read the whole row (u) or reach into a field ((u).zip):

The array functions and operators work over composite elements too — array_append, ||, cardinality, @>/<@/&&, array_remove, and = ANY / = ALL (which compares whole rows, so a matching NULL field still counts as equal):

Composite types with array fields

The nesting works the other way too: a composite type can have an array-typed field, so one row holds a list — CREATE TYPE poly AS (name text, pts i32[]). Build a value with ROW(name, ARRAY[…]) (or write the field as a text literal, ROW(name, '{10,20,30}')), read the whole array with (p).pts, and subscript it with (p).pts[1]. Comparison and ORDER BY follow the row order field-by-field, using the array’s element-wise order for the array field:

Dates

A date is a calendar date — year/month/day, no time or zone. Write one as a quoted ISO string ('2024-01-15') that adapts to the column, or with the DATE '…' keyword literal. Dates compare and sort chronologically, and infinity / -infinity are first-class values. (A literal carrying a time keeps only the date part, so '2024-01-15 09:30:00' stores 2024-01-15.)

Cost

Cost is shown with every result. Each query accrues a deterministic cost, and a caller can set a ceiling so an expensive query aborts with 54P01 rather than running away — which is what makes it safe to run untrusted SQL.

When an ORDER BY already matches the order the data is stored in — an ascending prefix of the table’s PRIMARY KEY — jed skips the sort and reads rows straight from the table in order. Paired with a LIMIT, this becomes a top-N: the scan stops as soon as the window is full, so SELECT … ORDER BY <pk> LIMIT 100 over a million-row table reads ~100 rows, not a million. A collated primary key is stored in its collation’s order, so a collated ORDER BY is satisfied the same way, with no in-memory re-sort. (Ordering by a non-key column, or DESC, still does a full sort for now.)