SQLite Forum

no such table: generate_series
Login

no such table: generate_series

(1) By luuk on 2020-11-06 07:25:45 [link]

```
D:\TEMP>sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from generate_series(1,1,10);
Error: no such table: generate_series
sqlite>
```

"https://sqlite.org/series.html":
The generate_series(START,END,STEP) table-valued function is a loadable extension included in the SQLite source tree, and compiled into the command-line shell.

I have (when looking at the download page) the current version 3.33 


What am i missing?

(2) By little-brother on 2020-11-06 08:01:56 in reply to 1

Load extension [`series`](https://www.sqlite.org/src/file?name=ext/misc/series.c&ci=tip). You should compile it and then load.

(3) By jake on 2020-11-06 08:15:04 in reply to 1 [link]

Although documented to be compiled into the shell ([2016-09-14](https://sqlite.org/docsrc/info/bdbea7dc63fcdc21)), it was not actually included until recently ([2020-08-28](https://www.sqlite.org/src/info/fc0856dccfab273d)).

I imagine this will just remain a documentation bug until the scheduled 3.34.0 release ([2020-12-01](https://www.sqlite.org/draft/releaselog/3_34_0.html)).

(4) By John Dennis (jdennis) on 2020-11-06 10:16:36 in reply to 1 [link]

Note that your function generate_series(1,1,10) will probably not do what you want.  The three parameters are START, END, and STEP - so your function call says "start at 1, end at 1".  I suspect you mean generate_series(1,10,1)

(5) By Richard Hipp (drh) on 2020-11-06 12:08:02 in reply to 1 [link]

You should probably use [recursive common table expressions][1] for this,
since it is an SQL standard and available on most SQL database engines,
whereas the generate_series() table-valued function is an PostgreSQL-ism.

I suppose you are looking for all integers between 1 and 10.  (That is
not what your generated_series() call does, so I'm having to guess.)
The correct code for this is:

~~~
   WITH RECURSIVE c(x) AS (
     VALUES(1)
     UNION ALL
     SELECT x+1 FROM c WHERE x<10
   )
   SELECT x FROM c;
~~~

[1]: https://www.sqlite.org/lang_with.html#recursivecte

(6) By luuk on 2020-11-06 18:19:22 in reply to 1 [link]

Thanks, for the info.

I will wait for the update to 3.34, and until then do a recursive with.

and, yes, it was before coffee this morning, so i did have the parameters in the wrong order. 😉