SQLite Forum

no such table: generate_series
Login

no such table: generate_series

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

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 [link] [source]

Load extension series. You should compile it and then load.

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

Although documented to be compiled into the shell (2016-09-14), it was not actually included until recently (2020-08-28).

I imagine this will just remain a documentation bug until the scheduled 3.34.0 release (2020-12-01).

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

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] [source]

You should probably use recursive common table expressions 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;

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

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. 😉