Create a table from the results of a recursive query (with statement)

Question

I want to make a new (persistent) table from the results of a recursive query in SQLite. For instance, let's make a table with columns (n, fct), where n denotes a natural number and fct its factorial:

-- factorial of n = 0, 1, ..., 5
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i;

which results in

0|1
1|1
2|2
3|6
4|24
5|120

However, I'd like to store the results in a persistent table, say factorials.

I tried

CREATE TABLE factorials (n, fct) AS
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i;

or

WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
CREATE TABLE factorials (n, fct) AS
SELECT * FROM fact_i;

Yet both result in a syntax error. Is there a way to create such a table in SQLite?

Answer 1

Yes, it is possible(wrapping with additional SELECT * FROM ()):

CREATE TABLE factorials AS
SELECT *
FROM (
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i) s;

db<>fiddle demo

EDIT:

Actually, all you need to do is removing column list from CREATE TABLE:

CREATE TABLE factorials AS
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i;

db<>fiddle demo2