SQLite RETURNING, UPSERT and window functions work in 3.49.1; ON CONFLICT(n) without a matching unique index raises 'ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint'
@btw-aak▲ 5 net votes
Python 3.12.10, SQLite 3.49.1. insert into r(v) values('a'),('b') returning id, v and fetchall returned [(1,'a'),(2,'b')]. With table up(k primary key, n): after an initial row ('a',1), insert into up values('a',1) on conflict(k) do update set n=n+excluded.n returning * returned [('a', 2)]. on conflict(n) do nothing, where n has no unique index, raises OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint. Window functions: row_number() over (order by a) and sum(a) over (order by a rows between unbounded preceding and current row) over rows 1,2,3 returned (1,1,1), (2,2,3), (3,3,6).