Sqlite fails when performing an 'insert' with a 'returning' value clause

Question

I am having problems with SQLite? I didn't have this problem when using the same tables on PostgreSQL. When the table is empty and you use the following SQL statement it throws an error.

INSERT INTO [table] ([column]) VALUES ([column value]) RETURNING [column];

The returning column is an auto-increment primary key field and the error says there is a syntax error after 'RETURNING'. When there is already another record in the table, it has no issues. My workaround right now is to do an empty table check and insert a dummy record if needed. This is clunky, and I feel like there is something I am missing. Any thoughts out there?

• mustaccio: Works for me. Perhaps you can create a repro?
• Grymjack: dbfiddle.uk cool site! Your sql fails when I run it locally. screenshot: ibb.co/pJTkY9Z SQLite Version 3.12.99 FYI: It doesn't just fail in DB Browser, it also fails in code.

Answer 1

Your version (3.12.99) of SQLite doesn't support the RETURNING clause:

The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021年03月12日).

This is why mustaccio's repro works, it's using version 3.39, but dbfiddle allows you to drop the version down to 3.27 and you get an error there as well then.

• Grymjack: Ok, thank you. db-to-sqlite is the tool I use to dump from postgresql to sqllite. pypi.org/project/db-to-sqlite Looking like the last update was Jun 11, 2021. Do you know of a dump tool that creates a more current version of a sqlite file?
• J.D.: @Grymjack Unfortunately I don't, sorry. But not sure I fully follow either...would it be possible to upgrade your version of SQLite?
• Grymjack: the db-to-sqlite tool creates the sqlite file. I guess I need to upgrade the .dll file? I thought that since it was a 'portable' database (using this for a android app) that it was built into the actual file.
• J.D.: @Grymjack Oh I see, I didn't realize that tool creates a database file for you. I assumed just scripts that you were then running on your own local instance. Hmm not sure what else to advise, sorry. Best of luck!
• Grymjack: I dropped an issue on GitHub for the creator of the tool. Meanwhile I can work around it by inserting a dummy record in an empty table. "INSERT INTO " + table_name + " DEFAULT VALUES")