The Difference Between RANGE() and GENERATE_SERIES() in DuckDB

by

DuckDB offers two handy functions for generating sequences of numbers: range() and generate_series(). While they both do the same thing, and share basically the same syntax, there is one important difference between them.

The primary difference between them is in their inclusivity behavior with regard to the stop value.

Key Difference: Inclusivity vs. Exclusivity

Here’s how each function works:

  • range(start, stop, step): Generates values from start up to but excluding stop
  • generate_series(start, stop, step): Generates values from start up to and including stop

The exclusivity of range() makes it behave more like Python’s range() function, while the inclusivity of generate_series() follows PostgreSQL’s generate_series() convention.

Both functions can also accept fewer arguments in some cases (we can omit start and step when working with numbers), but we must always specify the stop value. Either way, the difference between these functions is in how they treat the stop value.

Example of the Difference

Here’s an example that demonstrates the difference between the two functions:

SELECT 
    range(5),
    generate_series(5);

Result:

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

We can see that range(5) went from zero to four, whereas generate_series(5) went from zero to five.

So given the same stop value, they returned different results.

Both Functions Treat start the Same

There’s no difference in how the functions treat the start argument:

SELECT 
    range(1,5),
    generate_series(1,5);

Result:

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

In this case the start value is 1 for both functions, and both sequences start with that value.

Both Functions Treat step the Same

There’s no difference in how the functions treat the step argument either:

SELECT 
    range(1,5,2),
    generate_series(1,5,2);

Result:

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

However, there are scenarios where both functions could return the same result when using the same arguments. It all depends on the values being used. For example:

SELECT 
    range(1,6,2),
    generate_series(1,6,2);

Result:

+------------------+--------------------------+
| "range"(1, 6, 2) | generate_series(1, 6, 2) |
+------------------+--------------------------+
| [1, 3, 5]        | [1, 3, 5]                |
+------------------+--------------------------+

Date/Time Values

Both functions can be used with date/time values:

SELECT 
    range(
        DATE '2031-10-01',
        DATE '2031-10-03',
        INTERVAL '1 day'
    ) AS range,
    generate_series(
        DATE '2031-10-01',
        DATE '2031-10-03',
        INTERVAL '1 day'
    ) AS generate_series;

Output:

+--------------------------------------------+-----------------------------------------------------------------+
|                   range                    |                         generate_series                         |
+--------------------------------------------+-----------------------------------------------------------------+
| [2031-10-01 00:00:00, 2031-10-02 00:00:00] | [2031-10-01 00:00:00, 2031-10-02 00:00:00, 2031-10-03 00:00:00] |
+--------------------------------------------+-----------------------------------------------------------------+

When we use date/time values, we must provide a start, stop, and step/interval value. This is the case with both functions, so no difference in that regard.

Also, they both accept TIMESTAMP and TIMESTAMPTZ values. As seen with this example, if we pass a DATE, then the result is a TIMESTAMP.

Both Functions Can Be Unnested

Both functions return their results as an array, but they can also be unnested.

Example:

SELECT 
    unnest(range(
        DATE '2031-10-01',
        DATE '2031-10-03',
        INTERVAL '1 day'
    )) AS range,
    unnest(generate_series(
        DATE '2031-10-01',
        DATE '2031-10-03',
        INTERVAL '1 day'
    )) AS generate_series;

Output:

+---------------------+---------------------+
|        range        |   generate_series   |
+---------------------+---------------------+
| 2031-10-01 00:00:00 | 2031-10-01 00:00:00 |
| 2031-10-02 00:00:00 | 2031-10-02 00:00:00 |
|                     | 2031-10-03 00:00:00 |
+---------------------+---------------------+