SQLite (and other RDBMS, like PostgreSQL) allow you to perform an insert, and if there's a conflict, execute an update instead. However, getting the final value that was updated seems tricky.
Take this answer as an example:
INSERT INTO players (user_name, age)
VALUES('steven', 32)
ON CONFLICT(user_name)
DO UPDATE SET age=excluded.age;
Suppose I actually needed to know what age was ultimately set to. With PostgreSQL, you can say RETURNING age, while trying the same thing with SQLite (3.34.0[1], at least), this seems to be a syntax error. If I simply perform a read operation right after, it's possible that another operation will have swooped in and updated the row after my write completed and before my read started, thus returning a misleading result to the original caller.
- Is there any equivalent syntax to
RETURNINGthat SQLite supports to atomically write and read as described? - If not, is there some other pattern I should follow, perhaps
begin transaction-write-read-commit?
Thanks!
[1] xerial/sqlite-jdbc 3.34.0 specifically.
