> ... what happens if I write?
> INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid
> INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO NOTHING RETURNING rowid
> Is the rowid returned if there was already an entry?
I would expect no returned result from attempting data modification where it cannot occur. However, the DML with a RETURNING clause ("DMLR") is in development and so it is premature to ask what happens, as if that was the final answer. We might discuss what happens now, and how desirable that or something else is.
In my opinion, DMLR which yields only data modification attempts should not return anything. The pre-v3.35 build I have acts that way. To wit:<code>
[C:\Tmp]
> type dmlr_conflict.sql
create table t (species text unique);
insert into t values ('dog');
insert into t values ('cat');
insert into t values ('pig') on conflict do nothing returning rowid, species;
insert into t values ('dog') on conflict do nothing returning rowid, species;
drop table t
[C:\Tmp]
> echo .q | sqlite3m -batch -cmd ".read dmlr_conflict.sql"
rowid species
3 pig
rowid species
[C:\Tmp]
>
</code>
As you can see, the last DMLR input produced an empty [a] result set.
[a. The 'sqlite3m' shell is like sqlite3.exe except it emits row headers even if the result set was empty. ]
> Maybe a different syntax like would be better?
A simple query would get the set of rowid you desire. If the DMLR feature (which is experimental and evolving) should produce results that can be put into a temp table or used directly in queries, you could easily write that simple query using those RETURNING results. I think that is a much smaller imposition on users than adding further syntax complexity.