Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 90 ยท Querying with PostgreSQL Features

generate_series and Set-Returning Functions

Learn PostgreSQL set-returning functions: generate_series for numbers and dates, unnest, WITH ORDINALITY, regexp_split_to_table, and calendar tables with gap filling.

Most functions take values in and give one value back. A set-returning function (SRF) gives back a whole table: zero, one or many rows. PostgreSQL ships with a rich collection of them, and you use them in the FROM clause as if they were tables. generate_series() produces a run of numbers or dates, unnest() turns an array into rows, and regexp_split_to_table() cuts a string into pieces, one per row.

They solve problems that are awkward in plain SQL: "I need a row for every day in March, including days with no sales", "I need a million rows of test data", "I have a comma-separated string and want to query the pieces". Because they are implicitly lateral, they can also read columns from tables listed before them in FROM.

Our in-browser engine is SQLite, which has no generate_series and no arrays. Every PostgreSQL function in this lesson is shown in read-only blocks. Each idea also comes with a runnable SQLite version built from a recursive CTE or json_each(), which produces the same rows.

Functions that return tables

generate_series A run of numbers, dates or timestamps.
unnest One row per array element.
Splitting text regexp_split_to_table and string_to_table.
Regex matches regexp_matches returns every match.
1 / 2

generate_series for numbers

generate_series(start, stop) returns every integer from start to stop, both inclusive. A third argument sets the step:

SQL
SELECT * FROM generate_series(1, 5);
--  generate_series
-- -----------------
--                1
--                2
--                3
--                4
--                5

SELECT * FROM generate_series(1, 20, 3);   -- 1, 4, 7, 10, 13, 16, 19
SELECT * FROM generate_series(5, 1, -1);   -- 5, 4, 3, 2, 1  (negative step counts down)
SELECT * FROM generate_series(1, 0);       -- no rows at all
SELECT * FROM generate_series(0, 1, 0.25); -- numeric steps work too: 0.00, 0.25, 0.50, 0.75, 1.00

A step of 0 is an error, and if the step points away from the stop value you simply get no rows. Give the column a friendlier name with an alias: FROM generate_series(1, 5) AS n makes the column and the table both called n, and AS g(n) names the column n separately from the table g.

SQLite can do the same with a recursive CTE. It starts with the first value and adds the step until the stop value is passed:

SQL A number series with a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

The seven rows are 1, 4, 7, 10, 13, 16, 19. The WHERE in the recursive part plays the role of the stop argument. In PostgreSQL you would never write this by hand, because generate_series is shorter and faster.

generate_series for dates and timestamps

For dates, PostgreSQL needs an interval step. The result is a timestamp type (with date arguments PostgreSQL picks timestamptz), so cast it back to date when you only want days:

SQL
SELECT d::date AS day
FROM generate_series('2024-02-26'::date, '2024-03-03'::date, interval '1 day') AS g(d);
-- 2024-02-26, 2024-02-27, 2024-02-28, 2024-02-29, 2024-03-01, 2024-03-02, 2024-03-03

SELECT * FROM generate_series(
  '2024-01-01 09:00'::timestamp,
  '2024-01-01 12:00'::timestamp,
  interval '90 minutes');
-- 09:00, 10:30, 12:00

Notice that 2024 is a leap year, so the series includes February 29. Month steps (interval '1 month') work too, and with timestamptz the step follows the session's time zone, which matters around daylight-saving changes. You will meet time zones properly in Dates, Times and Time Zones.

The SQLite version steps with date(d, '+1 day'):

SQL A date series with weekdays

Press Run (or Ctrl + Enter) to see the result here.

You get seven rows from Monday 2024-02-26 (weekday 1) to Sunday 2024-03-03 (weekday 0), with February 29 in the middle.

Calendar tables and filling gaps

A report that groups orders by month silently skips months without orders. The fix is to start from a complete series and LEFT JOIN the data onto it. In PostgreSQL:

SQL
SELECT m::date AS month,
       count(o.id) AS orders,
       coalesce(sum(o.quantity), 0) AS copies
FROM generate_series('2024-02-01'::timestamp, '2024-08-01'::timestamp, interval '1 month') AS m
LEFT JOIN orders AS o
       ON date_trunc('month', o.ordered_on::timestamp) = m
GROUP BY m
ORDER BY m;

The same shape in SQLite, using the bookstore orders (there are none in August 2024):

SQL Orders per month, including empty months ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The output has seven rows: February 2, March 2, April 2, May 1, June 2, July 1 and August 0. Without the generated series, August would not appear at all. Two details matter here. Count a column of the right-hand table (o.id), not *, because COUNT(*) would count the empty month's single null-extended row as 1. And wrap SUM in COALESCE, because a sum over no rows is NULL, not 0. These are the same left-join rules you know from LEFT JOIN.

For a calendar you reuse often, store the series in a real table (one row per day, with columns for weekday, week number, holiday flag) and join to it. It is faster and lets you add business rules.

unnest: arrays into rows

unnest(array) returns one row per element. As a function in FROM, it can read an array column from a table before it:

SQL
SELECT unnest(ARRAY['red', 'green', 'blue']) AS colour;   -- 3 rows

SELECT p.title, t.tag
FROM posts AS p, unnest(p.tags) AS t(tag);               -- one row per post and tag

Giving unnest several arrays zips them together, padding the shorter ones with NULL: unnest(ARRAY[1,2,3], ARRAY['a','b']) yields (1,a), (2,b), (3,NULL). You will see arrays in depth in Arrays.

SQLite has no arrays, but a JSON array and json_each() give you the same rows. The WITH ORDINALITY stand-in below uses its key column.

WITH ORDINALITY

Adding WITH ORDINALITY to any set-returning function in FROM appends a bigint column that numbers the rows from 1:

SQL
SELECT t.word, t.n
FROM unnest(ARRAY['red', 'green', 'blue', 'teal']) WITH ORDINALITY AS t(word, n);
--  word  | n
-- -------+---
--  red   | 1
--  green | 2
--  blue  | 3
--  teal  | 4

That number is the element's position in the array, which is useful whenever order carries meaning: ranking list items, pairing two arrays, finding the third element. Note that you must alias with both column names, t(word, n), to name the extra column.

Here are two SQLite ways to get the same four rows. One walks the string with a recursive CTE, the other uses json_each and adds 1 to its zero-based key:

SQL Splitting a list and numbering the pieces

Press Run (or Ctrl + Enter) to see the result here.

Both queries return 1 red, 2 green, 3 blue, 4 teal. The recursive version peels off the text before the next comma on each step, which also shows what happens inside string_to_table.

Splitting and matching text

PostgreSQL has built-in text splitters:

SQL
SELECT * FROM string_to_table('a,b,c', ',');            -- PostgreSQL 14+: a, b, c
SELECT * FROM regexp_split_to_table('one  two   three', '\s+');  -- one, two, three

SELECT m[1] AS year, m[2] AS month
FROM regexp_matches('released 2023-11, patched 2024-02', '(\d{4})-(\d{2})', 'g') AS m;
-- 2023 | 11
-- 2024 | 02

regexp_matches returns one row per match, and each row is an array holding the capture groups, so m[1] is the first group. Without the 'g' flag it returns only the first match. If you only need a count or a yes/no, PostgreSQL 15 added regexp_count() and regexp_like(), which are simpler. More on patterns in Pattern Matching and Regular Expressions.

Set-returning functions can also appear in the SELECT list, for example SELECT id, unnest(tags) FROM posts. It works, but the rules for several of them side by side are subtle. Put them in FROM instead, where behaviour is clear and you can add WITH ORDINALITY or a LEFT JOIN.

Generating data

Because generate_series is a row factory, it is the quickest way to create test data or a lookup grid:

SQL
-- 100,000 fake users with random scores
INSERT INTO users (name, score)
SELECT 'user_' || g, round(random() * 100)
FROM generate_series(1, 100000) AS g;

-- every combination of 3 sizes and 4 colours
SELECT s, c
FROM generate_series(1, 3) AS s
CROSS JOIN generate_series(1, 4) AS c;      -- 12 rows

With SELECT ... FROM generate_series, the whole insert runs as one statement, which beats a loop of single inserts by a wide margin. Random values change each run, so tests that need repeatable data should use deterministic expressions (g % 7, g * 3) or seed the generator with setseed().

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How many rows does generate_series(1, 10, 3) return in PostgreSQL?

    It returns 1, 4, 7 and 10. The stop value is inclusive and 10 is reached exactly.
  2. 2. Why do monthly reports usually start from generate_series and use a LEFT JOIN?

    Grouping the data alone only produces groups that exist. A complete series on the left side keeps the empty periods.
  3. 3. What does WITH ORDINALITY add to a set-returning function in FROM?

    The extra column starts at 1 and you name it in the alias list, for example AS t(word, n).
  4. 4. What is the SQLite counterpart used in this lesson for turning a JSON array into rows?

    json_each() returns one row per array element with key and value columns, like unnest with ordinality when you use key + 1.
  5. 5. In the empty-month report, why is COUNT(o.id) used instead of COUNT(*)?

    For a month with no orders the left join still produces one row, filled with NULLs. COUNT(o.id) skips the NULL, so the result is 0.

Summary

  • A set-returning function returns a table and is used in FROM; it can read columns from earlier FROM items.
  • generate_series(start, stop[, step]) is inclusive, works for integers, numerics, dates and timestamps (dates and timestamps need an interval step), and can count down.
  • Left join data onto a generated series to fill gaps, count a right-side column, and wrap sums in COALESCE.
  • unnest turns arrays into rows; several arrays are zipped together.
  • WITH ORDINALITY appends a 1-based position column.
  • string_to_table, regexp_split_to_table and regexp_matches split and search text row by row.
  • In SQLite, recursive CTEs and json_each() give the same rows, which is how this lesson runs live.

Next: INSERT ON CONFLICT and RETURNING, writing data without race conditions and getting rows back from the same statement.