Context: I have an existing API that uses PostgreSQL as a backend, and I need to create a fork that will use SQLite.

Versions:

• OS: Ubuntu 20.04.3
• SQLite: 3.37.2

To simplify my use case, the following query works in PostgreSQL,

WITH cte AS (INSERT INTO test (s) VALUES ('test') RETURNING test.id, test.s) SELECT cte.id, cte.s FROM cte;

But it does not work in SQLite.

sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY, s TEXT);
sqlite> WITH cte AS (INSERT INTO test (s) VALUES ('test') RETURNING test.id, test.s) SELECT cte.id, cte.s FROM cte;
Error: in prepare, near "INSERT": syntax error (1)

INSERT...RETURNING by itself works correctly.

sqlite> INSERT INTO test (s) VALUES ('test') RETURNING test.id, test.s;
1|test

The CTE might seem unnecessary (and indeed, it's not used for any fancy transformations or hierarchical traversals), but the SQL is generated by the API's code and I would like to avoid rewriting it if possible to keep the delta between the origin branch and fork minimal.

The documentation states that

All common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. A single WITH clause can specify one or more common table expressions, some of which are ordinary and some of which are recursive.

...

The WITH clause must appear at the beginning of a top-level SELECT statement or at the beginning of a subquery. The WITH clause cannot be prepended to the second or subsequent SELECT statement of a compound select.

I found a somewhat relevant thread on the SQLite forum, but it doesn't appear resolved or active:

https://sqlite.org/forum/info/98fb8943a2e0e70a

I also found a Stack Overflow thread with a response stating that

SQLite ... does not allow INSERT in CTE.

which seems to contradict the documentation but seems valid, and seems to apply to UPDATE and DELETE as well:

sqlite> WITH cte(id,s) AS (UPDATE test SET s = 'test' WHERE id = 1 RETURNING test.id, test.s) SELECT cte.id, cte.s FROM cte;
Error: in prepare, near "UPDATE": syntax error (1)
sqlite> WITH cte(id,s) AS (DELETE FROM test WHERE id = 1 RETURNING test.id, test.s) SELECT cte.id, cte.s FROM cte;
Error: in prepare, near "DELETE": syntax error (1)

Am I missing something?

But it does not work in SQLite.

See 97f548bd6e71426a.

See Limitations And Caveats item number 3.