Search code examples
sqliteupsert

How to avoid consume key-id when using upsert with autoincrement key in SQLite?

I have a table like below:

The id is PK and autoincrement.

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+

When i using the upsert query bleow, and 100 request that api at same time.

INSERT INTO Views (kind, count)
VALUES(1, 1)
ON CONFLICT(kind)
DO UPDATE SET count = count + 1

I'll get:

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+
| 100 | kind-1 |   100  |
+-----+--------+--------+
Solution

  • it is possible to manipulate sqlite_sequence table like this:

    UPDATE sqlite_sequence SET seq=(SELECT MAX(id) FROM Views) WHERE name="Views"

    which will "reset" sequence to last used id.

    This isn't, however, usefull at all. Updating sqlite_sequence during bulk UPSERT would for sure break performance and doing such after operation wouldn't avoid "holes" in key sequence.

    Another option is to rewrite all id, but do you really need such?