DuckDB/SQL: Create a list of numbers
While in DuckDB land, I wanted to create a list of numbers, just like you can with Cypher’s range function.
After a bit of searching that resulted in very complex solutions, I came across the Postgres generate_series function, which does the trick.
We can use it in place of a table, like this:
SELECT *
FROM generate_series(1, 10);| generate_series |
|---|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
As you can see from the results, this returns 10 rows with one number per row.
Alternatively, we can use it inside a SELECT clause, like this:
SELECT generate_series(1, 10);| generate_series(1, 10) |
|---|
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] |
This time it returns one row with an array containing 10 values.
I later learnt that there is also a range function, exactly like the Cypher function that I mentioned at the beginning of the post.
The difference between range and generate_series is that for range`, the stop parameter is exclusive, whereas for generate_series it is inclusive.
So if we asked range to generate numbers between 1 and 11 with a step of 2, it would result in the following output, which excludes 11:
SELECT range(1, 11, 2);| range(1, 11, 2) |
|---|
[1, 3, 5, 7, 9] |
About the author
I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.