Whenever we use DuckDB’s generate_series() function, the results are returned in a list. But sometimes we might want the results to be returned in a table instead of a list, so that each value in the series is presented as a separate row in a table (as opposed to being an item in a list). In such cases we would need to “unnest” the results.

The Raw Results

First, here’s an example of what the raw results look like when we use generate_series():

SELECT generate_series(1, 5);

Result:

+-----------------------+
| generate_series(1, 5) |
+-----------------------+
| [1, 2, 3, 4, 5]       |
+-----------------------+

So the function returns the series as a list.

We have multiple options when it comes to “unnesting” this list. Below are two options we can use to unnest the result of the generate_series() function in DuckDB.

1. Using unnest()

DuckDB has an unnest() function, so this is probably one of the more obvious options:

SELECT * FROM unnest(generate_series(1, 5));

Result:

+--------+
| unnest |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
| 5      |
+--------+

2. Using the Table Function Syntax

Another way to do it is with the SELECT * FROM syntax:

SELECT * FROM generate_series(1, 5);

Result:

+-----------------+
| generate_series |
+-----------------+
| 1               |
| 2               |
| 3               |
| 4               |
| 5               |
+-----------------+

Example of Usage In a CTE (Common Table Expression)

A common table expression (CTE) is a temporary result set defined within a SQL query that can be referenced by the main query or even recursively within itself. Here’s an example of using generate_series() in a CTE:

WITH series AS (
  SELECT * FROM generate_series(1, 5)
)
SELECT * FROM series;

Result:

+-----------------+
| generate_series |
+-----------------+
| 1               |
| 2               |
| 3               |
| 4               |
| 5               |
+-----------------+

In this case we called generate_series() using the table function syntax, but we could have just as easily used the unnest() function:

WITH series AS (
  SELECT unnest(generate_series(1, 5))
)
SELECT * FROM series;

Result:

+-------------------------------+
| unnest(generate_series(1, 5)) |
+-------------------------------+
| 1                             |
| 2                             |
| 3                             |
| 4                             |
| 5                             |
+-------------------------------+