Reading data

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. 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 is accepted and ignored, since a single-session engine has no other session to lock rows against.

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 inputs and MAKE_TIMESTAMP validate calendar dates, clock fields, and timezone offsets. Invalid dates such as February 30 throw. Supported calendar years are 1–9999; years below 100 keep their requested year. Explicit TEXT casts retain text comparison semantics: comparing '1'::TEXT to the number 1 throws rather than interpreting the text as a number.

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.

Joins

SELECT s.name AS store, COUNT(*) AS orders, ROUND(SUM(o.total), 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 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), 2) AS revenue,
       ROUND(AVG(i.line_total), 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), 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

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 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 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:

SELECT '{"sku": "A-1", "dims": {"w": 30, "h": 40}}' -> '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.

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 series:

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.

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. Temporal interval offsets and 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.