[SOLVED] sqlite3 INSERT sometimes, RETURNING always

sqlite3 INSERT sometimes, RETURNING always

So I have a Table in some sqlite3 DB that has the following structure:

CREATE TABLE table_00 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  value VARCHAR(100) UNIQUE NOT NULL   
);

I need to retrieve the id from this table to use it in some other query. Until now, this has been done using:

INSERT INTO table_00(value) VALUES (<some_value>) ON CONFLICT (value) DO UPDATE SET value = <some_value> RETURNING id;

This works, but on the long run it spends too much time updating when I only intend to retrieve the id corresponding to the value. Is there any query that would always return the id, without needing to update any entry in case the value already exists ? I would like to avoid doing an INSERT-ON CONFLICT-DO NOTHING followed by a SELECT.

Solution

You can try setting the value column to itself on update, rather than to your externally provided value. I'm not completely sure SQLite detects this case and optimizes it away, but it's worth a try.

INSERT INTO table_00(value) VALUES (<some_value>) 
ON CONFLICT (value) DO UPDATE SET value = value 
RETURNING id;