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 50WHERE 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 5ORDER 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 DESCINNER, 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 DESCCOUNT, 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 DESCAs 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.statusPostgreSQL'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 cThe 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 DESCPERCENTILE_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 <= 3A 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 itemJSON_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 monthWITH 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 monthsA 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 daygenerate_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 ordersROW_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 monthSet 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.