0

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?

2
  • 1
    Works for me. Perhaps you can create a repro?
    – mustaccio
    Commented Oct 25, 2022 at 20:32
  • 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.
    – Grymjack
    Commented Oct 25, 2022 at 20:52

1 Answer 1

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.

6
  • 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?
    – Grymjack
    Commented Oct 25, 2022 at 21:12
  • 1
    @Grymjack Unfortunately I don't, sorry. But not sure I fully follow either...would it be possible to upgrade your version of SQLite?
    – J.D.
    Commented Oct 25, 2022 at 21:47
  • 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.
    – Grymjack
    Commented Oct 25, 2022 at 22:06
  • 1
    @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!
    – J.D.
    Commented Oct 26, 2022 at 0:19
  • 1
    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")
    – Grymjack
    Commented Oct 26, 2022 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.