5. Advanced SQL in Postgres

Chapter 4 covered the statements every application uses. This chapter covers the features that turn SQL from a way of fetching rows into a way of answering harder questions. These are common table expressions for multi-step and recursive queries, window functions for calculations across related rows, full-text and fuzzy search over natural language, and a set of analytical patterns built from those pieces. Each of them can replace a large amount of application code, or a slow loop of many small queries, with one statement that the database can plan and optimize as a whole. All examples use the shop schema from 4.1 unless a topic creates its own tables.

<a id="51-common-table-expressions"></a>

5.1 Common Table Expressions

This subchapter covers the WITH clause: naming intermediate results, chaining several steps in one query, recursion over hierarchies and graphs, controlling how the planner treats a CTE, and running INSERT, UPDATE, and DELETE inside a query.

WITH Clause Basics

A query that answers a real business question often takes several steps: compute per-order totals, then the average of those totals, then the orders above that average. With nested subqueries (4.5), these steps are written inside out. You have to read the innermost parentheses first, and if a step is needed twice, you write it twice. A common table expression (CTE) gives each step a name and puts it before the query that uses it, so the query reads top to bottom, like a recipe.

Syntax. WITH name AS (subquery) defines a temporary, named result that exists only while this one statement runs. The main query after it can use the name like a table:

WITH order_totals AS (                                 -- step 1: one row per order
    SELECT order_id, sum(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT o.id, c.name, t.total                           -- step 2: use the step-1 result like a table
FROM order_totals AS t
JOIN orders AS o    ON o.id = t.order_id
JOIN customers AS c ON c.id = o.customer_id
WHERE t.total > (SELECT avg(total) FROM order_totals)  -- the same CTE, referenced a second time
ORDER BY t.total DESC;

--  id |     name     |  total
-- ----+--------------+---------
--   5 | Diego Ruiz   | 1358.80
--   1 | Ana Silva    | 1324.00
--   4 | Chloe Martin |  999.00

The same query with derived tables would need the per-order aggregation twice: once in FROM and once inside the subquery that computes the average.

Naming the columns. You can list column names after the CTE name instead of using aliases inside it. This is handy when the subquery's expressions have no natural names:

WITH stats (orders, first_order, last_order) AS (
    SELECT count(*), min(placed_at), max(placed_at) FROM orders
)
SELECT orders, last_order - first_order AS active_span FROM stats;

Scope. A CTE belongs to a single statement. It is not stored anywhere, and the next statement cannot see it. CTEs can also appear inside subqueries, views, and INSERT/UPDATE/DELETE statements.

  WITH a AS (...), b AS (...)     <- visible only inside this statement
  SELECT ... FROM a JOIN b ...
  ;
  SELECT * FROM a;                <- ERROR: relation "a" does not exist

CTE versus other ways to name a result

Tool	Lifetime	Can be indexed	Typical use
Derived table (FROM (SELECT ...) AS t)	One query, one place	No	A single, short intermediate step
CTE (WITH t AS (...))	One statement, referenced any number of times	No	Readable multi-step queries, recursion
View (CREATE VIEW)	Permanent, shared by all sessions	No (materialized views can be)	A query reused across the application (9.4)
Temporary table (CREATE TEMP TABLE)	Session or transaction	Yes, and has statistics after ANALYZE	Large intermediate results used by several statements

Since PostgreSQL 12, a simple CTE that is referenced once is usually inlined into the main query, so it performs like the equivalent derived table. The rules and exceptions are covered in MATERIALIZED and NOT MATERIALIZED Hints.

Key takeaways

• WITH name AS (...) names an intermediate result so a query reads top to bottom instead of inside out.
• A CTE lives for one statement only and can be referenced several times in it.
• An optional column list, WITH name (col1, col2) AS (...), names the output columns.
• For results reused across statements, use a temporary table or a view instead.

🧪 Practice

1. (Beginner) Rewrite the average order value query from Subqueries in FROM Clauses using a CTE named per_order.
2. (Intermediate) Using a CTE of per-customer order counts, list the customers whose number of orders is greater than the average number of orders per customer. Customers with no orders count as zero.
3. (Interview) "When would you replace a CTE with a temporary table?" Hint: Think about statistics, indexes, how many statements need the result, and how large it is.

Multiple and Chained CTEs

Real reports are pipelines: filter the raw rows, aggregate them, aggregate again, compare against a benchmark. A single WITH clause can define several CTEs separated by commas, and each one can read the CTEs defined before it. This turns a deeply nested query into a flat sequence of named steps, each small enough to understand on its own.

Syntax. One WITH keyword, then name AS (...) blocks separated by commas:

WITH valid_orders AS (                          -- step 1: drop cancelled orders
    SELECT id, customer_id
    FROM orders
    WHERE status <> 'cancelled'
),
order_totals AS (                               -- step 2: reads step 1
    SELECT v.customer_id, oi.order_id, sum(oi.quantity * oi.unit_price) AS total
    FROM valid_orders AS v
    JOIN order_items AS oi ON oi.order_id = v.id
    GROUP BY v.customer_id, oi.order_id
),
customer_totals AS (                            -- step 3: reads step 2
    SELECT customer_id, count(*) AS orders, sum(total) AS spent
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spent,
       round(ct.spent / (SELECT avg(spent) FROM customer_totals), 2) AS vs_avg
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
ORDER BY ct.spent DESC;

--     name    | orders |  spent  | vs_avg
-- ------------+--------+---------+--------
--  Ana Silva  |      2 | 1417.00 |   1.96
--  Diego Ruiz |      1 | 1358.80 |   1.88
--  Emma Weber |      1 |   90.50 |   0.12
--  Ben Okafor |      1 |   29.90 |   0.04

The data flows through the steps like this:

 orders ──> valid_orders ──┐
                           ├──> order_totals ──> customer_totals ──┬──> final SELECT
 order_items ──────────────┘                                       └──> avg(spent) subquery

Rules

• A CTE can reference only CTEs defined earlier in the same WITH list (recursive CTEs, covered next, are the exception: they reference themselves).
• Write WITH once. Every following CTE is introduced by a comma, not another WITH.
• CTE names hide tables with the same name for the rest of the statement. A CTE named orders makes orders refer to the CTE, not the table, which is legal but confusing.
• Several CTEs can read the same earlier CTE, and the final query can use any of them.

Debugging a pipeline. Because each step has a name, you can inspect any intermediate result by temporarily replacing the final SELECT with SELECT * FROM order_totals;. This is much easier than extracting a subquery from three levels of nesting.

When to stop adding steps. Every step should do one recognizable thing: filter, join, aggregate, or rank. If two consecutive CTEs could be described as one action ("orders with their totals"), merge them. If the pipeline is reused by several queries, move the stable part into a view.

Key takeaways

• One WITH can hold many comma-separated CTEs. Each can read the ones before it.
• Chained CTEs express a query as a pipeline of named, single-purpose steps.
• Inspect any step while debugging by selecting from it directly.
• Avoid giving CTEs the same names as real tables.

🧪 Practice

1. (Beginner) Write a two-step query: a CTE shop_items with every order item and its product's category id, then a final query that sums revenue per category id.
2. (Intermediate) Build a three-step pipeline that computes, per country, the number of customers, the number of non-cancelled orders, and the revenue per customer. Countries whose customers placed no orders must show zero revenue, not be missing.
3. (Interview) "A colleague says CTEs make queries slower. Are they right?" Hint: Distinguish PostgreSQL versions before and after 12, CTEs referenced once versus several times, and readability versus performance.

Recursive CTEs

Some questions have no fixed depth. "Who reports to Grace, directly or indirectly?" might need one join for a flat team and five for a deep one. A self join (4.3) climbs exactly one level per join, so you would have to know the depth in advance. A recursive CTE repeats a query until it stops finding new rows, so the depth is discovered while the query runs.

Structure. WITH RECURSIVE plus a CTE made of two parts joined by UNION ALL (or UNION):

WITH RECURSIVE counter (n) AS (
    SELECT 1                          -- anchor member: the starting row(s), runs once
    UNION ALL
    SELECT n + 1                      -- recursive member: refers to the CTE itself
    FROM counter
    WHERE n < 5                       -- stop condition: no new rows means recursion ends
)
SELECT n FROM counter;
-- 1, 2, 3, 4, 5

How it is evaluated. Despite the name, PostgreSQL does not call anything recursively. It iterates, using a working table that holds only the rows produced by the previous round:

 round 0: run anchor                     -> working table = {1}        result = {1}
 round 1: run recursive part on {1}      -> working table = {2}        result = {1,2}
 round 2: run recursive part on {2}      -> working table = {3}        result = {1,2,3}
 ...
 round 4: run recursive part on {4}      -> working table = {5}        result = {1,...,5}
 round 5: run recursive part on {5}      -> no rows (5 < 5 is false)   STOP

Each round sees only the new rows from the previous round, not the whole result accumulated so far. Recursion ends when a round produces nothing.

A real example: an org chart. First give the sample data a deeper hierarchy:

INSERT INTO employees (id, name, manager_id, department, salary) VALUES
  (7, 'Max',  2, 'Engineering', 6000),    -- reports to Hugo
  (8, 'Nora', 7, 'Engineering', 5500);    -- reports to Max

Everyone in Grace's organization, with their level:

WITH RECURSIVE reports AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 1                               -- anchor: Grace herself
    UNION ALL
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees AS e
    JOIN reports AS r ON e.manager_id = r.id   -- people whose manager was found in the previous round
)
SELECT * FROM reports;

--  id | name  | manager_id | depth
-- ----+-------+------------+-------
--   1 | Grace |            |     1
--   2 | Hugo  |          1 |     2
--   3 | Iris  |          1 |     2
--   7 | Max   |          2 |     3
--   8 | Nora  |          7 |     4

UNION ALL versus UNION

Combinator	Behavior	Use when
UNION ALL	Keeps every row the recursive part produces	Trees and other data that cannot revisit a row. Fastest
UNION	Discards rows already in the result, so they are not fed back	Graphs where the same row (all columns equal) can be reached twice. Stops simple cycles

UNION only removes rows that are identical in every column. A row that carries a growing depth or path is never identical to an earlier one, so UNION does not stop such a query from looping. Cycle handling is covered in the next topic.

Restrictions on the recursive member. It must reference the CTE exactly once, and it cannot use aggregates, GROUP BY, window functions, DISTINCT, ORDER BY, LIMIT, or the CTE on the nullable side of an outer join. Do that work in the final SELECT, after recursion has finished.

Guarding against runaway recursion. A recursive CTE that never stops runs until it fills memory or disk or hits statement_timeout. During development, add a depth limit to the recursive member:

    ...
    JOIN reports AS r ON e.manager_id = r.id
    WHERE r.depth < 20                         -- safety net: no org chart is this deep

Key takeaways

• WITH RECURSIVE = anchor member UNION ALL recursive member that refers to the CTE itself.
• Evaluation is iterative: each round runs the recursive member on the previous round's new rows only, and stops when a round returns nothing.
• Use UNION ALL for trees. UNION discards duplicate rows but does not stop cycles when rows carry a depth or path.
• Aggregates, LIMIT, and ORDER BY belong in the final query, not in the recursive member. Add a depth limit as a safety net.

🧪 Practice

1. (Beginner) Generate the dates of the first 10 days of March 2025 with a recursive CTE, then compare your query with generate_series.
2. (Intermediate) For every employee, return the number of people below them in the hierarchy (direct and indirect). Hint: Start the anchor from every employee and carry the root's id through the recursion.
3. (Interview) "What happens if the employees table contains a cycle, for example A manages B and B manages A, and you run the org-chart query?" Hint: Trace the working table round by round with UNION ALL, then with UNION, remembering that depth is part of each row.

Graph and Hierarchy Traversal

Most hierarchical data in relational databases is stored as an adjacency list: each row stores its parent's id (employees.manager_id, categories.parent_id). This is easy to update, but every question about more than one level, such as all descendants, all ancestors, or the full path to the root, needs a recursive CTE. General graphs (routes, social connections, dependencies) are the same idea with one complication: they can contain cycles, so a naive traversal never ends.

Walking down: a subtree. All products in the Electronics category or any of its subcategories:

WITH RECURSIVE subtree AS (
    SELECT id FROM categories WHERE name = 'Electronics'   -- the root of the subtree
    UNION ALL
    SELECT c.id
    FROM categories AS c
    JOIN subtree AS s ON c.parent_id = s.id                -- children of rows found so far
)
SELECT p.name, p.price
FROM products AS p
WHERE p.category_id IN (SELECT id FROM subtree)
ORDER BY p.price DESC;

--       name      |  price
-- ----------------+---------
--  UltraBook 14   | 1299.00
--  WorkBook 15    |  999.00
--  Wireless Mouse |   29.90
--  USB-C Cable    |   12.50

Walking up: the chain of ancestors. Reverse the join direction: find the row whose id is the current row's parent:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = 8    -- start at Nora
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees AS e
    JOIN chain AS c ON e.id = c.manager_id                                 -- go to the manager
)
SELECT string_agg(name, ' -> ' ORDER BY level) AS chain_of_command FROM chain;
-- Nora -> Max -> Hugo -> Grace

Building paths and displaying a tree. Carry the path as a string (for display) and as an array (for sorting) through the recursion:

WITH RECURSIVE tree AS (
    SELECT id, name, 0 AS depth,
           name::text AS path,                -- human-readable path
           ARRAY[id]  AS sort_key             -- sorts children directly under their parent
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, t.depth + 1,
           t.path || ' > ' || c.name,
           t.sort_key || c.id
    FROM categories AS c
    JOIN tree AS t ON c.parent_id = t.id
)
SELECT repeat('  ', depth) || name AS category, path
FROM tree
ORDER BY sort_key;

--    category    |           path
-- ---------------+---------------------------
--  Electronics   | Electronics
--    Laptops     | Electronics > Laptops
--    Accessories | Electronics > Accessories
--  Books         | Books

SEARCH clause (PostgreSQL 14+). The SQL standard has a shorthand that adds the sort column for you. SEARCH DEPTH FIRST BY id SET ord produces the same ordering as sort_key above. SEARCH BREADTH FIRST BY id SET ord orders level by level instead:

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories AS c JOIN tree AS t ON c.parent_id = t.id
) SEARCH DEPTH FIRST BY id SET ord            -- adds a column "ord" that sorts in depth-first order
SELECT name FROM tree ORDER BY ord;

Graphs with cycles. Consider a table of flight routes. Berlin flies back to Lisbon, so following routes from Lisbon would go around forever:

CREATE TABLE routes (src text, dst text, km int);
INSERT INTO routes VALUES
  ('LIS', 'MAD',  500), ('MAD', 'PAR', 1050), ('PAR', 'BER',  880),
  ('MAD', 'BER', 1870), ('BER', 'LIS', 2310), ('LIS', 'PAR', 1450);

 LIS ──500──> MAD      MAD ──1050──> PAR      PAR ──880──> BER
 LIS ──1450─> PAR      MAD ──1870──> BER      BER ──2310─> LIS    <- leads back to the start

The classic fix is to carry the visited nodes in an array and refuse to visit a node twice:

-- Every route from Lisbon to Berlin, without visiting a city twice
WITH RECURSIVE trips AS (
    SELECT dst, ARRAY['LIS', dst] AS path, km AS total_km
    FROM routes
    WHERE src = 'LIS'
    UNION ALL
    SELECT r.dst, t.path || r.dst, t.total_km + r.km
    FROM trips AS t
    JOIN routes AS r ON r.src = t.dst
    WHERE r.dst <> ALL (t.path)               -- cycle guard: next city not visited yet
)
SELECT array_to_string(path, ' -> ') AS route, total_km
FROM trips
WHERE dst = 'BER'
ORDER BY total_km;

--           route           | total_km
-- --------------------------+----------
--  LIS -> PAR -> BER        |     2330
--  LIS -> MAD -> BER        |     2370
--  LIS -> MAD -> PAR -> BER |     2430

The first row is the shortest route. This brute-force search is fine for small graphs, but the number of paths grows exponentially with graph size, so it does not replace a real shortest-path algorithm on large networks. For those, use the pgRouting extension or a graph database.

CYCLE clause (PostgreSQL 14+). The standard shorthand for the same guard. CYCLE dst SET is_cycle USING path adds a boolean is_cycle and a path column of visited values, and stops extending a row once it repeats a value:

WITH RECURSIVE trips AS (
    SELECT dst, km AS total_km FROM routes WHERE src = 'LIS'
    UNION ALL
    SELECT r.dst, t.total_km + r.km
    FROM trips AS t JOIN routes AS r ON r.src = t.dst
) CYCLE dst SET is_cycle USING path
SELECT dst, total_km, path
FROM trips
WHERE dst = 'BER' AND NOT is_cycle;

Note that the anchor's starting city is not in path here (only dst values are tracked), so a route that flies back to Lisbon is not flagged. Only a repeated destination city is. Include the start in the tracked column when that matters.

Alternative hierarchy models. Recursive CTEs are the standard way to query adjacency lists. When a hierarchy is read far more often than it changes, other models avoid recursion at query time:

Model	Stores	Subtree query	Moving a subtree
Adjacency list	parent_id	Recursive CTE	Update one row
Materialized path (ltree extension, or a text path)	Full path, such as 1.3.7	Prefix match, indexable	Update every descendant
Closure table	One row per (ancestor, descendant) pair	Simple join	Delete and insert many pairs
Nested sets	lft/rgt interval numbers	Range comparison	Renumber much of the tree

Key takeaways

• Walk down a tree by joining child.parent_id = cte.id. Walk up by joining parent.id = cte.parent_id.
• Carry paths, depth, and sort keys as extra columns through the recursion. SEARCH DEPTH FIRST and BREADTH FIRST (14+) build the sort key for you.
• In graphs, prevent infinite loops with a visited-nodes array (node <> ALL (path)) or the CYCLE clause (14+).
• Exhaustive path search grows exponentially. Use pgRouting or a different hierarchy model when the data is large or read-heavy.

🧪 Practice

1. (Beginner) Return each category with the number of products in it including all subcategories. Use the subtree pattern.
2. (Intermediate) Using routes, list every city reachable from Paris with the minimum number of flights needed to reach it. Hint: Use a hop counter, a cycle guard, and min() in the final query.
3. (Interview) "Design storage for a product category tree of 50,000 nodes that is read on every page view and changed once a week." Hint: Compare an adjacency list with recursive CTEs, ltree, and a closure table on read cost, write cost, and integrity.

MATERIALIZED and NOT MATERIALIZED Hints

A CTE can be executed in two ways. The planner can materialize it: run it once, store its rows in a temporary in-memory buffer (spilling to disk if large), and have every reference read from that buffer. Or it can inline it: paste the CTE's query into the main query as if it were a derived table and optimize everything together. Each strategy is faster in different situations, and since PostgreSQL 12 you can choose explicitly.

Some history. Before PostgreSQL 12, every CTE was materialized. A CTE acted as an optimization fence: conditions in the outer query were never pushed into it, and indexes on its underlying tables could not be used for those conditions. Many older articles therefore recommend avoiding CTEs for performance, or use them deliberately as fences. Both pieces of advice are out of date.

The default rules (PostgreSQL 12+). A non-recursive CTE is inlined automatically when all of these hold:

• it is referenced exactly once in the rest of the query,
• it has no side effects (it is a plain SELECT, not an INSERT/UPDATE/DELETE),
• it calls no volatile functions such as random() or nextval().

Otherwise, it is materialized. Recursive CTEs are always materialized.

Seeing the difference. On an events table with a million rows and an index on user_id:

-- Inlined (default): the condition reaches the table, so the index is used
EXPLAIN (COSTS OFF)
WITH e AS (SELECT * FROM events)
SELECT * FROM e WHERE user_id = 42;

--  Bitmap Heap Scan on events
--    Recheck Cond: (user_id = 42)
--    ->  Bitmap Index Scan on events_user_id_idx
--          Index Cond: (user_id = 42)

-- Forced materialization: the whole table is scanned into the CTE, then filtered
EXPLAIN (COSTS OFF)
WITH e AS MATERIALIZED (SELECT * FROM events)
SELECT * FROM e WHERE user_id = 42;

--  CTE Scan on e
--    Filter: (user_id = 42)
--    CTE e
--      ->  Seq Scan on events

CTE Scan in a plan always means the CTE was materialized (7.3).

The hints

Syntax	Effect	Use when
WITH x AS (...)	Default rules above	Almost always
WITH x AS MATERIALIZED (...)	Always compute once and store	An expensive CTE is referenced once, but you need it evaluated only once (for example it calls an expensive function per row and inlining would duplicate the work), or you deliberately want a fence because the planner chose a bad plan
WITH x AS NOT MATERIALIZED (...)	Inline even if referenced several times	The CTE is referenced several times, but each reference filters it differently and those filters could use an index

Example: when materializing a multi-use CTE is right. An aggregate over a large table that is referenced twice should be computed once. This is what the default does:

WITH totals AS (                                -- referenced twice -> materialized by default
    SELECT user_id, count(*) AS n
    FROM events
    GROUP BY user_id
)
SELECT a.user_id, a.n
FROM totals AS a
JOIN totals AS b ON b.user_id = a.user_id + 1   -- compare each user with the next id
WHERE a.n > b.n;
-- The plan contains one aggregate over events and two "CTE Scan on totals" nodes.

Adding NOT MATERIALIZED here would make PostgreSQL scan and aggregate the million-row table twice.

Example: when inlining a multi-use CTE is right. If a CTE is a thin wrapper over a large table and each reference picks out a few rows, materializing it copies the whole table only to discard most of it. NOT MATERIALIZED lets each reference use the index:

WITH e AS NOT MATERIALIZED (SELECT * FROM events)
SELECT (SELECT count(*) FROM e WHERE user_id = 42) AS user_42,
       (SELECT count(*) FROM e WHERE user_id = 43) AS user_43;

Materialized CTEs have no statistics. The planner does not know how many rows a materialized CTE will return or how its values are distributed, so it uses rough estimates for the outer query. When a materialized intermediate result is large and joined in complex ways, a temporary table with ANALYZE can produce much better plans.

Key takeaways

• Since PostgreSQL 12, a side-effect-free, non-recursive CTE referenced once is inlined. Otherwise it is materialized (computed once, stored, and read by every reference).
• Materialization blocks pushing outer conditions into the CTE, so its indexes cannot serve them. CTE Scan in a plan reveals it.
• AS MATERIALIZED forces a single evaluation (or a deliberate fence). AS NOT MATERIALIZED forces inlining even for several references.
• Check the plan with EXPLAIN before adding a hint. The default is right most of the time.

🧪 Practice

1. (Beginner) Create the events table with a million rows (use generate_series and random()) and an index on user_id. Run EXPLAIN on the two queries above and confirm the plans.
2. (Intermediate) Write a CTE that calls random() and is referenced twice in the main query. Run it with and without NOT MATERIALIZED and explain why the results differ.
3. (Interview) "After upgrading from PostgreSQL 11 to 16, a reporting query became 50 times slower. It uses a CTE. What might have happened?" Hint: Before 12, the CTE was a fence, and the planner may now choose a different plan based on misestimated row counts. Consider AS MATERIALIZED as a quick fix and fixing statistics as the real one.

Data-Modifying CTEs

Some operations are naturally several statements: delete rows from one table and archive them in another, insert an order and then its items using the new order id, decrease stock and log the change. Running them as separate statements needs a transaction and several round trips, and the application must carry generated values from one statement to the next. In PostgreSQL, a CTE may contain an INSERT, UPDATE, or DELETE (and MERGE from PostgreSQL 17), and its RETURNING output (4.6) can feed the rest of the statement. The whole chain runs as one atomic statement.

Insert a parent and its children in one statement

WITH new_order AS (
    INSERT INTO orders (customer_id, status)
    VALUES (6, 'pending')
    RETURNING id                                   -- the generated order id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.id, p.id, 1, p.price              -- reuse the new id for every item
FROM new_order
CROSS JOIN products AS p
WHERE p.sku IN ('BK-001', 'ACC-002')
RETURNING order_id, product_id, unit_price;

--  order_id | product_id | unit_price
-- ----------+------------+------------
--         7 |          4 |      29.90
--         7 |          5 |      39.00

Update and log in one statement

CREATE TABLE stock_log (
  product_id bigint,
  change     int,
  logged_at  timestamptz DEFAULT now()
);

WITH sold AS (                                     -- plain SELECT step
    SELECT product_id, sum(quantity) AS qty
    FROM order_items
    WHERE order_id = 5
    GROUP BY product_id
),
updated AS (                                       -- modifying step
    UPDATE products AS p
    SET stock = p.stock - s.qty
    FROM sold AS s
    WHERE p.id = s.product_id
    RETURNING p.id, s.qty
)
INSERT INTO stock_log (product_id, change)         -- final statement logs what changed
SELECT id, -qty FROM updated;

The snapshot rule. All parts of the statement, including the main query, see the database as it was when the statement started. A data-modifying CTE's changes are not visible to the other parts by reading the table. The only way to see what it did is through its RETURNING output:

WITH upd AS (
    UPDATE products SET stock = stock - 1 WHERE id = 3
    RETURNING stock
)
SELECT (SELECT stock FROM products WHERE id = 3) AS stock_seen_by_query,   -- old value
       (SELECT stock FROM upd)                    AS stock_after_update;    -- new value

--  stock_seen_by_query | stock_after_update
-- ---------------------+--------------------
--                  200 |                199

 statement starts ──> snapshot S taken
   ├── CTE upd:      UPDATE products ...      (writes new row versions)
   ├── main SELECT:  reads products under S   (still sees the old versions)
   └── upd's RETURNING rows                   (the only view of the new values)
 statement ends ────> all changes become visible to later statements

Other rules to know

• A data-modifying CTE always runs to completion, even if nothing reads its output. WITH d AS (DELETE ...) SELECT 1 still deletes.
• The order in which sibling modifying CTEs run is not defined. Do not rely on one CTE's change being applied before another's, except through RETURNING data flow.
• Do not modify the same row twice in one statement (for example, two CTEs updating the same product). Only one of the changes takes effect, and which one is unpredictable. The same applies to deleting a row that another part of the statement updates: only the update happens.
• Data-modifying CTEs are always materialized and must be at the top level of the statement.

Common patterns

Goal	Shape
Archive rows	WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved
Insert parent and children	WITH p AS (INSERT ... RETURNING id) INSERT INTO child SELECT p.id, ... FROM p
Audit an update	WITH u AS (UPDATE ... RETURNING ...) INSERT INTO audit SELECT ... FROM u
Report what changed	WITH u AS (UPDATE ... RETURNING ...) SELECT count(*), sum(...) FROM u

Key takeaways

• INSERT, UPDATE, DELETE (and MERGE from 17) can appear in WITH, and their RETURNING rows feed later steps.
• The whole statement is atomic: it all succeeds or nothing is changed.
• Every part sees the same starting snapshot. Changes are visible to other parts only through RETURNING.
• Modifying CTEs always run, even if unread. Never modify the same row twice in one statement.

🧪 Practice

1. (Beginner) In one statement, delete all cancelled orders and return how many order items were removed with them. Hint: ON DELETE CASCADE removes the items, but the statement only returns orders. Delete the items explicitly in a CTE instead.
2. (Intermediate) Write a "get or create" statement for a customer by email: insert the customer if the email is new, and return the id in both cases. Use INSERT ... ON CONFLICT DO NOTHING RETURNING id in a CTE, combined with a SELECT of the existing row via UNION ALL.
3. (Interview) "Why can the 'get or create' pattern from the previous exercise still return no row under concurrent load?" Hint: Think about the snapshot rule: the SELECT part cannot see a row inserted by another transaction that committed after the statement started.

<a id="52-window-functions"></a>

5.2 Window Functions

This subchapter covers window functions: calculations across a set of related rows that, unlike GROUP BY, keep every input row in the result.

OVER Clause and Window Definition

GROUP BY answers "what is the average price per category?" but in doing so it collapses each category into one row, so the individual products are gone. Many questions need both levels at once: "show every product next to its category's average", "number each customer's orders", "compare each day with the previous day". Before window functions, these needed a join back to an aggregated subquery or a correlated subquery per row. A window function computes a value over a set of rows related to the current row, called its window, and attaches the result to that row without removing anything.

The analogy. Imagine walking along the rows of a result. At each row you look through a window frame and see some of the other rows: all rows, the rows in the same category, or the three rows before you. The window function computes something from what you see (a sum, a rank, the previous value) and writes it next to the current row. Then you move to the next row and the view shifts.

Syntax. Any aggregate (sum, avg, count, ...) or dedicated window function becomes a window function when followed by OVER (...):

function(args) OVER (
    [PARTITION BY expr, ...]      -- split rows into independent groups (next topic)
    [ORDER BY expr, ...]          -- order rows inside each group
    [frame clause]                -- which rows around the current one to use (Frame Clauses topic)
)

An empty OVER () means "the window is the whole result set":

SELECT name, price,
       round(avg(price) OVER (), 2)          AS avg_all,       -- same value on every row
       round(price - avg(price) OVER (), 2)  AS diff_from_avg
FROM products
ORDER BY price DESC;

--         name        |  price  | avg_all | diff_from_avg
-- --------------------+---------+---------+---------------
--  UltraBook 14       | 1299.00 |  351.20 |        947.80
--  WorkBook 15        |  999.00 |  351.20 |        647.80
--  Postgres Internals |   54.00 |  351.20 |       -297.20
--  SQL in Practice    |   39.00 |  351.20 |       -312.20
--  Wireless Mouse     |   29.90 |  351.20 |       -321.30
--  Gift Card          |   25.00 |  351.20 |       -326.20
--  USB-C Cable        |   12.50 |  351.20 |       -338.70

GROUP BY versus window function

 GROUP BY category_id                         avg(price) OVER (PARTITION BY category_id)

  rows in       rows out                       rows in       rows out
 ┌────────┐                                   ┌────────┐    ┌──────────────┐
 │ Book A │──┐                                │ Book A │───>│ Book A  46.5 │
 │ Book B │──┴──> │ Books 46.5 │              │ Book B │───>│ Book B  46.5 │
 │ Lap X  │──┐                                │ Lap X  │───>│ Lap X  1149  │
 │ Lap Y  │──┴──> │ Laptops 1149 │            │ Lap Y  │───>│ Lap Y  1149  │
 └────────┘                                   └────────┘    └──────────────┘
  4 rows -> 2 rows                             4 rows -> 4 rows, extra column

Where window functions run. In the logical processing order (4.1), window functions are computed after WHERE, GROUP BY, and HAVING, while the SELECT list is evaluated. Two consequences:

• The window sees only rows that survived WHERE. Filtering first and then computing avg() OVER () averages the filtered rows only.
• You cannot use a window function in WHERE, GROUP BY, or HAVING. To filter on its result, compute it in a subquery or CTE, then filter outside:

SELECT name FROM products WHERE price > avg(price) OVER ();
-- ERROR:  window functions are not allowed in WHERE

SELECT name, price
FROM (
    SELECT name, price, avg(price) OVER () AS avg_price
    FROM products
) AS t
WHERE price > avg_price;                 -- filter after the window function ran

Window functions can be applied to the output of GROUP BY. Then the argument may be an aggregate, as in sum(sum(x)) OVER (): the inner sum is the group's aggregate, the outer one is the window function over the groups.

Key takeaways

• function(...) OVER (...) computes a value from a window of related rows and attaches it to each row without collapsing rows.
• OVER () makes the whole result set the window.
• Window functions run after WHERE, GROUP BY, and HAVING, so they cannot appear in those clauses. Filter on them in an outer query.
• They can be applied on top of grouped results, for example sum(count(*)) OVER ().

🧪 Practice

1. (Beginner) List every product with its price and the price of the most expensive product overall, using max(price) OVER ().
2. (Intermediate) Using GROUP BY and a window function in the same query, return each order status with its order count and its percentage of all orders.
3. (Interview) "How would you write 'products priced above their category's average' without window functions, and why is the window version usually better?" Hint: Compare a correlated subquery, a join to an aggregated derived table, and one pass with avg() OVER (PARTITION BY ...).

PARTITION BY and ORDER BY in Windows

An empty OVER () treats the whole result as one window. Most questions are relative to a group ("compared to its category"), or to a position in a sequence ("this customer's first order"). PARTITION BY and ORDER BY inside OVER describe those two ideas.

PARTITION BY: independent windows per group. Rows with equal partition values form a partition, and the function is computed separately for each one, as if each partition were its own table. It works like GROUP BY without the collapsing:

SELECT p.name, cat.name AS category, p.price,
       round(avg(p.price) OVER (PARTITION BY p.category_id), 2) AS avg_in_category
FROM products AS p
LEFT JOIN categories AS cat ON cat.id = p.category_id
ORDER BY p.category_id, p.price DESC;

--         name        |  category   |  price  | avg_in_category
-- --------------------+-------------+---------+-----------------
--  Postgres Internals | Books       |   54.00 |           46.50
--  SQL in Practice    | Books       |   39.00 |           46.50
--  UltraBook 14       | Laptops     | 1299.00 |         1149.00
--  WorkBook 15        | Laptops     |  999.00 |         1149.00
--  Wireless Mouse     | Accessories |   29.90 |           21.20
--  USB-C Cable        | Accessories |   12.50 |           21.20
--  Gift Card          |             |   25.00 |           25.00    <- NULL category is its own partition

ORDER BY: a sequence inside each partition. ORDER BY inside OVER orders rows within the window. It does two things:

1. It defines the order for functions that care about position: row_number(), rank(), lag(), lead(), first_value().
2. For aggregates, it changes the default frame from "the whole partition" to "from the start of the partition up to the current row", so sum() becomes a running sum.

SELECT customer_id, id AS order_id, placed_at::date,
       row_number() OVER (PARTITION BY customer_id ORDER BY placed_at) AS nth_order,     -- position in sequence
       count(*)     OVER (PARTITION BY customer_id)                    AS orders_total   -- whole partition
FROM orders
ORDER BY customer_id, placed_at;

--  customer_id | order_id | placed_at  | nth_order | orders_total
-- -------------+----------+------------+-----------+--------------
--            1 |        1 | 2025-01-05 |         1 |            2
--            1 |        2 | 2025-02-11 |         2 |            2
--            2 |        3 | 2025-02-20 |         1 |            1
--  ...

 PARTITION BY customer_id ORDER BY placed_at

 ┌─ partition: customer 1 ─────────┐   ┌─ partition: customer 2 ─┐
 │ order 1 (Jan 05)  nth_order = 1 │   │ order 3  nth_order = 1  │   ...
 │ order 2 (Feb 11)  nth_order = 2 │   └─────────────────────────┘
 └─────────────────────────────────┘
   numbering restarts in every partition

ORDER BY inside OVER versus the query's ORDER BY. They are independent. The window's ORDER BY decides how the function sees the rows. The query's ORDER BY decides how the final result is displayed. Without a query-level ORDER BY, the output order is not guaranteed, even if it often happens to follow the last window's order.

Partitioning by expressions. Partition and order keys can be any expressions, for example PARTITION BY date_trunc('month', placed_at) or PARTITION BY (price > 100).

Performance. To compute a window, PostgreSQL sorts the rows by the partition keys followed by the order keys (unless an index already provides that order). Windows with the same PARTITION BY and ORDER BY share one sort. An index on (customer_id, placed_at) can let PostgreSQL skip the sort entirely for the query above.

Key takeaways

• PARTITION BY splits rows into groups that are computed independently. It is GROUP BY without collapsing.
• ORDER BY inside OVER orders rows within each partition, which drives ranking and offset functions and turns aggregates into running aggregates.
• The window's ORDER BY does not sort the output. Add a query-level ORDER BY for that.
• Each distinct (partition, order) combination needs a sort, which a matching index can avoid.

🧪 Practice

1. (Beginner) For every order item, show the product, the quantity, and the total quantity of all items in the same order.
2. (Intermediate) For every customer's order, return the customer's cumulative number of orders and cumulative spending up to and including that order.
3. (Interview) "A query has three window functions with different PARTITION BY clauses and is slow on 50 million rows. Why, and what can you do?" Hint: Count the sorts in EXPLAIN. Consider aligning windows, matching indexes, and pre-aggregating before applying windows.

Ranking Functions

"Top 3 products per category", "the customer's first order", "salary percentile within the department": all of these need to number rows by some ordering. Ranking functions assign each row a position within its partition. They differ in how they treat ties, rows that are equal in the window's ORDER BY.

The functions

Function	Ties	Gaps after ties	Example for values 9, 7, 7, 5
row_number()	Different numbers, arbitrary order among ties	Not applicable	1, 2, 3, 4
rank()	Same number	Yes ("Olympic" ranking)	1, 2, 2, 4
dense_rank()	Same number	No	1, 2, 2, 3
percent_rank()	Same value	Relative: (rank - 1) / (rows - 1)	0, 0.33, 0.33, 1
cume_dist()	Same value	Fraction of rows <= current row	0.25, 0.75, 0.75, 1
ntile(n)	May split ties	Divides rows into n nearly equal buckets	ntile(2): 1, 1, 2, 2

Comparing them on real data. Customers ranked by number of orders. Four customers are tied with one order:

SELECT c.name, count(o.id) AS orders,
       row_number() OVER (ORDER BY count(o.id) DESC, c.name) AS row_number,   -- name breaks ties
       rank()       OVER (ORDER BY count(o.id) DESC)         AS rank,
       dense_rank() OVER (ORDER BY count(o.id) DESC)         AS dense_rank
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY row_number;

--      name     | orders | row_number | rank | dense_rank
-- --------------+--------+------------+------+------------
--  Ana Silva    |      2 |          1 |    1 |          1
--  Ben Okafor   |      1 |          2 |    2 |          2
--  Chloe Martin |      1 |          3 |    2 |          2
--  Diego Ruiz   |      1 |          4 |    2 |          2
--  Emma Weber   |      1 |          5 |    2 |          2
--  Farid Haddad |      0 |          6 |    6 |          3     <- rank jumps to 6, dense_rank continues at 3

Ranking within groups. Combine with PARTITION BY. The most expensive product in each category, using the classic "rank in a subquery, filter outside" shape:

SELECT category_id, name, price
FROM (
    SELECT category_id, name, price,
           rank() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
    FROM products
) AS ranked
WHERE rnk = 1;           -- rank() keeps all products tied for first place

Choosing a function

Question	Function
"Exactly one row per group", such as deduplication or the latest event	row_number() with a unique tie-breaker
"Top N, include everyone tied at the cut-off"	rank()
"Top N distinct values", such as the three highest prices	dense_rank()
"Which percentile is this row in?"	percent_rank() or cume_dist()
"Split customers into 4 equal-sized tiers"	ntile(4)

Determinism. If the window's ORDER BY has ties, row_number() numbers the tied rows in an arbitrary order that can change between runs. Always add a unique column (such as the primary key) as the final sort key when you rely on row_number().

Deduplication with row_number. A frequent cleanup task: keep one row per email and delete the rest.

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, row_number() OVER (PARTITION BY lower(email) ORDER BY created_at, id) AS rn
        FROM customers
        WHERE email IS NOT NULL
    ) AS d
    WHERE rn > 1          -- every row except the oldest per email
);

The ranking functions here rank existing rows. The aggregates with the same names used with WITHIN GROUP answer a different question: where a hypothetical value would rank (4.4).

Key takeaways

• row_number() gives unique positions, rank() gives ties the same rank and leaves gaps, dense_rank() gives ties the same rank without gaps.
• percent_rank(), cume_dist(), and ntile(n) express relative position and bucketing.
• To filter on a rank, compute it in a subquery or CTE and filter outside.
• Make row_number() deterministic by ending the window's ORDER BY with a unique key.

🧪 Practice

1. (Beginner) Rank all products by price, from most to least expensive, with rank() and dense_rank(). Then change one price to create a tie and observe the difference.
2. (Intermediate) Split customers into three spending tiers with ntile(3) based on their total non-cancelled spending, including customers who spent nothing.
3. (Interview) "A table of user events contains exact duplicate rows because of a buggy import. How do you delete the duplicates while keeping one copy of each?" Hint: With no unique column, row_number() still works if you identify rows physically. Look up the ctid system column and its limits.

Offset Functions LAG and LEAD

Comparisons with neighbors are everywhere in reporting: "how much did revenue change since yesterday?", "how many days passed between a customer's orders?", "when is the next scheduled event?". Without window functions, each needs a self join on "the previous row", which is awkward to express and slow. lag() and lead() read a value from another row of the same window, a fixed number of rows behind or ahead.

Syntax

Function	Returns
lag(expr [, offset [, default]])	expr from the row offset rows before the current one (default 1)
lead(expr [, offset [, default]])	expr from the row offset rows after the current one
first_value(expr) / last_value(expr) / nth_value(expr, n)	expr from the first, last, or nth row of the window frame

When the target row does not exist (before the first or after the last row in the partition), the result is default, or NULL if no default is given.

Day-over-day change. This topic and the next ones use a small daily_sales table. Note that 2025-03-06 is missing, as if the shop was closed that day:

CREATE TABLE daily_sales (day date PRIMARY KEY, revenue numeric(10,2) NOT NULL);
INSERT INTO daily_sales VALUES
  ('2025-03-01', 120), ('2025-03-02',  80), ('2025-03-03', 150), ('2025-03-04',  90),
  ('2025-03-05', 200), ('2025-03-07',  60), ('2025-03-08', 110), ('2025-03-09', 130);

SELECT day, revenue,
       lag(revenue)  OVER (ORDER BY day)           AS prev,
       revenue - lag(revenue) OVER (ORDER BY day)  AS change,
       lag(revenue, 1, 0) OVER (ORDER BY day)      AS prev_or_zero,   -- default instead of NULL
       lead(day)     OVER (ORDER BY day)           AS next_day
FROM daily_sales;

--     day     | revenue |  prev  | change  | prev_or_zero |  next_day
-- ------------+---------+--------+---------+--------------+------------
--  2025-03-01 |  120.00 |        |         |            0 | 2025-03-02
--  2025-03-02 |   80.00 | 120.00 |  -40.00 |       120.00 | 2025-03-03
--  2025-03-03 |  150.00 |  80.00 |   70.00 |        80.00 | 2025-03-04
--  2025-03-04 |   90.00 | 150.00 |  -60.00 |       150.00 | 2025-03-05
--  2025-03-05 |  200.00 |  90.00 |  110.00 |        90.00 | 2025-03-07   <- skips the missing day
--  2025-03-07 |   60.00 | 200.00 | -140.00 |       200.00 | 2025-03-08
--  2025-03-08 |  110.00 |  60.00 |   50.00 |        60.00 | 2025-03-09
--  2025-03-09 |  130.00 | 110.00 |   20.00 |       110.00 |

Rows, not time. lag() looks one row back, not one day back. On 2025-03-07 the "previous" value comes from 2025-03-05. If the comparison must be with the previous calendar day, fill the missing days first with generate_series (Time Bucketing and Series Generation), or look up the exact date with a join.

Within partitions. Time between each customer's consecutive orders:

SELECT customer_id, id AS order_id, placed_at::date,
       placed_at - lag(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS since_previous
FROM orders
ORDER BY customer_id, placed_at;

--  customer_id | order_id | placed_at  |  since_previous
-- -------------+----------+------------+------------------
--            1 |        1 | 2025-01-05 |
--            1 |        2 | 2025-02-11 | 37 days 04:30:00
--            2 |        3 | 2025-02-20 |                    <- first order of each customer: NULL
--  ...

first_value, last_value, and the frame trap. These functions read from the window frame, not the whole partition. With ORDER BY in the window, the default frame ends at the current row, so last_value() returns the current row's value, which is almost never what you want:

SELECT day, revenue,
       last_value(revenue) OVER (ORDER BY day) AS last_default,   -- frame ends at current row
       last_value(revenue) OVER (ORDER BY day
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM daily_sales;
-- last_default equals revenue on every row; last_full is 130.00 on every row

Frames are explained in Frame Clauses ROWS, RANGE, and GROUPS. lag() and lead() ignore the frame and always work on the whole partition.

NULL handling. The SQL standard defines IGNORE NULLS for these functions ("the previous non-null value"). PostgreSQL 18 and earlier do not support it. The usual workaround for carrying the last known value forward is the gaps-and-islands technique (Gaps and Islands Problems): count non-null values with a running count() to form groups, then take the one value in each group.

Key takeaways

• lag() and lead() read a value from a row a fixed number of positions before or after the current one, inside the same partition.
• A third argument provides a default for the first or last rows instead of NULL.
• They count rows, not time units. Fill gaps first if you need calendar-based comparisons.
• first_value(), last_value(), and nth_value() use the frame, so last_value() needs an explicit frame to see the whole partition.

🧪 Practice

1. (Beginner) For each order, show the id of the previous and the next order placed in the shop (ignoring customers), ordered by placed_at.
2. (Intermediate) For each day in daily_sales, compute the percentage change from the previous row and flag days where revenue dropped more than 30%.
3. (Interview) "Given a table of user logins, find users who logged in on three consecutive days." Hint: Use lag(day, 2) within each user and compare it with day - 2, after removing duplicate logins on the same day.

Aggregate Window Functions

Every ordinary aggregate (sum, count, avg, min, max, string_agg, bool_or, and so on) can be used as a window function by adding OVER. This answers "part versus whole" questions in a single pass: the share of a product in its category's revenue, a customer's spend compared with the average, a running count of shipped orders.

Part versus whole. Each product's revenue as a percentage of its category's revenue. The query first groups by product, then applies window aggregates over the grouped rows:

SELECT p.name, cat.name AS category,
       sum(oi.quantity * oi.unit_price) AS revenue,                                  -- group aggregate
       sum(sum(oi.quantity * oi.unit_price)) OVER (PARTITION BY cat.id) AS category_revenue,
       round(100 * sum(oi.quantity * oi.unit_price)
                 / sum(sum(oi.quantity * oi.unit_price)) OVER (PARTITION BY cat.id), 1) AS pct_of_category
FROM order_items AS oi
JOIN products   AS p   ON p.id = oi.product_id
JOIN categories AS cat ON cat.id = p.category_id
GROUP BY p.id, p.name, cat.id, cat.name
ORDER BY cat.id, revenue DESC;

--         name        |  category   | revenue | category_revenue | pct_of_category
-- --------------------+-------------+---------+------------------+-----------------
--  SQL in Practice    | Books       |  117.00 |           171.00 |            68.4
--  Postgres Internals | Books       |   54.00 |           171.00 |            31.6
--  UltraBook 14       | Laptops     | 2598.00 |          3597.00 |            72.2
--  WorkBook 15        | Laptops     |  999.00 |          3597.00 |            27.8
--  Wireless Mouse     | Accessories |   89.70 |           127.20 |            70.5
--  USB-C Cable        | Accessories |   37.50 |           127.20 |            29.5

sum(sum(...)) OVER (...) reads as: the inner sum is the per-product group total, the outer sum ... OVER adds up those group totals per category.

Whole partition versus running. The presence of ORDER BY in OVER changes the meaning of the same aggregate:

Window	sum(revenue) means
OVER ()	Grand total, same on every row
OVER (PARTITION BY k)	Total of the row's partition
OVER (ORDER BY day)	Running total from the first row up to the current row (and its ties)
OVER (PARTITION BY k ORDER BY day)	Running total that restarts in each partition

FILTER in window aggregates. The FILTER clause (4.4) also works with window aggregates. It goes before OVER:

SELECT id, status,
       count(*) FILTER (WHERE status = 'shipped') OVER (ORDER BY placed_at) AS shipped_so_far
FROM orders;

--  id |  status   | shipped_so_far
-- ----+-----------+----------------
--   1 | shipped   |              1
--   2 | shipped   |              2
--   3 | pending   |              2
--   4 | cancelled |              2
--   5 | paid      |              2
--   6 | shipped   |              3

Limitations. DISTINCT inside an aggregate is not supported in window functions: count(DISTINCT customer_id) OVER (...) raises an error. Common workarounds are dense_rank() tricks or computing the distinct counts with GROUP BY and joining them back. Ordered-set aggregates such as percentile_cont cannot be used as window functions either.

Key takeaways

• Any aggregate becomes a window function with OVER, keeping every row while adding the aggregate value.
• On grouped queries, agg(agg(x)) OVER (...) aggregates the group results, which makes part-versus-whole percentages a single query.
• Adding ORDER BY inside OVER turns a total into a running total.
• FILTER (WHERE ...) works with window aggregates. DISTINCT and ordered-set aggregates do not.

🧪 Practice

1. (Beginner) For each customer, show their country, total spending, and the total spending of their country.
2. (Intermediate) For each order, list its total and the largest order total the same customer had placed before it. Hint: max() with an appropriate frame ending at 1 PRECEDING.
3. (Interview) "How would you compute, for each day, the number of distinct users active in the previous 7 days?" Hint: count(DISTINCT ...) OVER is not allowed. Compare a self join on a date range, generate_series with a lateral subquery, and pre-aggregated daily user sets.

Frame Clauses ROWS, RANGE, and GROUPS

Partitions say which group a row belongs to. The frame says which rows of that group an aggregate or value function looks at for the current row: everything so far, the previous three rows, the last seven days. Frames are what make moving averages and sliding windows possible, and the default frame explains the most common window function surprises.

Syntax

OVER (
    [PARTITION BY ...] ORDER BY ...
    { ROWS | RANGE | GROUPS }                     -- frame mode: how to measure distance
    BETWEEN frame_start AND frame_end
    [EXCLUDE { CURRENT ROW | GROUP | TIES | NO OTHERS }]
)
-- frame_start / frame_end:
--   UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

ROWS 2 PRECEDING alone is shorthand for ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

The three modes. They differ in how "distance from the current row" is measured, which matters only when the ORDER BY value has ties or gaps:

Mode	Distance is counted in	1 PRECEDING means
ROWS	Physical rows	The one row immediately before
RANGE	Values of the single ORDER BY column	Every row whose value is within 1 (or '1 day') of the current value
GROUPS (PostgreSQL 11+)	Groups of peers (rows with equal ORDER BY values)	The previous group of tied rows

SELECT x,
  sum(x) OVER (ORDER BY x ROWS   BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
  sum(x) OVER (ORDER BY x RANGE  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum,
  sum(x) OVER (ORDER BY x GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)         AS groups_sum
FROM (VALUES (10), (20), (20), (30)) AS t(x);

--  x  | rows_sum | range_sum | groups_sum
-- ----+----------+-----------+------------
--  10 |       10 |        10 |         10
--  20 |       30 |        50 |         50     <- RANGE/GROUPS include the tied peer row
--  20 |       50 |        50 |         50
--  30 |       80 |        80 |         70     <- GROUPS: previous peer group (20, 20) + current (30)

 Frame "UNBOUNDED PRECEDING .. CURRENT ROW" when the current row is the FIRST 20:

 ORDER BY x:      10    20    20    30
                        ^ current row

 ROWS:          [ 10    20 ]  20    30      stops at the current physical row  -> 30
 RANGE:         [ 10    20    20 ]  30      includes every peer of the current -> 50

The default frame. If you write no frame clause:

Window has ORDER BY?	Default frame	Effect
No	Whole partition	sum() is the partition total
Yes	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	sum() is a running total including peers of the current row

The "including peers" part surprises people: with ties in the order key, a running total jumps by the whole tied group at once, and tied rows show the same value. If you want a strictly row-by-row running total, use ROWS and a unique order key.

Value-based ranges with dates. RANGE with an offset (PostgreSQL 11+) requires exactly one ORDER BY column of a type that supports arithmetic: numbers, dates, timestamps (with an interval offset). This is how to express a true time window over data with gaps:

SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW)         AS last_3_rows,
       sum(revenue) OVER (ORDER BY day RANGE BETWEEN '2 days' PRECEDING AND CURRENT ROW) AS last_3_days
FROM daily_sales;

--     day     | revenue | last_3_rows | last_3_days
-- ------------+---------+-------------+-------------
--  ...
--  2025-03-05 |  200.00 |      440.00 |      440.00
--  2025-03-07 |   60.00 |      350.00 |      260.00   <- ROWS reaches back to 03-04; RANGE covers 03-05..03-07
--  2025-03-08 |  110.00 |      370.00 |      170.00   <- RANGE covers 03-06..03-08 (03-06 has no row)
--  2025-03-09 |  130.00 |      300.00 |      300.00

EXCLUDE. Removes rows from the frame after it is built: EXCLUDE CURRENT ROW (the current row only), EXCLUDE GROUP (the current row and its peers), EXCLUDE TIES (the peers but not the current row). Example: compare each value with the average of all other rows:

SELECT day, revenue,
       round(avg(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                EXCLUDE CURRENT ROW), 2) AS avg_of_other_days
FROM daily_sales;

Which functions use the frame. Aggregates, first_value(), last_value(), and nth_value() use it. Ranking functions, lag(), and lead() ignore it.

Key takeaways

• The frame selects the rows, within the partition, that a frame-aware function sees for the current row.
• ROWS counts physical rows, RANGE measures distance in ORDER BY values, GROUPS counts groups of tied rows.
• The default frame with ORDER BY is RANGE UNBOUNDED PRECEDING .. CURRENT ROW, which includes tied peers. Use ROWS for strictly row-by-row behavior.
• RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW gives a true time window that handles missing days correctly.

🧪 Practice

1. (Beginner) Compute a 3-row centered sum (previous, current, and next row) of daily_sales.revenue.
2. (Intermediate) Insert a second row for an existing order date into a copy of orders, then compare count(*) OVER (ORDER BY placed_at::date) with and without ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Explain the difference.
3. (Interview) "Why might a running total in a report show the same value on two consecutive rows, and how would you fix it?" Hint: Peers and the default RANGE frame.

Named Windows

A report often computes several window functions over the same window: the running sum, running average, and running maximum, all OVER (PARTITION BY customer_id ORDER BY placed_at). Repeating the window definition is verbose and invites mistakes when one copy is changed and the others are not. The WINDOW clause names a window definition once and lets every function refer to it.

Syntax. WINDOW name AS (definition) goes after HAVING and before ORDER BY. Functions then use OVER name:

SELECT customer_id, id AS order_id, placed_at::date,
       row_number() OVER w AS nth_order,
       count(*)     OVER w AS orders_so_far,
       min(placed_at::date) OVER w AS first_order_date
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at)
ORDER BY customer_id, placed_at;

Extending a named window. OVER (name ...) copies the named definition and adds to it. You can add an ORDER BY (if the named window has none) or a frame clause (if it has none). You cannot add or change PARTITION BY:

SELECT day, revenue,
       sum(revenue) OVER w                                                AS running_total,
       round(avg(revenue) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3   -- w + a frame
FROM daily_sales
WINDOW w AS (ORDER BY day)
ORDER BY day;

--     day     | revenue | running_total | moving_avg_3
-- ------------+---------+---------------+--------------
--  2025-03-01 |  120.00 |        120.00 |       120.00
--  2025-03-02 |   80.00 |        200.00 |       100.00
--  2025-03-03 |  150.00 |        350.00 |       116.67
--  ...

Several named windows can be defined, separated by commas, and one may build on another:

WINDOW by_customer AS (PARTITION BY customer_id),
       by_customer_time AS (by_customer ORDER BY placed_at)    -- adds ORDER BY to by_customer

Note the difference between OVER w and OVER (w): both use w unchanged, but OVER (w) copies the definition, which is not allowed when w contains a frame clause. Write OVER w when you do not modify the window.

Key takeaways

• WINDOW name AS (...), placed before ORDER BY, defines a window once for several functions.
• OVER (name ROWS ...) or OVER (name ORDER BY ...) extends a named window, but cannot change its partitioning.
• Named windows make reports shorter and keep related calculations consistent.

🧪 Practice

1. (Beginner) Rewrite the "time since previous order" query from the LAG and LEAD topic using a named window.
2. (Intermediate) Define WINDOW cat AS (PARTITION BY category_id) and, building on it, a second window ordered by price. Use them to return each product's category average, its rank in the category, and its price minus the next cheaper product's price.
3. (Interview) "Do functions sharing a named window run faster than the same functions with copied definitions?" Hint: Check EXPLAIN for both versions and count the WindowAgg and Sort nodes.

Running Totals and Moving Averages

The two most requested time-series calculations in business reporting are running totals ("revenue month-to-date") and moving averages ("7-day average of signups", which smooths out daily noise). Both are frames over an ordered window, but real data adds complications: totals that must reset each period, ties in the order key, and missing days.

Running total and cumulative share

SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day)                                         AS running_total,
       sum(revenue) OVER (PARTITION BY date_trunc('week', day) ORDER BY day)    AS week_to_date,    -- resets every Monday
       round(100.0 * sum(revenue) OVER (ORDER BY day) / sum(revenue) OVER (), 1) AS pct_of_total
FROM daily_sales;

--     day     | revenue | running_total | week_to_date | pct_of_total
-- ------------+---------+---------------+--------------+--------------
--  2025-03-01 |  120.00 |        120.00 |       120.00 |         12.8
--  2025-03-02 |   80.00 |        200.00 |       200.00 |         21.3
--  2025-03-03 |  150.00 |        350.00 |       150.00 |         37.2   <- Monday: week_to_date restarts
--  2025-03-04 |   90.00 |        440.00 |       240.00 |         46.8
--  2025-03-05 |  200.00 |        640.00 |       440.00 |         68.1
--  2025-03-07 |   60.00 |        700.00 |       500.00 |         74.5
--  2025-03-08 |  110.00 |        810.00 |       610.00 |         86.2
--  2025-03-09 |  130.00 |        940.00 |       740.00 |        100.0

day is the primary key, so there are no ties and the default frame behaves like ROWS. When the order key can repeat (for example, ordering transactions by date), write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and add a unique tie-breaker such as id, or tied rows will share one total.

Moving averages: rows versus days. A "3-day moving average" is not the same as a "3-row moving average" when days are missing:

SELECT day, revenue,
       round(avg(revenue) OVER (ORDER BY day ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW), 2)        AS ma_3_rows,
       round(avg(revenue) OVER (ORDER BY day RANGE BETWEEN '2 days' PRECEDING AND CURRENT ROW), 2) AS ma_3_days
FROM daily_sales;

--     day     | revenue | ma_3_rows | ma_3_days
-- ------------+---------+-----------+-----------
--  ...
--  2025-03-07 |   60.00 |    116.67 |    130.00   <- rows: 03-04, 03-05, 03-07; days: 03-05..03-07
--  2025-03-08 |  110.00 |    123.33 |     85.00   <- days: only 03-07 and 03-08 have rows

Note what ma_3_days does on 2025-03-08: it averages the two existing days, (60 + 110) / 2 = 85. If a missing day really means "zero revenue", that is wrong, and the average should be (0 + 60 + 110) / 3. To get it, first generate every calendar day and fill missing ones with zero (Time Bucketing and Series Generation), then apply a ROWS frame:

WITH calendar AS (
    SELECT d::date AS day
    FROM generate_series(date '2025-03-01', date '2025-03-09', interval '1 day') AS d
),
filled AS (
    SELECT c.day, coalesce(s.revenue, 0) AS revenue          -- missing day -> 0
    FROM calendar AS c
    LEFT JOIN daily_sales AS s USING (day)
)
SELECT day, revenue,
       round(avg(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3
FROM filled;
-- 2025-03-08 now shows (0 + 60 + 110) / 3 = 56.67

Partial windows at the start. The first rows have fewer than N rows behind them, so their "3-day average" uses only one or two values. Hide them if they would mislead, by counting the rows in the frame:

CASE WHEN count(*) OVER w3 = 3 THEN avg(revenue) OVER w3 END   -- NULL until the window is full
-- with WINDOW w3 AS (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Other common variants

Calculation	Window
Year-to-date	sum(x) OVER (PARTITION BY extract(year FROM d) ORDER BY d)
Centered 5-day average	avg(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Running maximum (all-time high)	max(x) OVER (ORDER BY d)
Drawdown from the high	x - max(x) OVER (ORDER BY d)
Same day last week	lag(x, 7) OVER (ORDER BY d) on a gap-free series

Key takeaways

• A running total is sum() OVER (ORDER BY ...). Add PARTITION BY a period (date_trunc('month', ...)) to reset it each period.
• With ties in the order key, use ROWS and a unique tie-breaker so each row gets its own running value.
• ROWS moving averages count rows. RANGE ... interval averages count calendar time but skip missing days. Fill gaps with generate_series when missing means zero.
• Suppress values for the first rows when the window is not yet full.

🧪 Practice

1. (Beginner) Compute the running number of orders over time, ordered by placed_at, and the running number of distinct customers who have ordered so far. Hint: For the second, mark each customer's first order with row_number() and run a sum over the marks.
2. (Intermediate) Using a filled calendar, produce a 7-day moving average of revenue for March 2025, returning NULL for the first six days.
3. (Interview) "Your dashboard shows a 30-day moving average of signups over a 3-year table with 200 million events, and it is slow. How do you make it fast?" Hint: Pre-aggregate to one row per day before applying the window, consider a summary table or materialized view, and check which index serves the date filter.

<a id="53-full-text-search"></a>

5.3 Full-Text Search

This subchapter builds a complete search feature inside PostgreSQL: how text becomes searchable lexemes, how to write and rank queries, how to index them, and how trigram and phonetic matching tolerate typos. The tsvector and tsquery types themselves were introduced in 3.5.

Text Search Configurations and Dictionaries

A search for "running shoes" should find a product described as "shoes for runners", but not one that only mentions "the". Matching like this needs knowledge of a language: which words are noise, and which forms ("run", "running", "runs") are the same word. In PostgreSQL that knowledge lives in a text search configuration, a named recipe that turns raw text into normalized lexemes. Every full-text function takes a configuration as its first argument, and picking the right one decides what your search can find.

The pipeline. A configuration has two parts: a parser that splits text into typed tokens, and a mapping that sends each token type through a list of dictionaries:

 "The Runners were running 5km in Berlin!"
                     │
                 [ parser ]              splits text into tokens and labels each one
                     │
   asciiword "The"   asciiword "Runners"   numword "5km"   blank "!" ...
                     │
     [ dictionaries for that token type, tried in order ]
       english_stem:  "The" -> stop word, dropped
                      "Runners" -> runner,  "running" -> run
       simple:        "5km" -> 5km
                     │
          lexemes: berlin, run, runner, 5km

A dictionary can return lexemes (the token is recognized), an empty result (a stop word, discarded), or nothing (not recognized: pass the token to the next dictionary in the list).

Inspecting the pipeline. ts_debug shows every token, the dictionaries consulted, and the result. It is the first tool to reach for when a search does not find what you expect:

SELECT alias, token, dictionaries, lexemes
FROM ts_debug('english', 'The Runners were running 5km in Berlin!')
WHERE alias <> 'blank';

--    alias   |  token  |  dictionaries  | lexemes
-- -----------+---------+----------------+----------
--  asciiword | The     | {english_stem} | {}          <- stop word
--  asciiword | Runners | {english_stem} | {runner}
--  asciiword | were    | {english_stem} | {}
--  asciiword | running | {english_stem} | {run}
--  numword   | 5km     | {simple}       | {5km}
--  asciiword | in      | {english_stem} | {}
--  asciiword | Berlin  | {english_stem} | {berlin}

SELECT ts_lexize('english_stem', 'running');   -- {run}: test one dictionary on one word

Built-in configurations. PostgreSQL ships with a configuration for each of about 30 languages (english, german, spanish, french, ...) plus simple, which only lower-cases words and removes nothing. List them with \dF in psql. The configuration used when you omit the argument is the default_text_search_config setting (often pg_catalog.english).

The configuration really matters:

SELECT to_tsvector('english', 'Les cafés de Paris'),    -- 'café':2 'de':3 'les':1 'pari':4
       to_tsvector('french',  'Les cafés de Paris');    -- 'caf':2 'le':1 'paris':4

The English configuration keeps the French stop words "les" and "de" and wrongly stems "Paris" to "pari".

Dictionary types

Dictionary template	What it does	Typical use
simple	Lower-cases, optionally removes stop words	Codes, names, languages without a stemmer
snowball (english_stem, ...)	Algorithmic stemming plus a stop word list	The default for most languages
ispell	Morphological dictionary from Hunspell/Ispell files	Better stemming, compound words (German)
synonym	Replaces a word with a synonym from a file	"postgres" -> "postgresql"
thesaurus	Replaces multi-word phrases	"supernova star" -> "sn"
unaccent (extension)	Removes accents, used as a filtering dictionary	"café" matches "cafe"

A custom configuration. Real applications often need small changes, such as ignoring accents. Copy a built-in configuration and change its mappings:

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION shop_english (COPY = english);

ALTER TEXT SEARCH CONFIGURATION shop_english
    ALTER MAPPING FOR hword, hword_part, word         -- token types for words with non-ASCII letters
    WITH unaccent, english_stem;                       -- strip accents first, then stem

SELECT to_tsvector('english',      'Crème brûlée in a café'),   -- 'brûlée':2 'café':5 'crème':1
       to_tsvector('shop_english', 'Crème brûlée in a café');   -- 'brule':2 'cafe':5 'creme':1

SELECT to_tsvector('shop_english', 'café') @@ to_tsquery('shop_english', 'cafe');   -- true

The golden rule: same configuration on both sides. Documents and queries must be processed with the same configuration. If documents are stemmed with english and the query uses simple, a search for "running" looks for the lexeme running, but the documents only contain run, so nothing matches. For multilingual data, store each row's language in a regconfig column and pass it to both to_tsvector and the query function.

Key takeaways

• A text search configuration = a parser that splits text into typed tokens + dictionaries that turn tokens into lexemes or discard them.
• Use ts_debug and ts_lexize to see exactly how text is processed.
• Pick the configuration for the text's language. simple does no stemming and is right for codes and names.
• Build custom configurations with COPY and ALTER MAPPING, for example to add unaccent or synonyms. Always use the same configuration for documents and queries.

🧪 Practice

1. (Beginner) Run ts_debug('english', ...) on a sentence containing an email address, a URL, and a hyphenated word. Which token types does the parser recognize?
2. (Intermediate) Create a synonym dictionary file that maps pg and postgres to postgresql, add it to a custom configuration before english_stem, and show that a search for pg finds a document that mentions PostgreSQL.
3. (Interview) "Your product catalog has descriptions in English, German, and Spanish. How would you design full-text search for it?" Hint: Consider a per-row regconfig column, one index per language versus one expression index, and how to detect the language of the user's query.

Parsing Documents into tsvector

A searchable document is usually not one column. A product has a name, a description, perhaps tags and a brand, and a match in the name should count more than a match deep in the description. This topic covers how to combine several fields into one tsvector, how to mark their importance, and where to store the result.

The sample data. This subchapter adds a description column to the shop's products:

ALTER TABLE products ADD COLUMN description text;

UPDATE products SET description = CASE sku
  WHEN 'LAP-001'  THEN 'Lightweight 14-inch laptop with a long battery life, ideal for travel and programming.'
  WHEN 'LAP-002'  THEN 'Durable 15-inch work laptop with a full keyboard. Discontinued model.'
  WHEN 'ACC-001'  THEN 'Braided USB-C charging cable, 2 meters, supports fast charging for laptops and phones.'
  WHEN 'ACC-002'  THEN 'Silent wireless mouse with a USB receiver and two-year battery.'
  WHEN 'BK-001'   THEN 'A practical guide to writing SQL queries: joins, grouping, window functions, and indexing.'
  WHEN 'BK-002'   THEN 'How PostgreSQL stores data, plans queries, and uses indexes, explained from the inside.'
  WHEN 'MISC-001' THEN 'A gift card for any product in the shop.'
END;

Positions. Each lexeme in a tsvector carries the positions where it occurred. Stop words are dropped, but they still count when positions are assigned, so phrase searches know that two words were separated by a removed word:

SELECT to_tsvector('english', description) FROM products WHERE sku = 'BK-001';
-- 'function':11 'group':9 'guid':3 'index':13 'join':8 'practic':2 'queri':7 'sql':6 'window':10 'write':5
--  position 1 ("A") and 4 ("to") were stop words, but positions keep counting

Combining fields and assigning weights. The || operator concatenates two tsvectors, shifting the positions of the right one so they follow the left. setweight labels every lexeme with a weight A, B, C, or D (the default). Ranking functions can then score A matches higher:

SELECT setweight(to_tsvector('english', 'SQL in Practice'), 'A')
    || setweight(to_tsvector('english', 'A practical guide to writing SQL queries'), 'B');
-- 'guid':6B 'practic':3A,5B 'queri':10B 'sql':1A,9B 'write':8B
--  "sql" appears in the title (1A) and the description (9B)

NULL makes the whole vector NULL. to_tsvector(NULL) is NULL, and concatenating anything with NULL gives NULL, so one missing field makes the entire row unsearchable. Always wrap optional columns: to_tsvector('english', coalesce(description, '')).

Where to keep the tsvector

Approach	How	Pros	Cons
Expression index only	CREATE INDEX ... USING gin (to_tsvector('english', body))	No extra column	Every query must repeat the exact expression. Ranking recomputes to_tsvector per row
Stored generated column (PostgreSQL 12+)	search tsvector GENERATED ALWAYS AS (...) STORED	Always in sync, simple queries, fast ranking	Extra storage. Expression must be immutable
Trigger-maintained column	BEFORE INSERT OR UPDATE trigger (9.3)	Can use data from other tables (tags, category names)	More code to maintain

The generated column is the usual choice when all fields are in the same row:

ALTER TABLE products ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', name), 'A') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'B')
    ) STORED;

The configuration must be written explicitly ('english', not the one-argument form), because a generated column requires an immutable expression (3.5).

Other ways to build a tsvector

-- Index only the string values of a JSON document (not keys, numbers, or booleans)
SELECT jsonb_to_tsvector('english', '{"title": "Fast cables", "tags": ["usb", "charging"], "price": 12}',
                         '["string"]');
-- 'cabl':6 'charg':3 'fast':5 'usb':1

-- Use array elements as lexemes as they are (no stemming), for example tags
SELECT array_to_tsvector(ARRAY['usb', 'charging']);              -- 'charging' 'usb'

-- Inspect and shrink
SELECT length(to_tsvector('english', 'the cat sat on the cat mat'));   -- 3 distinct lexemes
SELECT strip(to_tsvector('english', 'the cat sat on the cat mat'));    -- 'cat' 'mat' 'sat': no positions

strip removes positions and weights, which makes the vector smaller but disables phrase search and weight-based ranking.

Size limits. A single tsvector can hold at most 1 MB and positions only up to 16,383. Words beyond that position are still indexed, but with the maximum position, so phrase search and ranking get less accurate for very long documents. Very large documents are better split into sections or pages.

Key takeaways

• tsvector lexemes keep positions, and removed stop words still occupy a position.
• Combine fields with || and mark their importance with setweight(..., 'A'..'D').
• Wrap nullable columns in coalesce(col, ''), or one NULL hides the entire row from search.
• A stored generated tsvector column with an explicit configuration is the simplest way to keep the search document in sync. Use a trigger when the document needs data from other tables.

🧪 Practice

1. (Beginner) Add the generated search column to products and look at its value for the USB-C cable. Which lexemes have both an A and a B position, and why?
2. (Intermediate) Extend the search document so a product is also found by its category name (weight C). Since the category is in another table, implement it with a trigger, and make sure renaming a category updates its products.
3. (Interview) "Should the search vector be a generated column or an expression index?" Hint: Compare disk space, write cost, ranking cost (which needs the vector for every matching row), and how easy it is to change the definition later.

Query Operators and tsquery

A tsvector is only half of a search. The other half is the tsquery: which lexemes must appear, which must not, and in what arrangement. Knowing the operators lets you build precise searches (an exact phrase, a prefix for autocomplete, a match only in titles) and translate a search box's syntax into SQL.

Operators

Operator	Meaning	Example (on the shop's products)	Matches
&	Both	'laptop & battery'	UltraBook 14
|	Either	'sql | postgres'	Both books
!	Not	'laptop & !discontinued'	UltraBook 14, USB-C Cable
<->	Followed immediately by	'charging <-> cable'	USB-C Cable
<N>	Followed by, exactly N positions later	'charging <2> laptops'	USB-C Cable ("charging for laptops")
:*	Prefix	'index:*'	Both books ("indexing", "indexes")
:A, :AB	Only lexemes with these weights	'cable:A'	USB-C Cable (in the name, weight A)
( )	Grouping	'laptop & (mouse | cable)'	USB-C Cable

SELECT name FROM products WHERE search @@ to_tsquery('english', 'laptop & !discontinued');
-- UltraBook 14, USB-C Cable     (the cable's description mentions "laptops")

SELECT name FROM products WHERE search @@ to_tsquery('english', 'charging <2> laptops');
-- USB-C Cable                   ("for" is a stop word, but it still occupies position 2)

SELECT name FROM products WHERE search @@ to_tsquery('english', 'cable:A');
-- USB-C Cable                   ("cable" in the name, not just in a description)

The words in a tsquery go through the same configuration as documents, so 'laptops' becomes 'laptop' and 'the & laptop' becomes just 'laptop' because "the" is a stop word.

Phrase search details. phraseto_tsquery builds <-> chains from plain text, and it accounts for removed stop words by increasing the distance:

SELECT phraseto_tsquery('english', 'battery for the travel');
-- 'batteri' <3> 'travel'          "for" and "the" were removed, so the distance is 3

Phrase operators need positions, so they do not work on stripped vectors, and they are slower than & because each candidate row must be checked against positions.

Choosing a query constructor. All four functions were introduced in 3.5. The practical rule:

Input source	Function	Why
A search box typed by end users	websearch_to_tsquery	Understands "quoted phrases", -exclusion, and or. Never raises a syntax error
A list of words that must all appear	plainto_tsquery	Ignores punctuation and operators
An exact phrase	phraseto_tsquery	Builds <-> / <N> chains
Queries built by your code, with operators	to_tsquery	Full operator syntax. Raises errors on malformed input

SELECT websearch_to_tsquery('english', 'laptop -discontinued "battery life"');
-- 'laptop' & !'discontinu' & 'batteri' <-> 'life'

Building queries in code. Never concatenate user input into a to_tsquery string: a stray & or ( raises an error. Combine tsquery values with operators instead:

SELECT to_tsquery('english', 'laptop') && to_tsquery('english', 'cable | mouse');   -- 'laptop' & ( 'cabl' | 'mous' )
SELECT !! to_tsquery('english', 'book');                                            -- !'book'

-- Prefix search for autocomplete, built safely from the user's last word ($1)
SELECT name FROM products
WHERE search @@ to_tsquery('english', quote_literal($1) || ':*');

quote_literal wraps the input in quotes (escaping any quotes inside it), so operator characters typed by the user are parsed as text and cannot break the query syntax.

Key takeaways

• &, |, ! combine lexemes. <-> and <N> require order and distance. :* matches prefixes. :A restricts matches to weighted fields.
• Query words are normalized by the same configuration as documents, including stemming and stop word removal.
• Use websearch_to_tsquery for user input. Build programmatic queries with &&, ||, and !! on tsquery values, not string concatenation.
• Phrase operators need positions and cost more than plain &.

🧪 Practice

1. (Beginner) Find products whose name or description contains a word starting with "charg" but not the word "phone".
2. (Intermediate) Write a query that searches only product names (weight A) for a user's input, and falls back to searching names and descriptions if no name matches. Use a CTE for each attempt.
3. (Interview) "A user searches for "to be or not to be" and gets no results, although the document contains that phrase. Why?" Hint: Run the phrase through phraseto_tsquery('english', ...) and look at which words remain.

Ranking and Highlighting Results

Finding matching rows is not enough: a search with 500 matches must show the best ten first, and show why each one matched. PostgreSQL provides ranking functions that score how well a document matches a query, and ts_headline to show snippets with the matching words highlighted.

The two ranking functions

Function	Scores based on	Good for
ts_rank(vector, query)	How often the query lexemes occur, weighted by A/B/C/D	General relevance
ts_rank_cd(vector, query)	Cover density: how close together the matching lexemes are	Multi-word queries where proximity matters. Needs positions

Both return a real number where higher is more relevant. The values are only meaningful for comparing documents against the same query, not as absolute scores.

SELECT name,
       round(ts_rank(search, q)::numeric, 4)    AS rank,
       round(ts_rank_cd(search, q)::numeric, 4) AS rank_cd
FROM products, to_tsquery('english', 'usb | laptop') AS q    -- q is computed once and reused
WHERE search @@ q
ORDER BY rank DESC;

--       name      |  rank  | rank_cd
-- ----------------+--------+---------
--  USB-C Cable    | 0.4559 |  1.8000    <- "usb" in the name (weight A) and description
--  UltraBook 14   | 0.1216 |  0.4000
--  WorkBook 15    | 0.1216 |  0.4000
--  Wireless Mouse | 0.1216 |  0.4000

Weights. An optional first argument sets the score of each weight, in the order {D, C, B, A}. The default is {0.1, 0.2, 0.4, 1.0}. To make title matches dominate even more:

ts_rank('{0.0, 0.0, 0.1, 1.0}', search, q)      -- ignore C and D, B matches count little

Normalization. Long documents contain more words and therefore more matches. An optional last argument (a bit mask) normalizes the score by document length: 1 divides by 1 + log(length), 2 by the length, 32 scales the result into the range 0 to 1, and flags can be combined with |, for example ts_rank(search, q, 1|32). Without normalization, long documents tend to win.

Highlighting with ts_headline. Returns an excerpt of the original text with matching words wrapped in markers:

SELECT ts_headline('english', description, to_tsquery('english', 'laptop & battery'))
FROM products WHERE sku = 'LAP-001';
-- Lightweight 14-inch <b>laptop</b> with a long <b>battery</b> life, ideal for travel and programming.

SELECT ts_headline('english', description, to_tsquery('english', 'charging'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=8, MinWords=3')
FROM products WHERE sku = 'ACC-001';
-- <mark>charging</mark> cable, 2 meters

Useful options: StartSel/StopSel (markers), MaxWords/MinWords (excerpt length), MaxFragments (return several excerpts instead of one), FragmentDelimiter (text between fragments), and HighlightAll (highlight the whole document).

Security. ts_headline inserts markers into raw text. If the text may contain HTML and the result is rendered in a browser, escape the document first or use markers that your front end replaces after escaping. Otherwise you have an injection vulnerability.

Performance: rank many, highlight few. ts_headline works on the original text and is expensive, and ranking must be computed for every matching row before sorting. Compute headlines only for the rows actually displayed:

WITH q AS (SELECT websearch_to_tsquery('english', 'laptop battery') AS q),
top AS (
    SELECT p.id, p.name, p.description, ts_rank(p.search, q.q) AS rank, q.q
    FROM products AS p, q
    WHERE p.search @@ q.q
    ORDER BY rank DESC
    LIMIT 10                                           -- cut first...
)
SELECT name, rank,
       ts_headline('english', description, q, 'MaxWords=20') AS snippet   -- ...then highlight 10 rows
FROM top
ORDER BY rank DESC;

Key takeaways

• ts_rank scores by frequency and weight, ts_rank_cd also rewards matching words that appear close together.
• Customize weights with a {D, C, B, A} array and normalize for document length with the bit-mask argument.
• ts_headline returns highlighted excerpts from the original text. Escape HTML before displaying them.
• Rank and limit first, then compute headlines only for the rows shown.

🧪 Practice

1. (Beginner) Search products for 'laptop | battery' and order them by ts_rank. Then repeat with ts_rank_cd and explain any difference in order.
2. (Intermediate) Build a search query that returns the top 5 products for a user's input with a highlighted snippet of at most 15 words, using <em> tags, and a rank that is normalized for document length.
3. (Interview) "Your search ranks a 50-page manual above a short, precise FAQ entry for almost every query. How would you fix the ranking?" Hint: Look at length normalization, weights for titles, and splitting long documents into sections.

GIN Indexes for Text Search

Without an index, search @@ query must compute (or read) the tsvector of every row and test it, which is a full table scan. On a few thousand rows this is fine. On millions it takes seconds. The standard index for full-text search is GIN (Generalized Inverted Index), the same structure that powers the index at the back of a book.

How an inverted index works. Instead of storing "row -> its words", GIN stores "word -> the rows containing it". A query for laptop & battery looks up both words and intersects their row lists, without reading any other rows:

 Table rows                          GIN index (lexeme -> row ids)
 ┌────┬──────────────────────┐       ┌───────────┬────────────┐
 │ 1  │ laptop battery life  │       │ batteri   │ 1, 4       │
 │ 3  │ cable laptop charg   │  ──>  │ cabl      │ 3          │
 │ 4  │ mouse usb battery    │       │ laptop    │ 1, 2, 3    │
 │ 2  │ work laptop keyboard │       │ mous      │ 4          │
 └────┴──────────────────────┘       │ ...       │ ...        │
                                     └───────────┴────────────┘
 'laptop & battery'  ->  {1,2,3} ∩ {1,4}  =  {1}

Creating the index. Index either the stored column or an expression:

-- On a stored tsvector column (such as products.search)
CREATE INDEX products_search_gin ON products USING gin (search);

-- Or on an expression, if there is no stored column
CREATE INDEX docs_body_fts ON docs USING gin (to_tsvector('english', body));

On a live production table, add CONCURRENTLY so the build does not block writes (6.3).

The query must match the indexed expression. An expression index is used only when the query contains exactly the same expression, including the configuration argument. On a table docs with 200,000 rows and the expression index above:

EXPLAIN (COSTS OFF)
SELECT id FROM docs WHERE to_tsvector('english', body) @@ to_tsquery('english', 'vacuum');
--  Bitmap Heap Scan on docs
--    Recheck Cond: (to_tsvector('english'::regconfig, body) @@ '''vacuum'''::tsquery)
--    ->  Bitmap Index Scan on docs_body_fts            <- index used
--          Index Cond: (to_tsvector('english'::regconfig, body) @@ '''vacuum'''::tsquery)

EXPLAIN (COSTS OFF)
SELECT id FROM docs WHERE to_tsvector(body) @@ to_tsquery('english', 'vacuum');   -- no configuration
--  Gather
--    ->  Parallel Seq Scan on docs                     <- index ignored: different expression
--          Filter: (to_tsvector(body) @@ '''vacuum'''::tsquery)

A GIN index is always read with a bitmap scan: the index returns a set of candidate rows, and the heap rows are fetched afterwards. With an expression index, each candidate row's tsvector is recomputed during that fetch (the Recheck Cond), another reason to prefer a stored column on large documents.

Write cost and the pending list. Inserting one document touches one index entry per distinct lexeme, so GIN updates are expensive. By default (fastupdate = on), new entries go into an unsorted pending list that is merged into the main index in bulk, by VACUUM, by autovacuum, or when the list exceeds gin_pending_list_limit (4 MB by default). This makes writes faster but means a search must also scan the pending list, and an occasional insert pays for a large merge. For tables with heavy write traffic and strict latency requirements, tune gin_pending_list_limit or set fastupdate = off on the index.

GIN versus GiST for text search

	GIN	GiST
Lookup speed	Fast, exact	Slower, lossy (needs rechecking rows)
Build and update speed	Slower	Faster
Size	Larger	Smaller
Best for	Most search workloads	Very write-heavy tables, or combined with other GiST-indexable columns

What the index cannot do. The GIN index finds matching rows, but it does not store positions or rank information. ORDER BY ts_rank(...) LIMIT 10 must compute the rank for every matching row, then sort. A query matching a very common word ("data") in a large table therefore stays slow, even with the index. Remedies include requiring more specific queries, limiting the candidate set with other filters (date, category), or the external RUM extension, which stores positions in the index and can return results in rank order.

Key takeaways

• GIN is an inverted index: lexeme -> rows. It answers @@ queries by combining row lists.
• An expression index is used only if the query repeats the expression exactly, including the configuration. A stored tsvector column avoids that trap and avoids recomputation on recheck.
• GIN writes are expensive. The pending list (fastupdate) batches them at the cost of occasional slow merges.
• The index filters but does not rank: ranking cost grows with the number of matching rows.

🧪 Practice

1. (Beginner) Create the docs table with 200,000 rows of random words, add the GIN expression index, and compare EXPLAIN ANALYZE timings of a search with and without the index (SET enable_bitmapscan = off to disable it).
2. (Intermediate) Compare the size of a GIN and a GiST index on the same tsvector column with pg_relation_size, and time a search and a bulk insert of 10,000 rows with each.
3. (Interview) "Search is fast for rare words but takes 3 seconds for a common word in a 20-million-row table, even though EXPLAIN shows the GIN index is used. Why?" Hint: Look at how many rows the bitmap scan returns, and what has to happen to all of them before LIMIT 10 applies.

Trigram Similarity with pg_trgm

Full-text search matches words. It cannot find "Wireless Mouse" when the user types "wirless mous", because neither misspelled word stems to a lexeme in the document. It also cannot help with substring searches like ILIKE '%book%', or with names, codes, and short strings that have no linguistic structure. The pg_trgm extension solves both problems by comparing strings by their trigrams: every sequence of three consecutive characters.

What a trigram is. Each word is lower-cased and padded with two spaces in front and one at the end, then cut into overlapping three-character pieces:

CREATE EXTENSION pg_trgm;

SELECT show_trgm('cable');
-- {"  c"," ca",abl,ble,cab,"le "}

Two strings are similar when they share many trigrams. A typo changes only a few trigrams, so a misspelled word still shares most of them with the correct one.

Similarity functions and operators

Function / operator	Compares	Returns
similarity(a, b)	Whole strings	Shared trigrams / all distinct trigrams, from 0 to 1
a % b	Whole strings	true if similarity(a, b) >= pg_trgm.similarity_threshold (default 0.3)
word_similarity(a, b)	a against the most similar part of b	0 to 1
a <% b	Same	true if word_similarity >= pg_trgm.word_similarity_threshold (default 0.6)
strict_word_similarity(a, b) / a <<% b	a against whole words in b	Stricter variant
a <-> b	Whole strings	Distance = 1 - similarity, for ordering by closeness

SELECT similarity('wireless mouse', 'wirless mouse');    -- 0.7058824

SELECT name, round(similarity(name, 'wirless mous')::numeric, 3) AS sim
FROM products
WHERE name % 'wirless mous';                               -- similar enough?
--       name      |  sim
-- ----------------+-------
--  Wireless Mouse | 0.556

-- A short search term inside a longer string: word_similarity is the better measure
SELECT name,
       round(word_similarity('mous', name)::numeric, 3)        AS word_sim,     -- 0.800
       round(strict_word_similarity('mous', name)::numeric, 3) AS strict_sim,   -- 0.571
       round(similarity('mous', name)::numeric, 3)             AS whole_sim     -- 0.250
FROM products
WHERE 'mous' <% name;

Nearest-match ordering. "Show the closest names, even if none is very close" uses the distance operator:

SELECT name, round((name <-> 'ultrabok')::numeric, 3) AS distance
FROM products
ORDER BY name <-> 'ultrabok'                                -- smaller distance = more similar
LIMIT 3;
--      name     | distance
-- --------------+----------
--  UltraBook 14 |    0.429
--  USB-C Cable  |    0.947
--  WorkBook 15  |    0.950

Indexing. pg_trgm provides two operator classes:

CREATE INDEX products_name_trgm_gin  ON products USING gin  (name gin_trgm_ops);
CREATE INDEX products_name_trgm_gist ON products USING gist (name gist_trgm_ops);

Query	GIN (gin_trgm_ops)	GiST (gist_trgm_ops)
%, <%, <<%	Yes	Yes
LIKE / ILIKE with any wildcards, such as '%book%'	Yes	Yes
Regular expressions ~, ~*	Yes	Yes
ORDER BY col <-> 'x' LIMIT n (nearest neighbor)	No	Yes
Lookup speed / size	Faster, usually larger	Slower, smaller

The LIKE '%...%' support is often the main reason to install pg_trgm: it is the only built-in way to index "contains" searches (4.2). The search string needs at least one full trigram (three characters) for the index to help.

Tuning thresholds. SET pg_trgm.similarity_threshold = 0.4; makes % stricter, for the session or (with ALTER DATABASE ... SET) as a default. Always use the operators in WHERE, not similarity(...) > 0.4: only the operators can use the index.

Limits of trigrams. Short strings have few trigrams, so one typo has a large effect. Transpositions hurt as much as completely different letters: similarity('cable', 'cabel') and similarity('cable', 'table') are both 0.33. Trigrams know nothing about language: "ran" and "run" are not similar. Use full-text search for meaning and trigrams for spelling.

Key takeaways

• pg_trgm compares strings by shared three-character sequences, which makes it tolerant of typos.
• % compares whole strings, <% finds a short term inside a longer string, and <-> orders by closeness.
• A GIN gin_trgm_ops index accelerates %, LIKE '%x%', ILIKE, and regular expressions. A GiST index adds nearest-neighbor ordering.
• Use the operators, not similarity() > x, so the index can be used. Tune pg_trgm.similarity_threshold to your data.

🧪 Practice

1. (Beginner) Use show_trgm on your own name and on a misspelled version. Count the shared trigrams and compute the similarity by hand, then check it with similarity().
2. (Intermediate) On the 200,000-row docs table, compare EXPLAIN ANALYZE for body ILIKE '%snapshot%' without an index, with a GIN trigram index, and with a full-text search for the same word. Which finds more rows, and why?
3. (Interview) "Implement an autocomplete for city names that tolerates typos and returns results in under 10 ms from a table of 3 million cities." Hint: Compare a prefix B-tree with text_pattern_ops, a GIN trigram index with %, and a GiST trigram index with <-> ordering. Think about population as a tie-breaker.

Fuzzy Matching and Spelling Tolerance

Users misspell words, type names the way they sound ("Catherine", "Kathryn"), and forget accents. A search that answers "no results" to a small typo feels broken. PostgreSQL provides several complementary techniques: edit distance, phonetic codes, accent removal, and trigram-based "did you mean" suggestions. The right choice depends on what kind of mistake you expect.

Edit distance with fuzzystrmatch. The Levenshtein distance is the minimum number of single-character insertions, deletions, and substitutions that turn one string into another:

CREATE EXTENSION fuzzystrmatch;

SELECT levenshtein('kitten', 'sitting');                -- 3: k->s, e->i, +g
SELECT levenshtein('postgres', 'postgers');             -- 2: a swap of two letters counts as 2 edits
SELECT levenshtein_less_equal('postgres', 'mysql', 2);  -- 3: gives up early once the distance exceeds 2

levenshtein has no index support, so it must be computed for every candidate row. The common pattern is to narrow candidates with an indexed trigram search first, then refine with edit distance:

SELECT name
FROM customers
WHERE name % 'Diego Ruis'                               -- indexed trigram filter: few candidates
ORDER BY levenshtein(lower(name), lower('Diego Ruis'))  -- precise ordering on the small set
LIMIT 5;

Phonetic matching. Phonetic algorithms reduce a word to a code representing how it sounds, so names that sound alike get the same code:

SELECT soundex('Robert'), soundex('Rupert'), difference('Robert', 'Rupert');
-- R163 | R163 | 4                      difference: 0 (no match) to 4 (codes identical)

SELECT soundex('Catherine'), soundex('Kathryn');       -- C365 | K365: soundex keeps the first letter
SELECT metaphone('Catherine', 8), metaphone('Kathryn', 8);   -- K0RN | K0RN: metaphone knows C can sound like K

SELECT dmetaphone('Schmidt'), dmetaphone_alt('Schmidt'),     -- XMT | SMT
       dmetaphone('Smith'),   dmetaphone_alt('Smith');       -- SM0 | XMT
-- Double Metaphone returns a primary and an alternate code; names match if any codes match

Algorithm	Function	Strengths	Weaknesses
Soundex	soundex, difference	Simple, standard	English only, first letter must match, many false matches
Metaphone	metaphone(text, max_len)	Better English pronunciation rules	English only
Double Metaphone	dmetaphone, dmetaphone_alt	Handles names of many origins, two codes	Still tuned for names, not general words
Daitch-Mokotoff (PostgreSQL 16+)	daitch_mokotoff	Designed for Slavic and Germanic surnames, returns several codes	Specialized

Phonetic codes are cheap to store. To search by them efficiently, store the code in a generated column or an expression index and compare with =.

Accent-insensitive matching. unaccent removes diacritics:

SELECT unaccent('Crème Brûlée');     -- Creme Brulee

Use it inside a text search configuration (shown in Text Search Configurations and Dictionaries), or to normalize strings before trigram comparison. Note that unaccent() is not marked immutable, so using it in an index expression requires a wrapper function that is declared IMMUTABLE and calls the two-argument form with an explicit dictionary.

"Did you mean" suggestions. Full-text search does not correct spelling, but you can build a dictionary of the words that actually appear in your documents and look up the closest one with trigrams:

-- 1. Collect every word that appears in the searchable text, with document counts
CREATE TABLE search_words AS
SELECT word, ndoc
FROM ts_stat($$SELECT to_tsvector('simple', name || ' ' || coalesce(description, '')) FROM products$$);

CREATE INDEX ON search_words USING gin (word gin_trgm_ops);

-- 2. When a search returns nothing, suggest close words
SELECT word, round(similarity(word, 'baterry')::numeric, 3) AS sim
FROM search_words
WHERE word % 'baterry'
ORDER BY similarity(word, 'baterry') DESC, ndoc DESC
LIMIT 3;
-- battery | 0.455

The simple configuration is used so the word list contains real words, not stems, which makes good suggestions. Refresh the table periodically, for example with a scheduled job.

Choosing a technique

Expected mistake	Technique
Different forms of a word ("running", "ran")	Full-text search with stemming
Typos in any string, substring search	pg_trgm
Exact number of typos matters, small candidate set	levenshtein after a trigram filter
Names that sound alike	dmetaphone or daitch_mokotoff
Missing or extra accents	unaccent
Typo in a search box with zero results	"Did you mean" with ts_stat + pg_trgm

Combining techniques is common: a search can try full-text search first, fall back to trigram matching on names when there are no results, and offer a suggestion from the word list.

Key takeaways

• levenshtein counts edits precisely but cannot use indexes. Filter with an indexed trigram search first.
• Phonetic codes (soundex, metaphone, dmetaphone, daitch_mokotoff) match names by sound. Store the codes and compare with =.
• unaccent removes diacritics, usually as part of a custom text search configuration.
• Build "did you mean" suggestions from a ts_stat word list with a trigram index.

🧪 Practice

1. (Beginner) Compute levenshtein, similarity, and difference for five pairs of misspelled and correct names. Which measure best matches your intuition of "close"?
2. (Intermediate) Add a generated column with dmetaphone(name) to a table of 10,000 people (generate them from lists of first and last names), index it, and write a search that finds people by a name that sounds like the input.
3. (Interview) "Design a product search that handles stemming, typos, and partial words, and returns no-result suggestions." Hint: Layer full-text search, trigram indexes on names, and a word list, and decide in which order to try them and how to merge their rankings.

<a id="54-analytical-query-patterns"></a>

5.4 Analytical Query Patterns

This subchapter combines CTEs, window functions, and set-returning functions into reusable solutions for recurring reporting problems: pivoting, finding streaks and gaps, bucketing time, picking the top rows per group, sampling large tables, and expanding values into rows.

Pivoting and Crosstab Queries

SQL results are "long": one row per combination, such as (country, month, revenue). People read reports "wide": one row per country, one column per month. Turning distinct values of one column into separate columns is called pivoting, and the reverse is unpivoting. SQL has no PIVOT keyword in PostgreSQL, but there are three good ways to do it.

 long (what SQL produces)            wide (what people want to read)

 country | month   | revenue         country |   jan   |  feb  |   mar
 --------+---------+---------        --------+---------+-------+---------
 DE      | 2025-03 |   90.50   ──>   DE      |         |       |   90.50
 ES      | 2025-03 | 1358.80         ES      |         |       | 1358.80
 NG      | 2025-02 |   29.90         NG      |         | 29.90 |
 PT      | 2025-01 | 1324.00         PT      | 1324.00 | 93.00 |
 PT      | 2025-02 |   93.00

Method 1: conditional aggregation with FILTER. One aggregate per output column, each filtered to one value (4.4). This is standard SQL, needs no extension, and is the most common choice:

SELECT c.country,
       sum(oi.quantity * oi.unit_price) FILTER (WHERE o.placed_at >= '2025-01-01' AND o.placed_at < '2025-02-01') AS jan,
       sum(oi.quantity * oi.unit_price) FILTER (WHERE o.placed_at >= '2025-02-01' AND o.placed_at < '2025-03-01') AS feb,
       sum(oi.quantity * oi.unit_price) FILTER (WHERE o.placed_at >= '2025-03-01' AND o.placed_at < '2025-04-01') AS mar
FROM orders AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.country
ORDER BY c.country;

Method 2: the crosstab function. The tablefunc extension provides crosstab(), which takes a query returning (row name, category, value) and pivots it. Always use the two-argument form: the second query lists the categories, which fixes which value goes into which column:

CREATE EXTENSION tablefunc;

SELECT *
FROM crosstab(
    $$SELECT c.country, to_char(o.placed_at, 'YYYY-MM'), sum(oi.quantity * oi.unit_price)
      FROM orders o
      JOIN customers c    ON c.id = o.customer_id
      JOIN order_items oi ON oi.order_id = o.id
      WHERE o.status <> 'cancelled'
      GROUP BY 1, 2
      ORDER BY 1, 2$$,                                          -- must be ordered by row name
    $$VALUES ('2025-01'), ('2025-02'), ('2025-03')$$            -- the categories, in column order
) AS ct(country char(2), jan numeric, feb numeric, mar numeric);  -- you must declare the output columns

--  country |   jan   |  feb  |   mar
-- ---------+---------+-------+---------
--  DE      |         |       |   90.50
--  ES      |         |       | 1358.80
--  NG      |         | 29.90 |
--  PT      | 1324.00 | 93.00 |

The one-argument form crosstab(sql) fills columns left to right in the order values arrive, ignoring which category they belong to. With missing combinations, values silently land in the wrong column. In this data, it would put Germany's March revenue in the first column. Avoid it unless every row has every category.

Method 3: aggregate into JSON. When the set of columns is not known in advance, return one JSON object per row and let the application spread it into columns:

SELECT c.country, jsonb_object_agg(s.status, s.n) AS orders_by_status
FROM (SELECT customer_id, status, count(*) AS n FROM orders GROUP BY customer_id, status) AS s
JOIN customers AS c ON c.id = s.customer_id
GROUP BY c.country
ORDER BY c.country;
--  country |  orders_by_status
-- ---------+------------------
--  DE      | {"shipped": 1}
--  ES      | {"paid": 1}
--  FR      | {"cancelled": 1}
--  NG      | {"pending": 1}
--  PT      | {"shipped": 2}

Comparison

Method	Columns known in advance?	Extension	Notes
FILTER / CASE	Yes, written in the query	None	Portable, flexible (different aggregates per column)
crosstab(source, categories)	Yes, in the column definition list	tablefunc	Compact for many columns. Errors if the column list does not match
jsonb_object_agg	No	None	Dynamic. The application must interpret the JSON
psql \crosstabview	No	None	Display-only pivot in psql, handy for exploration

SQL requires the result columns to be known when the query is planned, so a truly dynamic pivot (one column per value that exists today) needs either JSON or a query generated by application code or a PL/pgSQL function (9.2).

Unpivoting. Turning columns back into rows uses a LATERAL join to a VALUES list (4.3):

SELECT p.country, u.month, u.revenue
FROM pivoted AS p                                             -- a table with columns country, jan, feb, mar
CROSS JOIN LATERAL (
    VALUES ('2025-01', p.jan), ('2025-02', p.feb), ('2025-03', p.mar)   -- one row per former column
) AS u(month, revenue)
WHERE u.revenue IS NOT NULL;

Key takeaways

• Pivoting turns category values into columns. Use agg(...) FILTER (WHERE ...) per column as the default technique.
• With crosstab, always use the two-argument form so values land in the correct columns.
• The output columns of a SQL query must be fixed at planning time. For dynamic columns, return jsonb or generate the SQL.
• Unpivot with CROSS JOIN LATERAL (VALUES ...).

🧪 Practice

1. (Beginner) Produce a table with one row per product category and one column per order status, containing the number of items sold.
2. (Intermediate) Write the same report with crosstab, with a categories query that reads the statuses from the data. Then add a new status value to an order and observe what happens.
3. (Interview) "A business user wants a report with one column per day of the current month. How would you build it?" Hint: Compare generating the SQL in application code, jsonb_object_agg, and doing the pivot in the reporting or spreadsheet tool, and consider how column names change every month.

Gaps and Islands Problems

Many questions about sequences are about runs: "what is each user's longest streak of consecutive login days?", "during which periods was the sensor offline?", "group clicks into sessions separated by 30 minutes of inactivity". These are called gaps and islands problems. An island is a maximal run of consecutive values; a gap is the missing range between two islands. They look hard in SQL because a row's group depends on its neighbors, but window functions make them routine.

The sample data. Daily logins per user:

CREATE TABLE logins (user_id int, day date, PRIMARY KEY (user_id, day));
INSERT INTO logins VALUES
  (1, '2025-03-01'), (1, '2025-03-02'), (1, '2025-03-03'),
  (1, '2025-03-05'), (1, '2025-03-06'), (1, '2025-03-09'),
  (2, '2025-03-02'), (2, '2025-03-03'), (2, '2025-03-04'), (2, '2025-03-05');

Islands: the row_number difference trick. Number each user's days in order. Within a run of consecutive days, the date and the row number both increase by 1 per row, so day - row_number is constant. When a day is skipped, the date jumps by more than the row number and the difference changes. That constant identifies the island:

SELECT user_id, day,
       row_number() OVER (PARTITION BY user_id ORDER BY day)              AS rn,
       day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
FROM logins
WHERE user_id = 1;

--  user_id |    day     | rn |    grp
-- ---------+------------+----+------------
--        1 | 2025-03-01 |  1 | 2025-02-28  ┐
--        1 | 2025-03-02 |  2 | 2025-02-28  ├ island 1
--        1 | 2025-03-03 |  3 | 2025-02-28  ┘
--        1 | 2025-03-05 |  4 | 2025-03-01  ┐ island 2
--        1 | 2025-03-06 |  5 | 2025-03-01  ┘
--        1 | 2025-03-09 |  6 | 2025-03-03    island 3

grp has no meaning of its own: it is just a label that is equal within an island and different between islands. Group by it:

WITH marked AS (
    SELECT user_id, day,
           day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
    FROM logins
)
SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
FROM marked
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

--  user_id | streak_start | streak_end | days
-- ---------+--------------+------------+------
--        1 | 2025-03-01   | 2025-03-03 |    3
--        1 | 2025-03-05   | 2025-03-06 |    2
--        1 | 2025-03-09   | 2025-03-09 |    1
--        2 | 2025-03-02   | 2025-03-05 |    4

The trick requires unique values per partition. If a user can log in twice on one day, deduplicate first (SELECT DISTINCT user_id, day) or use dense_rank() instead of row_number().

Gaps: compare with the next row. A gap exists wherever the next value is more than one step away:

SELECT user_id, day + 1 AS gap_start, next_day - 1 AS gap_end
FROM (
    SELECT user_id, day, lead(day) OVER (PARTITION BY user_id ORDER BY day) AS next_day
    FROM logins
) AS t
WHERE next_day - day > 1;

--  user_id | gap_start  |  gap_end
-- ---------+------------+------------
--        1 | 2025-03-04 | 2025-03-04
--        1 | 2025-03-07 | 2025-03-08

Islands defined by a threshold: sessionization. When "consecutive" means "within 30 minutes" rather than "the next integer", use the general method: flag each row that starts a new island, then turn the flags into island numbers with a running sum:

CREATE TABLE clicks (user_id int, at timestamptz);
INSERT INTO clicks VALUES
  (1, '2025-03-01 10:00'), (1, '2025-03-01 10:05'), (1, '2025-03-01 10:20'),
  (1, '2025-03-01 11:30'), (1, '2025-03-01 11:40'), (1, '2025-03-01 14:00');

WITH flagged AS (
    SELECT user_id, at,
           CASE WHEN at - lag(at) OVER (PARTITION BY user_id ORDER BY at) <= interval '30 minutes'
                THEN 0 ELSE 1                       -- 1 = this click starts a new session
           END AS new_session                       -- (the first click has lag NULL -> ELSE -> 1)
    FROM clicks
),
numbered AS (
    SELECT *, sum(new_session) OVER (PARTITION BY user_id ORDER BY at) AS session_id   -- running count of starts
    FROM flagged
)
SELECT user_id, session_id, min(at)::time AS started, max(at)::time AS ended, count(*) AS clicks
FROM numbered
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

--  user_id | session_id | started  |  ended   | clicks
-- ---------+------------+----------+----------+--------
--        1 |          1 | 10:00:00 | 10:20:00 |      3
--        1 |          2 | 11:30:00 | 11:40:00 |      2
--        1 |          3 | 14:00:00 | 14:00:00 |      1

 at:           10:00  10:05  10:20 | 11:30  11:40 | 14:00
 new_session:    1      0      0   |   1      0   |   1
 running sum:    1      1      1   |   2      2   |   3      <- session_id

This "flag the starts, then running-sum the flags" method solves almost every islands problem, including runs of equal status values (flag rows where status <> lag(status)).

Filling forward the last known value. A related problem: sensor readings with missing values should show the last known reading. Because lag() cannot skip NULLs, use a running count() of non-null values as a group label. count ignores NULLs, so it stays the same until the next real reading:

WITH readings (ts, temp) AS (VALUES (1, 20.5), (2, NULL), (3, NULL), (4, 21.0), (5, NULL))
SELECT ts, temp,
       first_value(temp) OVER (PARTITION BY grp ORDER BY ts) AS temp_filled
FROM (SELECT ts, temp, count(temp) OVER (ORDER BY ts) AS grp FROM readings) AS t
ORDER BY ts;
-- temp_filled: 20.5, 20.5, 20.5, 21.0, 21.0

Key takeaways

• Islands of consecutive values: value - row_number() is constant within an island. Group by it.
• Gaps: compare each row with lead() and keep the rows where the step is larger than one.
• Threshold-based islands (sessions, status runs): flag rows that start a new island, then number islands with a running sum() of the flags.
• Fill forward missing values with a running count(col) as a group label and first_value() within each group.

🧪 Practice

1. (Beginner) Return each user's longest login streak (one row per user) from logins.
2. (Intermediate) Given a table of (sensor_id, at, status) with statuses online and offline, return each period in which a sensor was offline, with its start, end, and duration.
3. (Interview) "Find all ranges of missing ids in a table whose id column should have no gaps." Hint: Use lead(id) and keep the rows where the next id is more than one higher. Then explain why identity columns often have gaps anyway (2.2).

Time Bucketing and Series Generation

Time-series reports group events into buckets (per hour, per day, per 15 minutes) and show one row per bucket. Two things make this harder than it looks: buckets with no events must still appear (as zero), and buckets need consistent boundaries and time zones. PostgreSQL provides the building blocks: date_trunc and date_bin to assign events to buckets, generate_series to produce the complete list of buckets, and width_bucket for numeric histograms. The individual functions were introduced in 3.2. This topic shows how to combine them into correct reports.

Assigning events to buckets

Function	Bucket width	Example
date_trunc('unit', ts)	Calendar units: minute, hour, day, week, month, quarter, year	date_trunc('month', placed_at)
date_trunc('unit', ts, 'zone')	Same, aligned to a named time zone (12+)	Days that start at midnight in Tokyo
date_bin(stride, ts, origin)	Any fixed interval (14+)	15-minute or 6-hour buckets

SELECT date_trunc('month', placed_at) AS month, count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

SELECT date_bin('15 minutes', at, timestamptz '2025-01-01') AS bucket, count(*) AS clicks
FROM clicks
GROUP BY 1
ORDER BY 1;
--          bucket         | clicks
-- ------------------------+--------
--  2025-03-01 10:00:00+00 |      2
--  2025-03-01 10:15:00+00 |      1
--  2025-03-01 11:30:00+00 |      2      <- 10:30 to 11:15 are missing: no clicks
--  2025-03-01 14:00:00+00 |      1

date_bin cannot use months or years, because their lengths vary. Use date_trunc for calendar units.

Filling empty buckets. GROUP BY can only produce buckets that contain at least one row. To show empty ones, generate the full list of buckets and LEFT JOIN the data to it:

SELECT gs.month::date AS month, count(o.id) AS orders           -- count(o.id), not count(*): empty months -> 0
FROM generate_series(timestamptz '2024-12-01',
                     timestamptz '2025-04-01',
                     interval '1 month') AS gs(month)           -- every bucket, data or not
LEFT JOIN orders AS o
       ON o.placed_at >= gs.month
      AND o.placed_at <  gs.month + interval '1 month'         -- half-open range: index-friendly, no overlap
GROUP BY gs.month
ORDER BY gs.month;

--    month    | orders
-- ------------+--------
--  2024-12-01 |      0
--  2025-01-01 |      1
--  2025-02-01 |      2
--  2025-03-01 |      3
--  2025-04-01 |      0

Two details matter here. count(*) would count the one all-NULL row that the outer join produces for an empty month, giving 1 instead of 0. And the join uses a half-open range on the raw column instead of date_trunc('month', o.placed_at) = gs.month, so an index on placed_at can be used (3.2).

An alternative shape: aggregate first, then fill. For large tables, aggregate the data into buckets first, then join the (small) result to the series. This avoids joining every event to the series:

WITH per_day AS (
    SELECT date_trunc('day', created_at) AS day, count(*) AS n
    FROM events
    WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01'
    GROUP BY 1
)
SELECT gs.day::date, coalesce(p.n, 0) AS events
FROM generate_series(timestamptz '2025-03-01', timestamptz '2025-03-31', interval '1 day') AS gs(day)
LEFT JOIN per_day AS p ON p.day = gs.day
ORDER BY gs.day;

Time zones. date_trunc('day', timestamptz) cuts days at midnight in the session's TimeZone setting. A report for a business in New York, run from a server session set to UTC, would place a 9 p.m. New York order in the next day. Make the time zone explicit, with the three-argument date_trunc(..., 'America/New_York') or generate_series in that zone, so the report does not depend on session settings.

Numeric histograms with width_bucket. width_bucket(value, low, high, count) returns which of count equal-width buckets between low and high a value falls into. Values below low get 0, values at or above high get count + 1:

SELECT width_bucket(price, 0, 100, 4) AS bucket,        -- 4 buckets of width 25 between 0 and 100
       count(*), min(price), max(price)
FROM products
GROUP BY 1
ORDER BY 1;

--  bucket | count |  min   |   max
-- --------+-------+--------+---------
--       1 |     1 |  12.50 |   12.50      [0, 25)
--       2 |     3 |  25.00 |   39.00      [25, 50)
--       3 |     1 |  54.00 |   54.00      [50, 75)
--       5 |     2 | 999.00 | 1299.00      >= 100 (overflow bucket)

-- Uneven boundaries: pass an array of lower bounds
SELECT width_bucket(price, ARRAY[0, 25, 50, 100, 1000]::numeric[]) AS bucket, count(*)
FROM products GROUP BY 1 ORDER BY 1;

To show empty histogram buckets, join to generate_series(0, count + 1), as with time buckets.

Key takeaways

• Assign events to buckets with date_trunc (calendar units) or date_bin (any fixed width).
• Generate the complete bucket list with generate_series and LEFT JOIN the data, counting a column from the joined table so empty buckets show 0.
• Join on half-open ranges of the raw timestamp, or aggregate first and join the small result to the series.
• Make time zones explicit in bucketing. Use width_bucket for numeric histograms.

🧪 Practice

1. (Beginner) Count orders per weekday (Monday to Sunday) and make sure all seven weekdays appear, even those without orders.
2. (Intermediate) For the events table, produce hourly event counts for one day, as one row per hour (24 rows), with an extra column for the share of that hour in the daily total.
3. (Interview) "A daily revenue report for a company in Sydney shows different numbers depending on which server runs it. What is wrong?" Hint: Look at session TimeZone, date_trunc on timestamptz, and how to make the bucket boundaries explicit.

Top-N Per Group

"The 3 best-selling products in each category", "each customer's latest order", "the highest-paid employee per department": returning the top N rows within each group is one of the most common analytical questions. Plain ORDER BY ... LIMIT only limits the whole result, and GROUP BY collapses rows so the other columns of the winning row are lost. PostgreSQL offers three solutions, with different strengths.

Solution 1: a ranking window function. Number the rows within each group, then keep the first N. This is the most flexible and portable solution:

SELECT category_id, name, price
FROM (
    SELECT category_id, name, price,
           row_number() OVER (PARTITION BY category_id ORDER BY price DESC, id) AS rn
    FROM products
) AS ranked
WHERE rn <= 2                                   -- top 2 per category
ORDER BY category_id, rn;

--  category_id |        name        |  price
-- -------------+--------------------+---------
--            2 | Postgres Internals |   54.00
--            2 | SQL in Practice    |   39.00
--            3 | UltraBook 14       | 1299.00
--            3 | WorkBook 15        |  999.00
--            4 | Wireless Mouse     |   29.90
--            4 | USB-C Cable        |   12.50
--              | Gift Card          |   25.00

The choice of ranking function decides how ties at the cut-off are handled (Ranking Functions):

Function	rn <= 2 returns
row_number()	Exactly 2 rows per group, ties broken by the extra sort key
rank()	At least 2 rows: all rows tied with the 2nd place are included
dense_rank()	All rows with the 2 highest distinct values

PostgreSQL 15+ recognizes the outer rn <= N filter. The plan shows a Run Condition on the WindowAgg node, which means PostgreSQL stops computing row numbers for a partition once the limit is passed. It still has to read and sort all input rows, though.

Solution 2: DISTINCT ON, for N = 1. The idiomatic PostgreSQL shortcut for "one row per group" (4.1):

SELECT DISTINCT ON (category_id) category_id, name, price
FROM products
ORDER BY category_id, price DESC, id;           -- first row per category_id wins

It is concise and usually fast, but it only handles N = 1 and is not standard SQL.

Solution 3: LATERAL with LIMIT. For each group, run a small query that fetches its top N (4.3):

SELECT cat.id, cat.name AS category, top.name, top.price
FROM categories AS cat
CROSS JOIN LATERAL (
    SELECT p.name, p.price
    FROM products AS p
    WHERE p.category_id = cat.id
    ORDER BY p.price DESC
    LIMIT 2
) AS top;

With an index on products (category_id, price DESC), each lateral subquery reads only N index entries. When there are few groups and many rows per group, this is dramatically faster than solutions 1 and 2, which must process every row.

Choosing a solution

Situation	Best choice
One row per group, simple query	DISTINCT ON
N rows per group, ties matter, or portability needed	row_number() / rank() in a subquery
Few groups, many rows per group, matching index exists	LATERAL ... ORDER BY ... LIMIT n
Groups come from a separate table (categories with no products must appear)	LEFT JOIN LATERAL ... ON true
Many groups with few rows each, no useful index	Window function (one sort of the whole table)

 Rows read for "top 2 per category", 10 categories x 1,000,000 products each:

 window function / DISTINCT ON:  read and sort ~10,000,000 rows, keep 20
 LATERAL + index:                10 index descents x 2 entries = ~20 rows read

Key takeaways

• Top-N per group: rank in a subquery or CTE with PARTITION BY, then filter rn <= N outside.
• Choose row_number(), rank(), or dense_rank() according to how ties at the cut-off should be treated.
• DISTINCT ON is the shortest form for N = 1.
• LATERAL with LIMIT and a matching index reads only the needed rows, which wins when groups are large.

🧪 Practice

1. (Beginner) Return each customer's most recent order using all three solutions, and confirm they return the same rows.
2. (Intermediate) For each category, return the 2 best-selling products by quantity sold, including ties at second place. Categories without sales must not appear.
3. (Interview) "Return the 5 most recent messages in each of a user's 200 conversations, from a table of 2 billion messages." Hint: Compare the plans of a row_number() query and a LATERAL query, and design the index that makes the lateral version read about 1,000 rows.

Sampling with TABLESAMPLE

On a table with a billion rows, exploratory questions ("roughly what share of events are purchases?", "what do typical rows look like?") do not need an exact answer, but an exact count(*) or GROUP BY must read everything. TABLESAMPLE reads only a random fraction of the table, giving an approximate answer in a fraction of the time.

Syntax. TABLESAMPLE method (argument) [REPEATABLE (seed)] follows a table name in FROM:

SELECT kind, count(*) * 100 AS estimated            -- scale up: a 1% sample
FROM events TABLESAMPLE SYSTEM (1)                   -- read about 1% of the table's pages
GROUP BY kind;

--  kind  | estimated              exact counts (full scan, much slower):
-- -------+-----------             buy    250,223
--  buy   |    240700              click  499,544
--  click |    477900              view   250,233
--  view  |    236200

The two built-in methods

Method	Samples	Speed	Randomness
SYSTEM (p)	Whole pages (blocks), each with probability p%	Very fast: unselected pages are never read	Clustered: rows stored together are sampled together
BERNOULLI (p)	Individual rows, each with probability p%	Slower: scans every page and picks rows	Statistically better, independent rows

 Table pages:  [r r r r] [r r r r] [r r r r] [r r r r] [r r r r]

 SYSTEM (40):  [R R R R]  ........  [R R R R]  ........  ........    whole pages, others skipped
 BERNOULLI(40):[R . r R] [. R . .] [R . . R] [. . R .] [R R . .]    individual rows, every page read

Because SYSTEM picks whole pages, it is biased when the physical row order correlates with the values you measure. For example, on an append-only events table, rows on the same page were inserted at about the same time. Use BERNOULLI when that matters and SYSTEM when speed matters more.

Sample size is approximate. Both methods select each page or row independently, so SYSTEM (1) on a million-row table returned 8,843 rows in one run and 11,081 in the next. To get the same sample in every run (for reproducible analyses or tests), add REPEATABLE (seed):

SELECT count(*) FROM events TABLESAMPLE BERNOULLI (1) REPEATABLE (42);   -- same rows every time,
                                                                         -- as long as the table is unchanged

Fixed-size and time-limited samples. Two contrib extensions add methods:

CREATE EXTENSION tsm_system_rows;
SELECT * FROM events TABLESAMPLE SYSTEM_ROWS (500);        -- exactly 500 rows (block-level, like SYSTEM)

CREATE EXTENSION tsm_system_time;
SELECT * FROM events TABLESAMPLE SYSTEM_TIME (100);        -- as many rows as can be read in 100 ms

Sampling happens before WHERE. The sample is drawn from the table first, then WHERE filters it. A 1% sample filtered to a rare condition may contain very few rows:

SELECT count(*) FROM events TABLESAMPLE SYSTEM (1) WHERE kind = 'buy';   -- ~1% of the buy rows, not 1% of the table

TABLESAMPLE versus ORDER BY random()

	TABLESAMPLE	ORDER BY random() LIMIT n
Rows read	A fraction of the table (SYSTEM)	All rows, plus a sort
Exact sample size	No (except SYSTEM_ROWS)	Yes
Works on views and subqueries	No, only tables and materialized views	Yes
Good for	Large tables, approximate analytics	Small tables or exact-size random picks

For a fast count estimate of a whole table, you may not need sampling at all: reltuples in pg_class holds the planner's estimate, maintained by VACUUM and ANALYZE (7.2).

Key takeaways

• TABLESAMPLE reads a random fraction of a table for approximate answers. Scale aggregates by the sampling rate.
• SYSTEM samples pages (fast, clustered), BERNOULLI samples rows (slower, more random).
• Sample sizes vary between runs. REPEATABLE (seed) makes a sample reproducible. tsm_system_rows gives a fixed row count.
• Sampling is applied before WHERE, and only to tables and materialized views.

🧪 Practice

1. (Beginner) Run SELECT count(*) FROM events TABLESAMPLE SYSTEM (5) five times and compute how much the result varies. Repeat with BERNOULLI and with REPEATABLE.
2. (Intermediate) Estimate the average number of events per user from a 1% BERNOULLI sample and compare it with the exact value. Explain why the sample's count(DISTINCT user_id) cannot be scaled up by 100.
3. (Interview) "How would you show a random sample of 20 products on a home page, from a catalog of 50 million, on every page load?" Hint: Compare ORDER BY random(), TABLESAMPLE SYSTEM_ROWS, and picking random ids within the id range, and think about gaps in the id sequence and caching.

Set-Returning Functions and unnest

Most functions return one value. A set-returning function (SRF) returns a whole set of rows, so it can be used in FROM like a table. SRFs are the bridge between compact values and relational rows: generate_series produces rows from nothing, unnest turns an array into rows, jsonb_array_elements turns a JSON array into rows. Many of the patterns in this chapter depend on them.

Common set-returning functions

Function	Produces	Example
generate_series(start, stop [, step])	Numbers, dates, or timestamps	generate_series(1, 10, 3) -> 1, 4, 7, 10
unnest(array [, array ...])	One row per element	unnest(ARRAY['a','b'])
generate_subscripts(array, dim)	The valid indexes of an array	1, 2, 3 for a 3-element array
string_to_table(text, delimiter) (14+)	One row per piece of a string	string_to_table('a,b', ',')
regexp_matches(text, pattern, 'g')	One row per regex match	Hashtags in a text
jsonb_array_elements(jsonb)	One row per JSON array element	Items in an order document
jsonb_each(jsonb)	One row per key/value pair	Attributes of a JSON object
json_table(...) (17+)	Rows and columns from JSON	Full JSON-to-table mapping (3.4)

Expanding a column per row. In FROM, an SRF can use columns of the tables to its left, because function calls in FROM are implicitly LATERAL:

-- Order lines stored as a JSON array, expanded into rows
SELECT o.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM (VALUES (1, '[{"sku": "BK-001", "qty": 2}, {"sku": "ACC-001", "qty": 1}]'::jsonb)) AS o(id, items)
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item;      -- LATERAL keyword optional for functions

--  id |   sku   | qty
-- ----+---------+-----
--   1 | BK-001  |   2
--   1 | ACC-001 |   1

-- Hashtags extracted from text: each match is a text[] of capture groups
SELECT m[1] AS tag
FROM regexp_matches('Great #postgres tips on #sql and #indexing', '#(\w+)', 'g') AS m;
-- postgres, sql, indexing

WITH ORDINALITY: keeping element positions. Appending WITH ORDINALITY to any SRF in FROM adds a bigint column numbering the rows 1, 2, 3, ... in the order the function produced them:

SELECT p.sku, part, n
FROM products AS p,
     unnest(string_to_array(p.sku, '-')) WITH ORDINALITY AS s(part, n)
WHERE p.sku LIKE 'BK%';

--   sku   | part | n
-- --------+------+---
--  BK-001 | BK   | 1
--  BK-001 | 001  | 2
--  BK-002 | BK   | 1
--  BK-002 | 002  | 2

This is the reliable way to keep array order, for example to re-aggregate with array_agg(x ORDER BY n) after processing elements.

Zipping several arrays. unnest with several arguments, or ROWS FROM with several functions, walks the inputs side by side. Shorter inputs are padded with NULLs:

SELECT * FROM unnest(ARRAY['a', 'b', 'c'], ARRAY[1, 2]) AS t(letter, num);
-- (a,1), (b,2), (c,NULL)

SELECT * FROM ROWS FROM (generate_series(1, 3), unnest(ARRAY['a', 'b'])) AS t(n, letter);
-- (1,a), (2,b), (3,NULL)

A common use is bulk operations from application arrays: pass one array per column as parameters and INSERT ... SELECT * FROM unnest($1::int[], $2::text[]), which inserts many rows with a single statement and a fixed number of parameters.

SRFs in the SELECT list. PostgreSQL also allows SRFs in the SELECT list, where they multiply the row:

SELECT generate_series(1, 3) AS a, generate_series(1, 2) AS b;
-- (1,1), (2,2), (3,NULL)     since PostgreSQL 10: run side by side, like ROWS FROM

SELECT id, generate_series(1, 0) FROM products;
-- 0 rows: an SRF that returns nothing removes the row entirely

The second example is a trap: a row disappears when its SRF returns no rows, for example unnest of an empty array or NULL. In FROM, you can control this with LEFT JOIN LATERAL ... ON true. Prefer SRFs in FROM for clarity and control, and avoid them in the SELECT list except for quick, simple queries.

Writing your own SRFs. Functions declared RETURNS SETOF type or RETURNS TABLE (...) are set-returning and can be used the same way (9.1).

Key takeaways

• Set-returning functions produce rows: generate_series from ranges, unnest from arrays, jsonb_array_elements and regexp_matches from documents and text.
• In FROM, they are implicitly lateral, so they can expand a column of each row.
• WITH ORDINALITY adds a row number that preserves element order. unnest(a, b) and ROWS FROM zip several inputs.
• In the SELECT list, an SRF that returns no rows deletes the whole row. Prefer FROM, with LEFT JOIN LATERAL when empty inputs must keep the row.

🧪 Practice

1. (Beginner) Use generate_series to produce a multiplication table for 1 to 9 as 81 rows (a, b, a * b).
2. (Intermediate) Given a table of blog posts with a tags text[] column, return each post with its tags sorted alphabetically and deduplicated, using unnest, DISTINCT, and array_agg. Posts with no tags must still appear with an empty array.
3. (Interview) "An application inserts 10,000 rows by sending 10,000 single-row INSERT statements. How would you speed it up without changing the database schema?" Hint: Compare multi-row VALUES, INSERT ... SELECT FROM unnest($1::type[], ...) with array parameters, and COPY, considering parameter limits and round trips.

<a id="6-storage-internals-and-indexing"></a>