SQL

Reading data

Joins, subqueries, CTEs, window functions, set operations, and grouping sets.

The examples on this page run against the schema of the console on the home page: stores, employees, products, customers, orders, order_items, and returns. Paste any of them into the console there.

Prefer a typed builder to SQL strings? The Kysely adapter compiles its PostgreSQL-style queries for the same engine.

Filtering and choosing columns

SELECT order_id, total, placed_at
FROM orders
WHERE status = 'completed'
  AND placed_at >= TIMESTAMP '2025-01-01'
  AND total BETWEEN 20 AND 500
ORDER BY placed_at DESC
LIMIT 50

WHERE supports comparison, BETWEEN, IN, LIKE, ILIKE, SIMILAR TO, IS NULL, AND / OR / NOT, and CASE. x = ANY(array) is x IN (…) over an array's members and x <> ALL(array) is NOT IN; the array is an ARRAY[…] constructor, an array column, or one bound parameter holding the whole list (see parameters):

SELECT order_id, status
FROM orders
WHERE status = ANY(ARRAY['returned', 'cancelled'])
  AND store_id <> ALL(ARRAY[1, 2])

Filters on columns with recorded value ranges can skip whole groups of rows without decoding them — see Query plans.

A string constant beside a typed value reads in that value's type, the way PostgreSQL types an untyped literal by its context: placed_at >= '2025-01-01' compares as a timestamp (a zoneless string is UTC), customer_id = '42' as a number, and refunded = 't' as a boolean. A bound parameter gets the same reading, and so does a constant beside a datetime that is not a column: NOW() > '2020-01-01', HAVING MAX(placed_at) > '2026-03-01', or a scalar subquery. Text that does not parse in that type is still a type error, so placed_at >= 'yesterday' fails rather than matching nothing. The same reading applies when a SQL statement writes a string constant or parameter into a typed column — see DML.

Unquoted identifiers resolve the way PostgreSQL folds them when no exact name exists: SELECT ID, Region FROM ORDERS reads the table orders and its columns id and region, and the output columns take the catalog's spelling. A name that matches exactly is never folded, so a table created as Orders through the typed API is still addressed as Orders; a name that matches several catalog names case-insensitively is an error. GROUP BY follows PostgreSQL's functional dependency: once a table's whole primary key is grouped, any other column of that table may appear in the select list, HAVING, or ORDER BY ungrouped (SELECT c.id, c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON … GROUP BY c.id).

String constants take PostgreSQL's spellings too: 'it''s', E'tab\there' with C-style escapes, and $tag$…$tag$ dollar quotes; 5. and .5 are numbers; ~~, !~~, ~~*, and !~~* spell LIKE, NOT LIKE, ILIKE, and NOT ILIKE; TABLE name is SELECT * FROM name; and a trailing FOR UPDATE or FOR SHARE takes no row locks. In a READ ONLY transaction, a clause that reaches a table or view is refused, including through FROM subqueries. An outer clause on a CTE reference or constant-only source locks nothing and is allowed. A referenced CTE's own locking clause counts whenever its reference count is positive, even if its only referrer is a CTE the final SELECT does not use. A zero-reference CTE contributes nothing; this rule applies separately inside each planned query scope. Both the base and step of a recursive declaration count when it has an external reference; its self-reference alone does not count. A locking-read refusal says cannot execute SELECT in a read-only transaction; the message does not report lock strength. Plans still retain the strongest effective intent after view and CTE expansion.

Casts take either spelling, CAST(total AS INTEGER) or total::INTEGER; the postfix form binds tighter than every operator, so -total::INTEGER * 2 is (-(total::INTEGER)) * 2. A double precision value cast to an integer type rounds to the nearest integer with ties to even, as PostgreSQL's cast does. Exact NUMERIC values and decimal literals round ties away from zero; integer text must contain signed decimal digits, so '1.5'::INTEGER and '1e2'::INTEGER fail. || concatenates text, and a number, boolean, or timestamp on the other side renders as text: 'order-' || order_id.

The scalar function surface follows PostgreSQL: CONCAT, CONCAT_WS, LEFT, RIGHT, REVERSE, REPEAT, INITCAP, SPLIT_PART, STRPOS, STARTS_WITH, TRANSLATE, ASCII, CHR, BTRIM, MD5, FORMAT, and REGEXP_REPLACE beside the standard UPPER, LOWER, TRIM, SUBSTRING (including the POSIX-regex SUBSTRING(text FROM 'pattern') (or SUBSTRING(text, 'pattern'))), POSITION, OVERLAY, and padding functions, plus TO_HEX, QUOTE_LITERAL, and QUOTE_IDENT; the regex operators ~, ~*, !~, and !~*; ^ for exponentiation with EXP, LN, LOG, LOG10, SIGN, TRUNC, PI, CBRT, DIV, GCD, LCM, WIDTH_BUCKET, and the trigonometric family beside ROUND, ABS, MOD, POWER (also spelled POW), and SQRT (with / dividing two integers as integers, truncating toward zero as PostgreSQL does — see integer division); NUM_NULLS and NUM_NONNULLS; and TO_CHAR, TO_DATE, TO_TIMESTAMP, DATE() (the DATE cast as a function), DATE_PART, EXTRACT, DATE_TRUNC, AGE, MAKE_DATE, MAKE_TIMESTAMP, NOW() with its transaction_timestamp(), statement_timestamp(), and clock_timestamp() aliases, and the CURRENT_* clock beside interval arithmetic. The compatibility page lists each with its example and any difference from PostgreSQL.

DIV and numeric TO_CHAR keep exact NUMERIC digits, including values beyond JavaScript's safe integer range. QUOTE_LITERAL renders public domain values and declared numeric scales; QUOTE_IDENT quotes PostgreSQL keywords. FORMAT supports %s, %I, %L, %%, and positional %n$ conversions. An explicit position sets the cursor for the next unnumbered conversion. Malformed directives and missing arguments throw; width and alignment directives are unsupported.

Regex matching selects the leftmost, longest match. It supports grouping, alternation, character classes and ASCII POSIX classes, anchors, and greedy repetition. Pattern backreferences, lookaround, inline flags, and non-greedy quantifiers throw; replacement backreferences still work. The SQL limits bound pattern size, compilation, matching, and replacement output.

Timestamp text is read as an instant: 2026-10-15 08:00:00, 2026-10-15T08:00:00.250Z, and an optional zone after the time, Z or an offset in PostgreSQL's spellings (+00, -08, +0530, +05:30, with or without a space before it). Text without a zone is UTC. Timestamp inputs and MAKE_TIMESTAMP validate calendar dates, clock fields, and offsets up to ±15:59. Invalid dates such as February 30 throw. Supported calendar years are 1–9999; years below 100 keep their requested year. Timestamp text also reads the years PostgreSQL writes outside that range, a five- or six-digit year and a BC suffix, so every timestamp Minnow writes as text reads back as the same instant. DATE input also accepts a timestamp's text and keeps its day, as PostgreSQL does: '2026-10-15 08:00:00'::date and day = '2026-10-15T08:00:00Z' both read 2026-10-15. A DATE compared with a TIMESTAMP value is midnight of its day, so day < TIMESTAMP '2026-10-15 00:00:01' holds for 2026-10-15; a Date bound to a placeholder beside a DATE column is read as that column's type, a day. Explicit TEXT casts retain text comparison semantics: comparing '1'::TEXT to the number 1 throws rather than interpreting the text as a number.

Timestamp literals and values in the current engine keep millisecond precision. Fractional digits beyond the first three are discarded before comparisons, arithmetic, or text output. For example, TIMESTAMP '2000-02-29 12:34:56.123400'::text returns 2000-02-29 12:34:56.123, while PostgreSQL returns 2000-02-29 12:34:56.1234.

Going the other way, a datetime turned into text reads as PostgreSQL writes it in a UTC session: CAST(placed_at AS TEXT) is 2025-03-14 09:30:00+00, an interval is 5 days 02:30:00, and date - date is a whole number of days, so dividing it counts whole weeks. Column types has every form, including timestamps without time zone.

SELECT r.return_id,
       CAST(r.returned_at AS TEXT) AS returned,
       r.returned_at - o.placed_at AS elapsed,
       (CAST(r.returned_at AS DATE) - CAST(o.placed_at AS DATE)) / 7 AS weeks_to_return
FROM returns r
JOIN orders o ON o.order_id = r.order_id
ORDER BY r.return_id
LIMIT 5

ORDER BY takes expressions, ASC / DESC, NULLS FIRST / NULLS LAST, ordinals, and explicit COLLATE on string expressions. A sort key may name a selected column through its source alias (SELECT id FROM orders AS o ORDER BY o.id), the way PostgreSQL resolves it. LIMIT and OFFSET both work, LIMIT 0 returns no rows, LIMIT ALL is PostgreSQL's spelling of no limit, and a LIMIT without an ORDER BY returns rows in no promised order, as in any SQL engine.

SELECT ALL names the default (every row, duplicates kept), and a column label needs no AS: SELECT total * 2 doubled FROM orders is SELECT total * 2 AS doubled FROM orders. A label is any identifier that no clause or operator keyword can claim; a quoted identifier is always a label.

* and alias.* are expanded from the input schema before Minnow plans operations that depend on the output width. They therefore work with DISTINCT, window columns, set operations, CTE/derived column lists, ORDER BY expressions and ordinals, INSERT ... SELECT, and beside other select items (SELECT *, total * 2 AS doubled FROM orders). Wildcard outputs use bare column names, as PostgreSQL returns them; only a name that two sources both contribute — id in SELECT * over a join on id — is returned as alias.column, since one result row cannot carry two columns of the same name. For the same reason, when two select items share an output name (SELECT a AS c, b AS c, or SELECT *, total FROM orders), both columns are returned and each later one is numbered: c and c_2, total and total_2.

A select item without a label is named the way PostgreSQL names it: a column keeps its name, a function call takes the function's name as written (COUNT(*) is count, ARRAY_AGG(id) is array_agg, ROW_NUMBER() OVER w is row_number), and a cast takes the name of what it casts (placed_at::date is placed_at, COUNT(*)::int is count). Any other expression is expression, numbered like any repeated name (expression, expression_2), where PostgreSQL would say ?column?.

ROUND(value, digits) takes NUMERIC in PostgreSQL. Cast a plain-number expression explicitly, as in ROUND(SUM(total)::NUMERIC, 2), for SQL that also runs there. Minnow's current engine additionally accepts a plain number with a digit count; PostgreSQL refuses that overload with 42883. This legacy overload is planned for removal when the rebuilt engine becomes the default; use the explicit cast now. The numeric form returns exact decimal text.

Joins

SELECT s.name AS store, COUNT(*) AS orders, ROUND(SUM(o.total)::NUMERIC, 2) AS revenue
FROM stores s
JOIN orders o ON o.store_id = s.store_id
LEFT JOIN employees e ON e.employee_id = o.employee_id
WHERE o.status = 'completed'
GROUP BY s.store_id, s.name
ORDER BY revenue DESC

INNER, LEFT, RIGHT, FULL, and CROSS joins are all supported, with USING as well as ON. Minnow estimates how many rows each side will produce, then orders the joins and builds its lookup table from the smaller side. Equality joins can use the unique-key lookup when one side has one.

A USING or NATURAL join merges its join columns, as PostgreSQL and SQLite do: an unqualified customer_id after JOIN customers USING (customer_id) is one column — the left side's value for an inner or left join, the right side's for a right join, and the first non-null of the two for a full join — and SELECT * lists it once, first. A later USING may name a column any earlier source supplies. The qualified o.customer_id and c.customer_id still name each side.

A parenthesized join group — FROM (orders o CROSS JOIN stores s) — is accepted as the first source or as the operand of a comma, CROSS JOIN, or INNER JOIN, whose ON condition then filters the product. A group cannot take an alias, and a LEFT, RIGHT, FULL, NATURAL, or USING join onto a group is rejected with an explicit error, since the flat join chain cannot treat the group as one side.

RIGHT JOIN must be the only join in its block and cannot be combined with SELECT * — both rejections come with explicit errors, and the mirrored LEFT JOIN is always available. FULL JOIN must also be the only join in its block. It supports wildcards, grouping, DISTINCT, window functions, and compound ON predicates. Operations over its result run after unmatched rows from both sides have been included.

An ON clause that carries more than its equality still hashes on that equality — ON b.order_id = a.order_id AND b.product_id > a.product_id builds on the order and applies the rest to the pairs it finds, rather than comparing every row with every row.

Equality and range-correlated LATERAL derived sources are decorrelated into the same set-at-a-time join path. An equality-correlated lateral query may also group, aggregate, and take a per-row ORDER BY … LIMIT: GROUP BY gains the correlation key, a global aggregate such as COUNT(*) still yields one row per outer row (0 for COUNT, NULL for the rest, exactly as PostgreSQL returns), and a LIMIT becomes a row number partitioned by the key. A range-correlated lateral query (o.amount > c.threshold) keeps the plain join and cannot group or limit.

Aggregation

SELECT p.category,
       COUNT(*) AS lines,
       COUNT(DISTINCT i.order_id) AS baskets,
       ROUND(SUM(i.line_total)::NUMERIC, 2) AS revenue,
       ROUND(AVG(i.line_total)::NUMERIC, 2) AS average_line,
       MIN(i.unit_price) AS cheapest,
       MAX(i.unit_price) AS dearest
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.category
HAVING SUM(i.line_total) > 10000
ORDER BY revenue DESC

COUNT, SUM, AVG, MIN, MAX, STRING_AGG, JSON_ARRAYAGG, the statistical spreads (VARIANCE, STDDEV, and their _POP / _SAMP forms), the ordered-set aggregates (PERCENTILE_CONT, PERCENTILE_DISC, MODE), and per-aggregate DISTINCT are available. The numeric and counting aggregates accept FILTER (WHERE …) for conditional aggregation. JSON_ARRAYAGG(value ORDER BY expression) returns JSON text, keeps SQL nulls as JSON nulls by default, and returns SQL NULL for an empty input. Its ORDER BY list follows the same direction and NULL-placement rules as query ordering. Filter its input in WHERE; FILTER is not supported for this aggregate. GROUP BY also accepts GROUPING SETS, ROLLUP, and CUBE.

SELECT DISTINCT ON (expressions) keeps the first row of each group in ORDER BY order — the PostgreSQL idiom for "the latest order per customer":

SELECT DISTINCT ON (customer_id) customer_id, order_id, placed_at
FROM orders
ORDER BY customer_id, placed_at DESC

As in PostgreSQL, a DISTINCT ON query with an ORDER BY must start it with the DISTINCT ON expressions, in any order. DISTINCT ON (customer_id) … ORDER BY placed_at DESC, customer_id fails with "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".

SELECT DISTINCT over a grouped, aggregated, or windowed block takes the distinct rows of that block's output — SELECT DISTINCT category, COUNT(*) … GROUP BY category, brand — with ORDER BY and LIMIT applied to the distinct rows, as in PostgreSQL.

GROUP BY items follow PostgreSQL's resolution: an integer is a select-list ordinal (GROUP BY 1), and a name that is an output alias but not a source column stands for the aliased expression (SELECT DATE_TRUNC('month', placed_at) AS month … GROUP BY month). A name that is both an alias and a source column is the source column, as in PostgreSQL and SQLite: SELECT LOWER(status) AS status, COUNT(*) … GROUP BY status counts each stored spelling of status separately. Group by the expression (or GROUP BY 1) to merge them. A selected expression built over a grouping expression is grouped, so FLOOR(total / 50) * 50 may be selected beside GROUP BY FLOOR(total / 50), and COALESCE(region, 'all') labels a ROLLUP total. A grouped column may be spelled bare on one side and qualified on the other (SELECT status … FROM orders o GROUP BY o.status, or SELECT o.status … GROUP BY status), and both spellings may appear in one expression; with several sources a bare name is the column of the one source that has it.

JSON constructors retain JSON provenance until the result crosses the JavaScript boundary. A constructor used inside another constructor is embedded as a document, not quoted as text:

SELECT JSON_OBJECT(
  'id' VALUE o.order_id,
  'detail' VALUE JSON_OBJECT('status' VALUE o.status),
  'items' VALUE JSON_ARRAYAGG(JSON_OBJECT('sku' VALUE p.sku))
) AS document
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
GROUP BY o.order_id, o.status

PostgreSQL's spellings are accepted too: json_agg and jsonb_agg for JSON_ARRAYAGG, json_build_object and json_build_array (and their jsonb_ twins) for JSON_OBJECT and JSON_ARRAY, and to_json, to_jsonb, and row_to_json to render one value as a document. A table alias used as a value is the row as a JSON object with the source's columns in order, so the shapes Kysely's jsonArrayFrom and jsonObjectFrom and Drizzle's relational queries emit run unchanged:

SELECT c.customer_id,
  (SELECT COALESCE(json_agg(agg), '[]')
   FROM (SELECT o.order_id, o.total FROM orders o WHERE o.customer_id = c.customer_id) agg
  ) AS orders
FROM customers c

The current engine returns compact JSON strings. Whitespace and numeric spelling can differ from PostgreSQL's JSON text; the rebuild preserves PostgreSQL's spelling. Add ORDER BY inside an aggregate when array order matters: without it, PostgreSQL may visit a group's rows in a different order.

The same rule applies through JSON_QUERY. Ordinary text that happens to contain JSON remains a JSON string; cast it to JSON/JSONB or pass it through a JSON constructor when it should embed as a document.

PostgreSQL's -> and ->> operators read back into a document. A text key selects an object member; an integer key selects an array element, counting from the end when negative. Cast an untyped document literal to JSON so PostgreSQL can resolve the operator overload:

SELECT '{"sku": "A-1", "dims": {"w": 30, "h": 40}}'::json -> 'dims' ->> 'w' AS width

-> returns a JSON value, so steps chain and a selected string keeps its quotes; ->> returns text, with strings unquoted and a selected JSON null as SQL NULL. A key of the wrong kind for the document's shape — a member name against an array, a position against an object or scalar — selects NULL. The operators accept stored JSON/JSONB values and ordinary JSON text alike, but unlike JSON_VALUE's quiet NULL, a document that is not JSON at all is an error.

STRING_AGG(value, delimiter ORDER BY expression) skips NULL values, supports DISTINCT, and spills under the same query-memory bound as other grouped aggregates.

The ordered-set aggregates take their input from WITHIN GROUP (ORDER BY …) — a median, a percentile, or the most common value per group:

SELECT p.category,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY i.line_total) AS median_line,
       PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY i.line_total) AS p90_line,
       MODE() WITHIN GROUP (ORDER BY i.quantity) AS usual_quantity
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.category
ORDER BY median_line DESC

PERCENTILE_CONT(f) interpolates between the two values around fraction f of the sorted group and returns a double; PERCENTILE_DISC(f) returns the first actual value at or past f; MODE() returns the most frequent value, a tie going to the one that sorts first. DESC in the ORDER BY reverses which end f counts from. NULL inputs are skipped, an empty group is NULL, and f must lie between 0 and 1. Each group holds its sorted input in memory, under the query's memory budget; the array-of-fractions forms and FILTER are not supported.

DISTINCT is per aggregate, not per query: each one keeps its own set of values, so a select can carry several of them beside ordinary aggregates, inside expressions, and in HAVING — COUNT(DISTINCT r.return_id) / COUNT(DISTINCT i.order_item_id) counts two different things.

An aggregate over a whole column reads only that column. This is the shape columnar storage is for: summing one column of a fourteen-column table touches a fourteenth of the bytes.

Subqueries and derived tables

SELECT category, name, revenue
FROM (
  SELECT p.category, p.name, SUM(i.line_total) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(i.line_total) DESC) AS rank
  FROM order_items i
  JOIN products p ON p.product_id = i.product_id
  GROUP BY p.category, p.name
) AS ranked
WHERE rank <= 3

A derived table needs an alias. Scalar subqueries, IN (SELECT …), and EXISTS all work. Correlated EXISTS and NOT EXISTS, including range comparisons such as inner.amount < outer.amount, decorrelate into semi-joins and anti-joins rather than executing per row. A correlated predicate nested below OR, NOT, CASE, or a select expression keeps its true, false, and unknown results without multiplying outer rows, and correlated EXISTS, IN, NOT IN, ANY, ALL, and scalar blocks can nest and sit side by side to any parser-supported depth.

Correlated scalar subqueries support aggregate expressions—including JSON_ARRAYAGG—and ordinary single-row projections. ORDER BY, LIMIT, and OFFSET are applied independently for each distinct outer probe tuple. The inner predicates, projection, and aggregate arguments may use any number of qualified outer columns; every referenced value becomes part of that probe tuple. An ordinary scalar returns NULL for no row and raises a cardinality error for more than one row. The inner scalar block cannot itself use GROUP BY or HAVING.

Membership in an empty subquery is always false (IN) or true (NOT IN), including a NULL probe. A nonempty list containing NULL still follows SQL's three-valued logic.

Correlated IN, NOT IN, ANY, and ALL preserve their empty-set and NULL behavior at top level and inside larger expressions. NOT IN carries per-probe counts rather than treating NULL as an ordinary value. A correlated scalar in a grouped outer select list may reference the outer grouping columns; it cannot be nested inside an outer aggregate or reference an ungrouped outer column.

Constant JSON documents can also produce rows:

SELECT item.sku, item.qty
FROM JSON_TABLE(
  '[{"sku":"A-1","qty":2},{"sku":"B-4","qty":1}]',
  '$[*]' COLUMNS (sku TEXT PATH '$.sku', qty INTEGER PATH '$.qty')
) AS item

JSON_TABLE accepts $ and $[*] row paths over a constant document. Correlated document expressions are unsupported.

Common table expressions

WITH monthly AS (
  SELECT DATE_TRUNC('month', placed_at) AS month, SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', placed_at)
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month

WITH RECURSIVE is supported too, for hierarchies and generated sequences:

WITH RECURSIVE months(month) AS (
  SELECT TIMESTAMP '2025-01-01'
  UNION ALL
  SELECT month + INTERVAL '1 month' FROM months WHERE month < TIMESTAMP '2025-12-01'
)
SELECT month FROM months

A CTE can name its own output columns, as months(month) does above; a recursive one takes those names before its step runs, which is how the step reads month back. DATE '2025-01-01' is a zoneless calendar date returned as YYYY-MM-DD; TIMESTAMP '2025-01-01 09:30:00' is an instant read as UTC, the way every datetime in a Minnow database is stored. INTERVAL '1 month' added to or subtracted from either does calendar arithmetic, so 31 January plus a month is the end of February. DATE ± INTERVAL always returns a timestamp, including whole-day and zero intervals. EXTRACT reads fractional seconds from timestamps and reads the stored fields of TIME and INTERVAL without converting them to dates.

Dates and timestamps do PostgreSQL's arithmetic: date - date is a whole number of days, an integer that divides as one, date + 7 and date - 7 shift a DATE by days, and timestamp - timestamp is an INTERVAL of days and time that compares, extracts, and shifts another timestamp. For a plain series of numbers or timestamps, generate_series in FROM is shorter than a recursive CTE and produces its rows in one pass — here, every day of a month with that day's order count, zero included:

SELECT day, COUNT(o.order_id) AS orders,
       EXTRACT(EPOCH FROM MAX(o.placed_at) - MIN(o.placed_at)) / 3600 AS busy_hours
FROM generate_series(TIMESTAMP '2025-03-01', TIMESTAMP '2025-03-31', INTERVAL '1 day') AS day
LEFT JOIN orders o ON DATE_TRUNC('day', o.placed_at) = day
GROUP BY day
ORDER BY day

generate_series(start, stop[, step]) counts integers by 1 unless a step is given, downward with a negative one, and walks timestamps by an INTERVAL step. AS name names its column. Its arguments are constants, parameters, or uncorrelated subqueries — not columns of another FROM item.

Window functions

SELECT customer_id,
       placed_at,
       total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total,
       RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS biggest_basket,
       LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS previous_order
FROM orders

ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and the aggregates as window functions, with PARTITION BY, ORDER BY, and explicit ROWS, RANGE, and GROUPS frames, including exclusions. Numeric RANGE offsets measure ordering-value distance and require exactly one numeric ORDER BY expression. Offsets must be numeric literals: parameters (including ROWS $1 PRECEDING), quoted offsets, and temporal interval offsets are refused. -0 is zero. A ROWS or GROUPS decimal offset is rounded half away from zero, so 1.5 PRECEDING means 2 rows.

Public RANGE distances use double precision. A fractional offset such as RANGE 1.5 PRECEDING is accepted over an integer key; PostgreSQL rejects this combination with 0A000. A positive offset outside double precision (1e400, 1e-400) is refused with "RANGE frame offsets outside double precision are not supported", even over a NUMERIC key, where PostgreSQL runs it. Exact NUMERIC distances in the unexported rebuild core are not part of the public executor.

Negative and NULL offsets are errors; PostgreSQL uses 22013 and 22004 for them. Negative RANGE offsets are rejected at parse time, including on empty or NULL-only input and under LIMIT 0; PostgreSQL checks them when comparing non-NULL keys and can return without an error if no comparison is needed. PostgreSQL also skips ROWS/GROUPS offset initialization under LIMIT 0; Minnow still validates those literals while parsing. Minnow says "frame starting offset must not be negative"; PostgreSQL says "invalid preceding or following size in window function". For negative float offsets outside double precision, Minnow reports this negative-offset error before PostgreSQL's 22003 overflow error.

Window AVG returns a JavaScript number (float64), including for integer inputs. PostgreSQL returns NUMERIC for AVG(integer); Minnow keeps its float64 result contract.

DISTINCT window aggregates are not supported. Windows work in both the select list and ORDER BY; WHERE, GROUP BY, and HAVING cannot contain them.

Compatible windows share sorting and prepare peer information only when a function or frame needs it. Positional functions and ROWS frames without peer exclusions skip those buffers. Prefix and whole-partition aggregates accumulate in linear time. Frame aggregates combine only values inside the frame, avoiding rounded prefix subtraction and repeated whole-frame scans. Window working buffers count toward the query memory budget, and long database window passes yield for cancellation. Window working buffers do not spill: raise the query budget or bound the input if these buffers exceed it.

A window runs after GROUP BY and HAVING, matching PostgreSQL, so it ranks the groups rather than the rows behind them and its OVER clause reads the group's own aggregates — that is what makes the ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(i.line_total) DESC) above the best sellers per category. SUM(SUM(total)) OVER (PARTITION BY region) is the same idea: the inner aggregate makes the group, the outer one totals across groups.

A window is an expression, so it composes like one. The arithmetic around it runs afterwards, over the column the window produced:

WITH monthly AS (
  SELECT DATE_TRUNC('month', placed_at) AS month, SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', placed_at)
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       100.0 * revenue / SUM(revenue) OVER () AS pct_of_total
FROM monthly
ORDER BY month

Set operations

SELECT customer_id FROM orders WHERE placed_at >= TIMESTAMP '2025-01-01'
EXCEPT
SELECT customer_id FROM orders WHERE placed_at >= TIMESTAMP '2025-07-01'

UNION, UNION ALL, INTERSECT, and EXCEPT, each requiring matching column counts and compatible types. A trailing ORDER BY, LIMIT, or OFFSET applies to the whole set operation and names the first member's output columns, so SELECT id AS key … UNION SELECT … ORDER BY key sorts the combined rows; a member of its own needs parentheses around its ORDER BY or LIMIT. VALUES lists take the same trailing clauses.

Consistency

One query call executes against one version of the database. A join across seven tables sees all seven as they were at a single point, whatever else commits while it runs.

To hold that same version across several calls, open a snapshot scope.

On this page