UPSERT and last_insert_rowid()
(1) By anonymous on 2022-05-07 09:04:41 [link] [source]
Hello,
I'm currently using SQLite for a pet project of mine, and ran into this limitation: when doing an INSERT INTO … ON CONFLICT DO UPDATE …, the last_insert_rowid() value is invalid (set to 0 on my machine) in case of a conflict.
Since I do not know the row ID before the query (otherwise I wouldn't try an INSERT), and since SQLite has to know the row ID after having detected the conflict, I think it would make sense that it provides that value when I resolve the conflict with an UPDATE.
Is there a good reason why this is happening? Is this just an oversight? I couldn't find any official documentation about this particular case. This is also something that might need to be addressed.
(2) By anonymous on 2022-05-07 17:58:54 in reply to 1 [link] [source]
You can use the RETURNING command to return the rowid number.
(3) By Larry Brasfield (larrybr) on 2022-05-07 18:24:58 in reply to 1 [link] [source]
Is there a good reason why this is happening? Is this just an oversight? I couldn't find any official documentation about this particular case.
This doc on sqlite3_last_insert_rowid(...) says, "An INSERT that fails due to a constraint violation is not a successful INSERT and does not change the value returned by this routine." This assertion in the (official) documentation is good reason for that return value to be unaffected by the failed insert.
I think it would make sense that it provides that value when I resolve the conflict with an UPDATE.
That might be sensible as an original specification of behavior. But it would create compatibility issues as a revised specification of behavior.
(4) By Gerry Snyder (GSnyder) on 2022-05-07 18:35:45 in reply to 3 [link] [source]
That might be sensible, but it might not.
It is not an ID of an insertion.
Assuming the RETURNING clause works whether an update or an insertion is done, the only thing I would suggest is making that clear in the docs.
Gerry
(5) By Keith Medcalf (kmedcalf) on 2022-05-07 18:42:01 in reply to 4 [source]
RETURNING will work if an operation is done. If nothing is done, nothing is returned.
sqlite> create table t(id integer primary key, data text not null collate nocase unique);
sqlite> insert into t(data) values ('one') on conflict do update set data=excluded.data returning id, data;
┌────┬───────┐
│ id │ data │
├────┼───────┤
│ 1 │ 'one' │
└────┴───────┘
sqlite> insert into t(data) values ('one') on conflict do update set data=excluded.data returning id, data;
┌────┬───────┐
│ id │ data │
├────┼───────┤
│ 1 │ 'one' │
└────┴───────┘
sqlite> insert into t(data) values ('one') on conflict do nothing returning id, data;
sqlite>
(6) By Larry Brasfield (larrybr) on 2022-05-07 19:03:47 in reply to 4 [link] [source]
That might be sensible, but it might not.
It is not an ID of an insertion.
I would agree that "last_insert_rowid" would be a poor name for something better called "last_insert_or_update_rowid". If not for the superiority of the RETURNING clause and the limitations of having a single rowid result from the existing API, one might make a compelling argument for a new API.
Assuming the RETURNING clause works whether an update or an insertion is done, the only thing I would suggest is making that clear in the docs.
The doc on RETURNING is already quite clear on this point.