insert with returning question

(1) By anonymous on 2021-05-03 15:53:29 [link] [source]

Hello,

If I have a table like this: create table if not exists table1(a text, b integer);

I can add rows like this: insert into table1(a, b) values("a", 1), ("b", 2), ... ;

And query them like this: select rowid, a, b from table1;

If I now do this: insert into table1(a, b) values("a", 1) returning rowid;

No row will be returned. If a new record is added, the rowid will be returned.

I'm looking for a way to always have a row returned. In other words, to form a "get or create" kind of statement. I realize I can run two queries to do this, but it seems like it would be more efficient if I could do this with just a single query.

Any suggestions?

(2) By Harald Hanche-Olsen (hanche) on 2021-05-03 16:14:41 in reply to 1 [source]

I don't get the behaviour you describe from sqlite.

Since your table has no primary keys or unique constraints, INSERT will always insert a new row, and the RETURNING clause will indeed give you the new rowid.

If you made the a column, say, a primary key, and use INSERT OR REPLACE, you also get rows inserted and rowids returned.

So perhaps you are thinking of INSERT OR IGNORE, and you would like a single sql command INSERTing og IGNOREing, respectively, but returning a rowid in either case? Honestly, I don't know if you can. But can you tell us whether my interpretation is correct, or I have misunderstood the question completely?

(3) By anonymous on 2021-05-03 16:59:28 in reply to 2 [link] [source]

My apologies. Indeed, I did forget to add the "or ignore" qualifier. And, your interpretation is correct.

In C++ systems I write, I tend to create code that looks something like this:

class Vertex;

class Graph { ... Vertex* GetOrCreateVertex(const std::string& rVertexName); ... }

Vertex* pV = pGraph->GetOrCreateVertex("kitchen"); assert(pV != 0); pV->SetRoomSize(20.5 * 24.5);

The GetOrCreate pattern makes code straightforward to write. All I'm after is something that works in a similar fashion for a sqlite database. I know about the UPSERT statement but this isn't quite the same thing as a "get or create" statement.

Again, if I have to use two different statements to make this work, that's not a problem. I was simply wondering if there is way to do this with just a single statement.